Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2007
    Posts
    13

    Exclamation Unanswered: Undo Pragma EXCEPTION_INIT

    I have a problem with Pragma EXCEPTION_INIT,
    I declared an exception
    DECLARE
    .
    .
    ROLLBACK_OLD exception;
    pragma EXCEPTION_INIT(ROLLBACK_OLD,-1555);
    .
    .
    BEGIN
    .
    .
    EXCEPTION
    WHEN ROLLBACK_OLD THEN
    dbms_output.put_line('Too old!! skipping '||v_variable);
    WHEN OTHERS THEN
    .
    .
    END;
    /
    The expected result is an output should be 'Too old!! Skipping XXXXXX'; What I get is PL/SQL procedure successfully complete, and no other output.
    even if i comment out the Declare of the Pragma EXCEPTION_INIT and the Rollback_old exception. when i comment the whole exceptions block, the error appears.

    How do I get the system to act when it encounters this error and not just ignore it? Or how to I undo the Pragma EXCEPTION_INIT?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Oracle Exception Handing WHEN OTHERS SQLCODE SQLERRM PRAGMA INIT

    >How do I get the system to act when it encounters this error and not just ignore it?
    What do you want to occur?

    >Or how to I undo the Pragma EXCEPTION_INIT?
    Please clarify?

    "undo"? as in ROLLBACK?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2007
    Posts
    13
    I want the script to identify the returned error of a flashback query as unable to query because of the flashback is too old or unavailable and update the various tables as such.

    Isn't Pragma EXCEPTION_INIT defined per script and not system update?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    why are you using Pragma EXCEPTION_INIT to begin with.

    Processing has failed when error gets thrown.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jul 2007
    Posts
    13
    Ok, Maybe i'm not explaining this clearly, I need it to perform additional actions when it incounters this user definied error.

    I have these results that are not performing as I specified, and even if I don't have Pragma EXCEPTION_INIT defined and have an exceptions block to handle others, I do not the see the error for ora-01555

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It works for me.

    Code:
    11:11:05 SQL> @eh
    11:11:09 SQL> SET SERVEROUTPUT ON ECHO ON TERM ON TIME ON
    11:11:09 SQL> DROP TABLE FOOBAR;
    
    Table dropped.
    
    11:11:10 SQL> CREATE TABLE FOOBAR(ID NUMBER, PRIMARY KEY(ID));
    
    Table created.
    
    11:11:10 SQL> DECLARE
    11:11:10   2  DOUBLE_DUP   exception;
    11:11:10   3  pragma EXCEPTION_INIT(DOUBLE_DUP	,-1);
    11:11:10   4  BEGIN
    11:11:10   5  DBMS_OUTPUT.ENABLE(10000);
    11:11:10   6  INSERT INTO FOOBAR VALUES(1);
    11:11:10   7  INSERT INTO FOOBAR VALUES(1);
    11:11:10   8  EXCEPTION
    11:11:10   9  WHEN DOUBLE_DUP	THEN
    11:11:10  10  dbms_output.put_line('Duplicate value now!');
    11:11:10  11  WHEN OTHERS THEN
    11:11:10  12  RAISE;
    11:11:10  13  END;
    11:11:10  14  /
    Duplicate value now!
    
    PL/SQL procedure successfully completed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jul 2007
    Posts
    13
    Ok,

    How do you undeclare a pragma EXCEPTION_INIT

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How do you undeclare a pragma EXCEPTION_INIT
    I don't know that you can do so.
    Don't declare it in the first place?
    Why do you think you need to do so?

    How do you undeclare PL/SQL variable?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    15:16:18 SQL> DROP TABLE FOOBAR;
    
    Table dropped.
    
    15:16:19 SQL> CREATE TABLE FOOBAR(ID NUMBER, PRIMARY KEY(ID));
    
    Table created.
    
    15:16:19 SQL> DECLARE
    15:16:19   2  DOUBLE_DUP   exception;
    15:16:19   3  pragma EXCEPTION_INIT(DOUBLE_DUP	,-1);
    15:16:19   4  BEGIN
    15:16:19   5  DBMS_OUTPUT.ENABLE(10000);
    15:16:19   6  INSERT INTO FOOBAR VALUES(1);
    15:16:19   7  INSERT INTO FOOBAR VALUES(1);
    15:16:19   8  EXCEPTION
    15:16:19   9  WHEN DOUBLE_DUP	THEN
    15:16:19  10  BEGIN
    15:16:19  11  dbms_output.put_line('Duplicate value now!');
    15:16:19  12  INSERT INTO FOOBAR VALUES(2);
    15:16:19  13  INSERT INTO FOOBAR VALUES(2);
    15:16:19  14  dbms_output.put_line('Duplicate value now2');
    15:16:19  15  END;
    15:16:19  16  WHEN OTHERS THEN
    15:16:19  17  RAISE;
    15:16:19  18  END;
    15:16:19  19  /
    Duplicate value now!
    DECLARE
    *
    ERROR at line 1:
    ORA-00001: unique constraint (DBADMIN.SYS_C006168) violated
    ORA-06512: at line 13
    ORA-00001: unique constraint (DBADMIN.SYS_C006168) violated
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by dsawvel View Post
    I want the script to identify the returned error of a flashback query as unable to query because of the flashback is too old or unavailable and update the various tables as such.
    What about detecting the exception thrown in flashback query?
    Code:
    SQL> create table t( c integer );
    
    Table created.
    
    SQL> select * from t as of timestamp to_timestamp( '01.01.2010', 'dd.mm.yyyy' );
    select * from t as of timestamp to_timestamp( '01.01.2010', 'dd.mm.yyyy' )
                  *
    ERROR at line 1:
    ORA-08180: no snapshot found based on specified time
    
    
    SQL>
    Do you see the error code? It is definitely not -1555. And I am sure you would see it in that PL/SQL block as well if you got rid of WHEN OTHERS exception masking at all.

Posting Permissions

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