Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Fw: Strange behaviour i.e SQL%FOUND

Fw: Strange behaviour i.e SQL%FOUND

From: Fuad Arshad <>
Date: Thu, 21 Jun 2007 13:12:13 -0700 (PDT)
Message-ID: <>

looked at your suggestion and that doesnt look as the case herei s the output SQL*Plus: Release - Production on Thu Jun 21 15:08:27 2007 (c) Copyright 2000 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release - Production With the Partitioning option JServer Release - Production SQL> connect tstuser Enter password: Connected. SQL> create table test (test1 varchar2(1)); Table created. SQL> insert into test values ('A'); 1 row created. SQL> l 1* insert into test values ('A') SQL> commit; Commit complete. SQL> l 1* commit SQL> ----- Forwarded Message ---- From: Stephane Faroult <> To: Cc: Sent: Thursday, June 21, 2007 2:24:54 PM Subject: 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 ( > > ** > > *9i - *SQL%FOUND fails > > ** > > *10g - *SQL%FOUND fails > > >

Received on Thu Jun 21 2007 - 15:12:13 CDT

Original text of this message