Home » SQL & PL/SQL » SQL & PL/SQL » How to ensure uniqueness on RAISE_APPLICATION_ERROR
How to ensure uniqueness on RAISE_APPLICATION_ERROR [message #670710] Mon, 23 July 2018 00:28 Go to next message
ramya29p
Messages: 144
Registered: November 2007
Location: Chennai
Senior Member
Hi ,
CREATE OR REPLACE PACKAGE p_exception

error_no1 NUMBER := -20001;
error_no2 NUMBER := -20002;
error_no3 NUMBER := -20003;
error_no1 NUMBER := -20004;

error_msg1 varchar2(100) := 'Message 1';
error_msg2 varchar2(100) := 'Message 2';
error_msg3 varchar2(100) := 'Message 3';
error_msg4 varchar2(100) := 'Message 4';

END;
/

CREATE OR REPLACE PACKAGE BODY p_exception
PROCEDURE raise_exception(p_error_in NUMBER, p_message VARCHAR2 DEFAULT NULL) AS
    
  BEGIN   
   
    RAISE_APPLICATION_ERROR(p_error_in, p_message);
    
    END;
END;

I have create the above package for ensuring uniqueness while raising RAISE_APPLICATION_ERROR. raise_exception procedure would be called from different package.

Do we have any alternative way other than creating package to ensure uniqueness while raising RAISE_APPLICATION_ERROR.

I don't want to use the same Error number for throwing a another exception.

For Example if -200001 is already used then I don't want to use the same number for throwing another exception.


Could anyone suggest.
Re: How to ensure uniqueness on RAISE_APPLICATION_ERROR [message #670712 is a reply to message #670710] Mon, 23 July 2018 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The only way I see is to dissociate the error passed to the procedure and the error raised by the procedure by enumerating the errors in an array inside the procedure itself or a package specification.

[Updated on: Mon, 23 July 2018 06:15]

Report message to a moderator

Re: How to ensure uniqueness on RAISE_APPLICATION_ERROR [message #670713 is a reply to message #670712] Mon, 23 July 2018 01:37 Go to previous messageGo to next message
ramya29p
Messages: 144
Registered: November 2007
Location: Chennai
Senior Member
But I want to ensure uniqueness at the schema level. The procedure can be called from many other packages within the schema.
Re: How to ensure uniqueness on RAISE_APPLICATION_ERROR [message #670714 is a reply to message #670713] Mon, 23 July 2018 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, this does not change my answer.

Re: How to ensure uniqueness on RAISE_APPLICATION_ERROR [message #670717 is a reply to message #670714] Mon, 23 July 2018 03:24 Go to previous messageGo to next message
cookiemonster
Messages: 13724
Registered: September 2008
Location: Rainy Manchester
Senior Member
You have one package in one schema that lists all the errors and have everything else refer to it.
Re: How to ensure uniqueness on RAISE_APPLICATION_ERROR [message #670744 is a reply to message #670717] Tue, 24 July 2018 01:49 Go to previous messageGo to next message
ramya29p
Messages: 144
Registered: November 2007
Location: Chennai
Senior Member
I am already mentioned the error details in the package specification. But I am asking do we have any other way to maintain uniqueness without using a package specification.
Re: How to ensure uniqueness on RAISE_APPLICATION_ERROR [message #670745 is a reply to message #670744] Tue, 24 July 2018 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use an array and its index as error number.

Re: How to ensure uniqueness on RAISE_APPLICATION_ERROR [message #670749 is a reply to message #670745] Tue, 24 July 2018 03:00 Go to previous messageGo to next message
ramya29p
Messages: 144
Registered: November 2007
Location: Chennai
Senior Member
would be great if you could share some example
Re: How to ensure uniqueness on RAISE_APPLICATION_ERROR [message #670761 is a reply to message #670749] Tue, 24 July 2018 13:13 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create or replace type errorType as object (
  2      error_name    varchar2(30),
  3      error_message varchar2(256)
  4    );
  5  /

Type created.

SQL> create or replace type errorArrayType as table of errorType
  2  /

Type created.

SQL> create or replace package pkg_errors as
  2    g_errors errorArrayType := errorArrayType (
  3      errorType('my_error',      'My error is raised'),
  4      errorType('another_error', 'This is another error'),
  5      errorType('third_error',   'Well, guess!')
  6    );
  7    procedure raise_error (p_name varchar2);
  8  end;
  9  /

Package created.

SQL> create or replace package body pkg_errors as
  2    procedure raise_error (p_name varchar2) is
  3      i pls_integer;
  4    begin
  5      i := 1;
  6      while i <= g_errors.count loop
  7        if g_errors(i).error_name = p_name then
  8          raise_application_error (-20000-i, g_errors(i).error_message);
  9        end if;
 10        i := i + 1;
 11      end loop;
 12      raise_application_error(-20000, 'Unknown error: '||p_name);
 13    end;
 14  end;
 15  /

Package body created.

SQL> exec pkg_errors.raise_error('my_error')
BEGIN pkg_errors.raise_error('my_error'); END;

*
ERROR at line 1:
ORA-20001: My error is raised
ORA-06512: at "MICHEL.PKG_ERRORS", line 8
ORA-06512: at line 1


SQL> exec pkg_errors.raise_error('third_error')
BEGIN pkg_errors.raise_error('third_error'); END;

*
ERROR at line 1:
ORA-20003: Well, guess!
ORA-06512: at "MICHEL.PKG_ERRORS", line 8
ORA-06512: at line 1


SQL> exec pkg_errors.raise_error('XXX')
BEGIN pkg_errors.raise_error('XXX'); END;

*
ERROR at line 1:
ORA-20000: Unknown error: XXX
ORA-06512: at "MICHEL.PKG_ERRORS", line 12
ORA-06512: at line 1
When you want to add a new error you just have to add it to the initialization part of "g_errors" variable.
Re: How to ensure uniqueness on RAISE_APPLICATION_ERROR [message #670790 is a reply to message #670761] Thu, 26 July 2018 00:07 Go to previous messageGo to next message
ramya29p
Messages: 144
Registered: November 2007
Location: Chennai
Senior Member
Hi Michel,
I want to use RAISE_APPLICATION_ERROR from different packages[PACKAGE1 , PACKAGE2 , PACKAGE3 etc..]. also want to maintain uniqueness on the error number. And dont want to maintain the error details separately in a pacage.
Re: How to ensure uniqueness on RAISE_APPLICATION_ERROR [message #670791 is a reply to message #670790] Thu, 26 July 2018 00:13 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Then your only other solution is to maintain the errors in a SQL table.

[Updated on: Thu, 26 July 2018 00:13]

Report message to a moderator

Re: How to ensure uniqueness on RAISE_APPLICATION_ERROR [message #670792 is a reply to message #670790] Thu, 26 July 2018 00:15 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
And dont want to maintain the error details separately in a pacage.
So what is "CREATE OR REPLACE PACKAGE p_exception..." in your first post?

Re: How to ensure uniqueness on RAISE_APPLICATION_ERROR [message #670793 is a reply to message #670792] Thu, 26 July 2018 00:58 Go to previous messageGo to next message
ramya29p
Messages: 144
Registered: November 2007
Location: Chennai
Senior Member
Currently i have defined all the error details to be raised on a common package specification. But i dont want to go with this method. That is why asking for other way so that i could maintain uniqueness on error number.
Re: How to ensure uniqueness on RAISE_APPLICATION_ERROR [message #670794 is a reply to message #670793] Thu, 26 July 2018 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And I answered you.

Re: How to ensure uniqueness on RAISE_APPLICATION_ERROR [message #670797 is a reply to message #670794] Thu, 26 July 2018 03:27 Go to previous message
cookiemonster
Messages: 13724
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't maintain uniqueness without maintaining a unique list and referring to it everywhere you raise errors.
There are only two types of objects in oracle that can hold unique lists - arrays and tables.

To use an array it needs to be persistent, global and initialized the first time a session uses it - so it needs a package.
Tables are more permanent but you're still going to need a procedure/function (probably in a package) to control the code that's used to check it.
Previous Topic: passing date value to the query from sql*plus
Next Topic: Possibility of Name
Goto Forum:
  


Current Time: Tue Nov 19 09:33:39 CST 2019