Monday, June 25, 2007

Cursor Attribute changes from Oracle 8 onwards

There was aPl/sql change in the way oracle handles cursor attributes.
A colleague of mine at work had this issue and i decided to look this up.
Below is the test case (Oracle Always wants one ).

--CREATE TABLE DDL
create table test (test1 varchar2(1));

set timing onset time onset echo onset serveroutput on
DECLARE v_test1 VARCHAR2(1) := 'A'; BEGIN --CREATE A RECORD insert into test values ('A'); commit;
DELETE FROM test WHERE test1 = v_test1; commit; --THIS IS THE COMMIT THAT PASSES SQL%FOUND IN ORACLE 8i BUT FAILS IN 9i & 10g IF SQL%FOUND THEN -- delete succeeded/passed
DBMS_OUTPUT.PUT_LINE('SUCCESS 1');
ELSE -- delete failed
DBMS_OUTPUT.PUT_LINE('FAILURE 1');
END IF; END;


The results of this code in each DB version is:
8i - SQL%FOUND succeeds
9i - SQL%FOUND fails
10g - SQL%FOUND fails


Now if you look at the above results it does seem that the changes happened in the 9.2 build (maybe 9.0 but i dont have a database to test).

Kurt Franke d at oracle-l pointed out the fact that from version 9i on cursor attributes are no longer accessible after a commitif you need it anyway you must set a special event in the init.ora:
event = "10943 trace name context forever, level 4194304"


Oracle later on came back with a bug number 2286387 and said that going forward this would be the expected behaviour.

Thanks to all that helped .

Google Search