Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55

    Question Unanswered: Defining my own message error on my own error

    Hi,
    All is in the title.
    I defined an exception, and i want to associate a message because i don't want to handle it in the block i declared it.
    I see RAISE_APPLICATION_ERROR(error_number, message) but i can't associate my error with an Oracle exception with PRAGMA EXCEPTION_INIT.

    I'd like to have something like this :

    RAISE_my_APPLICATION_ERROR(my_error, my_message) to handle it in another subprogram.
    Does it exist ?
    Thx for your contribution.

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: Defining my own message error on my own error

    Originally posted by venderic
    Hi,
    All is in the title.
    I defined an exception, and i want to associate a message because i don't want to handle it in the block i declared it.
    I see RAISE_APPLICATION_ERROR(error_number, message) but i can't associate my error with an Oracle exception with PRAGMA EXCEPTION_INIT.

    I'd like to have something like this :

    RAISE_my_APPLICATION_ERROR(my_error, my_message) to handle it in another subprogram.
    Does it exist ?
    Thx for your contribution.
    In pls/sql procedure or function you could handling the exceptions.
    for example:
    ...

    begin
    do_something;

    exception when others then
    do_something_other;

    end;
    ...

    This is very basic info, so for aditional exception handling check the documentation

  3. #3
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55

    Re: Defining my own message error on my own error

    Originally posted by ika
    In pls/sql procedure or function you could handling the exceptions.
    for example:
    ...

    begin
    do_something;

    exception when others then
    do_something_other;

    end;
    ...

    This is very basic info, so for aditional exception handling check the documentation
    Very very thanks.... but i repeat, i don't want to handle my exception in the program i raise.

    i have this :
    Code:
    procedure toto
    my_exception exception;
    begin
    
    do_something
    if not
      raise my_exception
    
    exception
      when my_exception then
         do_something;
         raise;
    end toto;
    
    procedure toto1
    begin
      do_something;
      toto;
    exception
      when others  then -- including my_exception
         dbms_output.put_line(my_message);
    end;
    How can i do this ?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Defining my own message error on my own error

    You can declare your exception in a package specification, and then it is visible to all code:
    PHP Code:
    SQLcreate package pkg is
      2    my_exception exception
    ;
      
    3  end;
      
    4  /

    Package created.

    SQLcreate or replace procedure proc1 is
      2  begin
      3    raise pkg
    .my_exception;
      
    4end;
      
    5  /

    Procedure created.

    SQLcreate or replace procedure proc2 is
      2  begin
      3    proc1
    ;
      
    4  exception
      5    when pkg
    .my_exception then
      6      raise_application_error
    (-20001,'My exception was raised' );
      
    7    when others then 
      8      raise_application_error
    (-20002,'Some other exception was raised');
      
    9  end;
     
    10  /

    Procedure created.

    SQLexec proc2
    begin proc2
    end;

    *
    ERROR at line 1:
    ORA-20001My exception was raised
    ORA
    -06512at "TANDREWS.PROC2"line 6
    ORA
    -06512at line 1 

  5. #5
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55

    Re: Defining my own message error on my own error

    Originally posted by andrewst
    You can declare your exception in a package specification, and then it is visible to all code:
    PHP Code:
    SQLcreate package pkg is
      2    my_exception exception
    ;
      
    3  end;
      
    4  /

    Package created.

    SQLcreate or replace procedure proc1 is
      2  begin
      3    raise pkg
    .my_exception;
      
    4end;
      
    5  /

    Procedure created.

    SQLcreate or replace procedure proc2 is
      2  begin
      3    proc1
    ;
      
    4  exception
      5    when pkg
    .my_exception then
      6      raise_application_error
    (-20001,'My exception was raised' );
      
    7    when others then 
      8      raise_application_error
    (-20002,'Some other exception was raised');
      
    9  end;
     
    10  /

    Procedure created.

    SQLexec proc2
    begin proc2
    end;

    *
    ERROR at line 1:
    ORA-20001My exception was raised
    ORA
    -06512at "TANDREWS.PROC2"line 6
    ORA
    -06512at line 1 
    i have just one question about what u wrote :
    The number of the exception you raised with raise_application_error is predifined by oracle, or do we have some errors we can define wtih a number which is not an oracle exception ? If yes, which range can we use ?

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Defining my own message error on my own error

    In raise_application_error you can use error numbers between -20000 and -20999; these numbers have been set aside for user-defined errors.

  7. #7
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55

    Re: Defining my own message error on my own error

    Originally posted by andrewst
    In raise_application_error you can use error numbers between -20000 and -20999; these numbers have been set aside for user-defined errors.
    Okay, thx a lot.
    I'll try it.

    Regards.

Posting Permissions

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