Home » SQL & PL/SQL » SQL & PL/SQL » How to get error number for exception_init
How to get error number for exception_init [message #347612] Fri, 12 September 2008 05:55 Go to next message
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 Go to previous message
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

Previous Topic: SQL against VARRAY returning subscripts/sequences ?
Next Topic: value from Procedure
Goto Forum:
  


Current Time: Tue Feb 11 02:00:24 CST 2025