topic Re: Insert/Update/Delete SQL Error "ORA-01456" In Qv,How to solve it? in QlikView

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

Fri, 22 Oct 2010 11:17:14 GMT

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?

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

danielrozental — Fri, 22 Oct 2010 21:48:55 GMT

you should check the user grants

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

Oleg_Troyansky — Sat, 23 Oct 2010 01:21:19 GMT

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.

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

bernd_schuetze — Thu, 03 May 2018 12:36:23 GMT

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

)

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