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

[#1]

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?

[danielrozental]

you should check the user grants

[Oleg_Troyansky]

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]

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)