1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Raise an exception in one file that can be seen from another file?

Discussion in 'SQL PL/SQL' started by pditty8811, Mar 4, 2014.

  1. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    If I have trigger in another file (file 2) (or some script) that gets started from file 1, how do I tell file 1 to look if file 2 (the trigger) threw an exception?
     
  2. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Please post a quick example.
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Tell that you want to implement:
    error handling of return codes after execution sql -script?

    if yes, then look these links :

    http://docs.oracle.com/cd/A91202_01/901_doc/server.901/a88827/ch852.htm

    http://www.orafaq.com/wiki/SQL*Plus_FAQ

    http://books.google.ru/books?id=aDHKcmoCTPEC&pg=PT378&lpg=PT378&dq=whenever+sqlerror+example+oracle&source=bl&ots=gQbrrL5oTK&sig=uqwSaWzo6uHI5yjYyDICsEMvp04&hl=ru&sa=X&ei=r64VU52hF6So4gSnj4HgBQ&ved=0CCMQ6AEwADgK#v=onepage&q=whenever%20sqlerror%20example%20oracle&f=false

    Code (SQL):


    $ sqlplus -s scott/tiger

    SQL> CREATE TABLE test1( a NUMBER(10));

    TABLE created.

    SQL> INSERT INTO test1 VALUES( 1 ); -- (***) note the value!

    1 ROW created.

    SQL> commit;

    Commit complete.

    CREATE OR REPLACE PROCEDURE testproc1 IS
      X NUMBER;
    BEGIN
      SELECT a INTO X FROM test1 WHERE a = 1; -- (***)
    exception
      WHEN no_data_found THEN
        raise;
    END;
    /
    CREATE OR REPLACE PROCEDURE testproc2 IS
      X NUMBER;
    BEGIN
      SELECT a INTO X FROM test1 WHERE a = 3; -- (xxx)
    exception
      WHEN no_data_found THEN
        raise;
    END;
    /
    CREATE OR REPLACE PROCEDURE testproc3 IS
      X NUMBER;
    BEGIN
      SELECT a INTO X FROM test1 WHERE a = 4;
    exception
      WHEN no_data_found THEN
        raise;
    END;
    /

    ^D

    $ cat SomeTest
    sqlplus -s scott/tiger << !
    whenever sqlerror exit 3;
    SELECT 'Test 1' FROM dual;
    EXEC testproc1;               -- This works (*1 - it selects where value = 1 (***))
    SELECT 'Test 2' FROM dual;
    EXEC testproc2;               -- This fails (*2) (xxx)
    SELECT 'Test 3' FROM dual;
    EXEC testproc3;               -- This is never executed (*3)
    !

    echo Returned $?



    $ ./SomeTest
    'TEST1
    ------
    Test 1


    PL/SQL procedure successfully completed. (*1)


    '
    TEST2
    ------
    Test 2

    BEGIN testproc2; END;

    *
    ERROR at line 1: (*2)
    ORA-01403: no DATA found
    ORA-06512: at "SCOTT.TESTPROC2", line 8
    ORA-06512: at line 1

    (*3) Note that TESTPROC3 IS NOT executed

    Returned 3

     
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The trigger TEXT is in a file but once you execute that script the TRIGGER is in the DATABASE. The source file has nothing to do with how the trigger executes. ANYTHING that runs that uses that trigger will see any errors that trigger generates.
     
  5. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Our instructor is telling us to use PRAGM INIT_EXCEPTION to pass the error code of user defined exception back to the original script.
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    EXCEPTION_INIT PragmaThe pragma EXCEPTION_INIT associates an exception name with an Oracle error number.
    You can intercept any ORA- error and write a specific handler for it instead of using the OTHERS handler.
    For more information, see "Associating a PL/SQL Exception with a Number: Pragma EXCEPTION_INIT".

    Code (SQL):

    DECLARE    
           deadlock_detected EXCEPTION;    
    PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
    BEGIN    
                 NULL; -- Some operation that causes an ORA-00060 error
    EXCEPTION    WHEN deadlock_detected THEN       NULL; -- handle the error
    END;
     

     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO