Re: Strange behaviour i.e SQL%FOUND

Fuad,

    I my failing memory is to be believed, I think that 9.0 was the first Oracle version when a ';' (or / in first position) was required with SQL*Plus after COMMIT or ROLLBACK. In other words, there has been a time when transaction management statements were definitely issuing behind-the-scene database calls but were somewhat assimilated to SQL*Plus commands such as COLUMN, COMPUTE or WHENEVER SQLERROR ... All of a sudden, they became full blown SQL statements. My interpretation is that SQL in SQL%FOUND refers to the DELETE in 8i, and to the COMMIT (that affects no row - well, it('s debatable, but you can say it affects no row) in later versions. I lack the databases at hand, but I suspect that if you issue a COMMIT under SQL*Plus and type L to list the SQL buffer, you will see the prior SQL statement with 8i and the COMMIT in later versions.

Stéphane Faroult

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