Strange behaviour i.e SQL%FOUND

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

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: