Home » SQL & PL/SQL » SQL & PL/SQL » How to associate a user-defined exception with a message (merged)
How to associate a user-defined exception with a message (merged) [message #239264] Mon, 21 May 2007 20:33 Go to next message
wngsheng
Messages: 2
Registered: May 2007
Junior Member
Hi,
my code as following:


declare
exc_novalid exception;
pragma exception_init(exc_novalid,-20002);
begin
raise exc_novalid;
exception
when exc_novalid then
dbms_output.put_line('error code: '||sqlcode);
dbms_output.put_line('error description: '||sqlerrm);
end;
/


output is:
error code: -20002
error description: ora-20002

How can I associate error message with a exception variable?thanks advance.

[Updated on: Mon, 21 May 2007 20:34]

Report message to a moderator

How to associate a user-defined exception with a message [message #239266 is a reply to message #239264] Mon, 21 May 2007 20:49 Go to previous messageGo to next message
wngsheng
Messages: 2
Registered: May 2007
Junior Member
Hi,please help to resolve this problem.

my code is following:


declare
  exc1 exception;
  pragma exception_init(exc1,-20001);
begin
   raise exc1;
exception
  when exc1 then
    dbms_output.put_line('sqlcode:'||sqlcode);
    dbms_output.put_line('sqlerrm:'||sqlerrm);
end;




output is:
sqlcode:-20001
sqlerrm:ORA-20001:

How can I provide a user-defined exception with a error message? thanks!

[Updated on: Mon, 21 May 2007 22:07]

Report message to a moderator

Re: How to associate a user-defined exception with a message [message #239270 is a reply to message #239266] Mon, 21 May 2007 21:14 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Read & FOLLOW #1 STICKY post at top of this forum.
Do NOT cross/multi-post.
http://www.orafaq.com/forum/t/82110/74940/

How can the same problem be for both newbies & expert?
Re: How to associate a user-defined exception with a message [message #239273 is a reply to message #239270] Mon, 21 May 2007 21:21 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
may it help you

EXCEPTION
WHEN OTHERS THEN
DECLARE
lv_error_code_num NUMBER := SQLCODE;
lv_error_msg_txt VARCHAR2(300) := SQLERRM;
BEGIN
DBMS_OUTPUT.PUT_LINE('ERROR #' ||
TO_CHAR(lv_error_code_num) || ': ' || lv_error_msg_txt);
p_ret_code := -1;
p_ErrMsg := 'During TS_STATS UPDATE:' || lv_error_msg_txt;
END;
END;
WHEN OTHERS THEN
DECLARE
lv_error_code_num NUMBER := SQLCODE;
lv_error_msg_txt VARCHAR2(300) := SQLERRM;
BEGIN
DBMS_OUTPUT.PUT_LINE('ERROR #' || TO_CHAR(lv_error_code_num) || ':
' || lv_error_msg_txt);
p_ret_code := -1;
p_ErrMsg := 'During TS_STATS ADD: ' || lv_error_msg_txt;
END;

[Updated on: Mon, 21 May 2007 21:22]

Report message to a moderator

Re: about define exception variable [message #239274 is a reply to message #239264] Mon, 21 May 2007 21:29 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi,
try using RAISE_APPLICATION_ERROR instead.
Re: How to associate a user-defined exception with a message [message #239369 is a reply to message #239273] Tue, 22 May 2007 03:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What's wrong with the simple answer?
SQL> set serveroutput on
SQL> begin
  2     raise_application_error(-20020,'Out of Cheese Error.');
  3  exception
  4    when others then
  5      dbms_output.put_line('sqlcode:'||sqlcode);
  6      dbms_output.put_line('sqlerrm:'||sqlerrm);
  7  end;
  8  /
sqlcode:-20020
sqlerrm:ORA-20020: Out of Cheese Error.

PL/SQL procedure successfully completed.


As a general rule, don't use DBMS_OUTPUT for your exception reporting. If there is nothing listening for the output in your session (and in general there won't be in the real world) then all your information will disappear.
Re: How to associate a user-defined exception with a message [message #239372 is a reply to message #239273] Tue, 22 May 2007 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DreamzZ,

Why don't you read and apply How to format your posts as already asked to you?

In addition, WHEN OTHERS MUST always contains RAISE at the end. This is a good way of programming, I don't see any real case when you want to ignore any exception. Exception is part of the language, don't just trap and erase it unless it is a known and expected error in your program.

Regards
Michel
Re: How to associate a user-defined exception with a message [message #241341 is a reply to message #239266] Tue, 29 May 2007 05:25 Go to previous messageGo to next message
kdipankar
Messages: 9
Registered: December 2006
Junior Member
Hi,

Please have a look in the following two pl/sql blocks.

The first one is done using "raise_application_error" only.

declare

x number(10) := 0;

y number(10) := 100;

z number(10);

begin

if x = 0

then

raise_application_error(-20111,'Sorry,Division by Zero Not Allowed.');

else

z := y/x;

dbms_output.put_line(z);

end if;

exception

when others then

dbms_output.put_line(SQLCODE);

dbms_output.put_line(SQLERRM);

end;


The second one is done using "raise_application_error" & "PRAGMA EXCEPTION_INIT" both.


declare

x1 number(10) := 0;

y1 number(10) := 100;

z1 number(10);

usr_exp exception;

PRAGMA EXCEPTION_INIT(usr_exp, -20111);

begin

if x1 = 0

then

raise_application_error(-20111,'Sorry,Division by Zero Not Allowed.');

else

z1 := y1/x1;

dbms_output.put_line(z1);

end if;

exception

when usr_exp then

dbms_output.put_line(SQLCODE);

dbms_output.put_line(SQLERRM);

end;

Run both of them and the output is self explanatory.

regards,
Dipankar.
Re: How to associate a user-defined exception with a message [message #241353 is a reply to message #241341] Tue, 29 May 2007 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It explains what?
Please read and apply How to format your posts and remove all useless blank lines.

Regards
Michel
Re: How to associate a user-defined exception with a message [message #241363 is a reply to message #241353] Tue, 29 May 2007 06:23 Go to previous messageGo to next message
kdipankar
Messages: 9
Registered: December 2006
Junior Member
Hi Michel,

The first one shows the use of raise_application_error for user defined error messages and the next one shows the usage of both raise_application_error and pragma EXCEPTION_INIT for user defined error messages as well as user defined exception.

regards,
Dipankar.
Re: How to associate a user-defined exception with a message [message #241367 is a reply to message #241363] Tue, 29 May 2007 06:43 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, maybe is this the summary that OP needs to understand.

Regards
Michel
Previous Topic: numeric or value error
Next Topic: Query getting 2 extra rows. Could somebody crack it please?
Goto Forum:
  


Current Time: Fri Dec 09 21:19:30 CST 2016

Total time taken to generate the page: 0.10662 seconds