Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Location
    Paris
    Posts
    48

    Unanswered: SQL%NOTFOUND not working

    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 ;
    Last edited by elitol; 08-10-04 at 12:35.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    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;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Apr 2004
    Location
    Paris
    Posts
    48
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •