How to ensure uniqueness on RAISE_APPLICATION_ERROR [message #670710] |
Mon, 23 July 2018 00:28 |
ramya29p
Messages: 146 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 #670761 is a reply to message #670749] |
Tue, 24 July 2018 13:13 |
|
Michel Cadot
Messages: 68693 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 #670797 is a reply to message #670794] |
Thu, 26 July 2018 03:27 |
cookiemonster
Messages: 13943 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.
|
|
|