Re: Strange behaviour i.e SQL%FOUND

Precisely what i was looking for.
tested this and it definitely seems to be the change. thanks all those who responded and gave suggestions.

• Original Message ---- From: Kurt Franke <frankek_at_strsun30.debitel.de> To: fuadar_at_yahoo.com Cc: oracle-l-bounce_at_freelists.org Sent: Friday, June 22, 2007 9:24:39 AM Subject: Re: Strange behaviour i.e SQL%FOUND

Fuad,

from version 9i on cursor attributes are no longer accessible after a commit

if you need it anyway you must set a special event in the init.ora:

event = "10943 trace name context forever, level 4194304"

Kurt Franke

Fuad Arshad wrote:
>
> Anyone encounter this case.
> i know i can help them rewrite this to a different approach but just curious as to what the reasoning could be for this
> This is a case of 8i to 9 upgrade .
> Application code has
> set timing on
> set time on
> set echo on
> set 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 (8.1.7.4)
> 9i - SQL%FOUND fails
> 10g - SQL%FOUND fails
>
>

--
http://www.freelists.org/webpage/oracle-l