topic Re: write in Oracle database table in App Development

write in Oracle database table

ali_hijazi — Thu, 30 Nov 2023 08:56:14 GMT

Hello
is there a way to write in an oracle database while running the script in Qlik sense?

Re: write in Oracle database table

vincent_ardiet_ — Thu, 30 Nov 2023 10:27:49 GMT

You can put what you want after the SQL keyword, of course it depends of the user rights of the account set in the connection.
For example, in my company, we are launching stored procedures to prepare the data or to acknowledge that we manage to do it, in order to receive incremental datasets.
Like this:

IF('$(vAckSuccessFlag)' = 'N') then

SQL CALL pkg_dwh.pr_acksync;

...

Re: write in Oracle database table

ali_hijazi — Thu, 30 Nov 2023 10:36:08 GMT

Hello thank you for your reply
what I want is read from a database (load data into memory)) then insert it in another database
now the script that you wrote above:
\SQL CALL pkg_dwh.pr_acksync

but the query after the SQL should return a result set; I cannot put insert into table;
kindly advise

Re: write in Oracle database table

vincent_ardiet_ — Thu, 30 Nov 2023 10:47:14 GMT

No there is no need of a dataset in return.
We are also doing such command:

SQL alter session set NLS_DATE_FORMAT = 'DD-MON-RR';

Re: write in Oracle database table

Lech_Miszkiewicz — Thu, 30 Nov 2023 10:47:42 GMT

Hi @ali_hijazi

you will need to make some configuration changes if you want to execute non-select queries. This is documented on help side:

https://help.qlik.com/en-US/connectors/Subsystems/ODBC_connector_help/Content/Connectors_ODBC/How-to-load-without-SELECT.htm

cheers

Re: write in Oracle database table

vincent_ardiet_ — Thu, 30 Nov 2023 11:06:17 GMT

Or use OLEDB 🙂

Re: write in Oracle database table

ali_hijazi — Thu, 30 Nov 2023 15:01:31 GMT

it seems to work; I got the insufficient privileges from Oracle server
though I remember once I tried to have an insert statement and I got error from Qlik itself saying what sounded like the query has to return a result set

but now if I have a table T in memory (loaded in Qlik)
what would be the syntax to read rows from T and insert them into a database table?

Re: write in Oracle database table

vincent_ardiet_ — Fri, 01 Dec 2023 07:55:21 GMT

You will have to do a loop across all rows of T and do your inserts one by one.