How to get error number for exception_init [message #347612] |
Fri, 12 September 2008 05:55  |
harshadsp
Messages: 100 Registered: August 2008
|
Senior Member |

|
|
Hi,
My question is how could I know error number while coding for pragma exception_init.
e.g. I know when we try to insert null values into NOT NULL column.we get ORA-1400
ORA-01400: cannot insert NULL into ("SCOTT"."RESULTS"."TESTNO") Since i know this in advance so I can easily associate this -01400 number with user defined error message.
SQL> CREATE OR REPLACE PROCEDURE prcexp IS
2
3 expnull EXCEPTION;
4 PRAGMA EXCEPTION_INIT(expNull, -01400);
5
6 BEGIN
7 INSERT INTO emp
8 (empno)
9 VALUES
10 (null);
11 COMMIT;
12 EXCEPTION
13 WHEN expnull THEN
14 dbms_output.put_line('ERROR: Enter empno');
15 END;
16 /
Procedure created.
SQL> exec prcexp;
ERROR: Enter empno
PL/SQL procedure successfully completed.
I wanted to know is there any way that I can easily trap error code dynamically and asscoiate it with Pragama Exception_init?
Thanks & regards
Harshad
|
|
|
Re: How to get error number for exception_init [message #347637 is a reply to message #347612] |
Fri, 12 September 2008 08:15  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Obviously if you want to trap an error, you know which error it is and so you know its number or at least you know how to create a test case to know it. Otherwise you can scan Database Error Messages.
The other errors must not be trapped in an exception block.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Carefully choose your forum. "Suggestions & Feedback" is for comment on the site. PL/SQL question should be in... "SQL & PL/SQL" forum.
Regards
Michel
[Updated on: Fri, 12 September 2008 08:17] Report message to a moderator
|
|
|