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 .
