Fw: Strange behaviour i.e SQL%FOUND

  • From: Fuad Arshad <fuadar@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 21 Jun 2007 13:12:13 -0700 (PDT)

looked at your suggestion and that doesnt look as the case 
herei s the output 
SQL*Plus: Release 8.1.7.0.0 - Production on Thu Jun 21 15:08:27 2007
(c) Copyright 2000 Oracle Corporation. All rights reserved.
 
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 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 <sfaroult@xxxxxxxxxxxx>
To: fuadar@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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 (8.1.7.4)
>
> **
>
> *9i - *SQL%FOUND fails
>
> **
>
> *10g - *SQL%FOUND fails
>
>  
>

Other related posts:

  • » Fw: Strange behaviour i.e SQL%FOUND