Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
danielrozental
Master II
Master II

you should check the user grants

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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.

bernd_schuetze
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)