Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Insert/Update/Delete SQL Error "ORA-01456" In Qv,How to solve it?

I checked "Open Databases in Read and Write mode", and run the script:

ODBC CONNECT TO [crmlocal;DBQ=CRMLOCAL ] (XUserId is XXXX, XPassword is XXX);

sql delete from tbl_log_etl;

The script got an error:

SQL Error:[Oracle][ODBC][Ora]ORA-01456: "may not perform insert/delete/update operation inside a READ ONLY transaction"
SQL State:S1000
sql delete from tbl_log_etl

Anyone met such problem? How to solve it?

3 Replies
Highlighted
Master II
Master II

you should check the user grants

Highlighted
MVP & Luminary
MVP & Luminary

Several places to check:

1. inside QliKView script editor - in the "Setiings Tab", check "Open Databases in Read and Write Mode"

2. ODBC Data source - check that it's not read only

3. Database or host computer settings - check that they allow update via ODBC connections (for example AS/400 databases often prohibit that globally)

4. Check user rights - the user should be allowed to update/ delete data.

Oleg Troyansky

Masters Summit for Qlik is going virtual! Nov 18th - I'll teach Advanced Set Analysis and AGGR

Highlighted
Contributor II
Contributor II

We had the same issue recently with some packages we call from Qlikview in the Oracle EEDB. The packages did work for a year or two but when we migrated to a new server (New ODBC Driver version, new Qlikview version 12.1.x) the error occurred regularly on some of them.

We fixed the issue with a (admittedly weird) workaround: Basically we issue a commit at the beginning of the procedure. This commit seems to result in a cleanup of stale session parameters that Qlikview (or the ODBC Driver) seems to still have somewhere

               PROCEDURE generate_Report(

               --Parameters

               )

               IS

               --Variables

               BEGIN

                  COMMIT;

                 -- real procedure...

Please don't ask me why this works and I hate to give such a comment, but we also didn't figure out why some procedures now get this error and others not.

(And yes, of course we checked the read only settings and the grants)