SQL%NOTFOUND not working

elitol · #1 (**permalink**) 08-09-04, 10:06

Hello

I'm using Oracle 9i
I'd like to make an insert and first check if the items don't already exist.
I do 2 tests.
my procedure compiles.
it works when the SELECT INTO finds sth but not in the other case.
however i'm using the SQL%NOTFOUND cursor just after each SELECT query.

using the NO_DATA_FOUND exception would not solve my problem because the exception can be (and should be) raised twice (in order to make the insertion)
and depending on the case, it's not the same code that is executed after.

thanks for your help.

Code:

select IDXACCOUNT into get_idxaccount 
from cv_account a 
where ... ; 
     if sql%notfound 
     then dbms_output.put_line('can''t insert : account doesn''t exist') ; 
     else 
   select broker INTO ligne 
   from cv_carte cb 
   where ... ;    
   if sql%notfound 
                then insert into cv_carte values (get_idxaccount, cb_name, cb_broker, cb_contract ) ; 
   DBMS_OUTPUT.put_line('carte inserted)') ; 
                else 
   DBMS_OUTPUT.put_line('carte not inserted because it already exists') ; 
                end if ; 
end if ;

LKBrwn_DBA · #2 (**permalink**) 08-09-04, 13:24

Quote:

Originally Posted by elitol

Hello
using the NO_DATA_FOUND exception would not solve my problem because the exception can be (and should be) raised twice (in order to make the insertion) and depending on the case, it's not the same code that is executed after.

NO_DATA FOUND Can sure solve the problem:

Code:

Begin
  Select IDXACCOUNT Into Get_Idxaccount 
    From Cv_Account A 
   Where ... ; 
  Begin 
    Select BROKER Into Ligne 
      From Cv_Carte Cb 
     Where ... ;    
    Dbms_Output.Put_Line('Carte Not Inserted Because It Already Exists'); 
  Exception
    When No_Data_Found Then
      Insert Into Cv_Carte Values (Get_Idxaccount, Cb_Name, Cb_Broker, Cb_Contract ); 
      Dbms_Output.Put_Line('Carte Inserted)'); 
  End;              
Exception
  When No_Data_Found Then
    Dbms_Output.Put_Line('Can''t Insert : Account Doesn''t Exist') ; 
End;

elitol · #3 (**permalink**) 08-10-04, 11:17

thank you soooooooooooooo much
and you answered really fast

i didn't know it was possible to use the begin/end like that.

now it works perfectly

CU ;D

Internet Services

Web Development

Digital Marketing

Consumer Tech