pragma exception_init [message #123334] |
Sun, 12 June 2005 03:12 |
raji.s
Messages: 52 Registered: February 2005
|
Member |
|
|
why does oracle is not allowing me to
associate a user def exception eith error code ORA-01403.
for eg--
SQL> declare
2 abc exception;
3 pragma exception_init(abc,-1403);
4
5 begin
6 raise abc;
7 exception when abc then
8 dbms_output.put_line('raised due to abc');
9 end;
10 /
abc exception;
*
ERROR at line 2:
ORA-06550: line 2, column 4:
PLS-00701: illegal ORACLE error number -1403 for PRAGMA EXCEPTION_INIT
Can anyone explain please.
***********************************************************
it allows to associate too_many_rows i.e -1422,
dup_val_on_index, -1 and
all .
|
|
|
|
Re: pragma exception_init [message #123387 is a reply to message #123334] |
Mon, 13 June 2005 02:14 |
|
Barbara Boehmer
Messages: 9090 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
In most cases the Oracle error and the sql code are the same number, like Oracle error ORA-01422 and sql code -1422. However, there are exceptions. For example, the sql code for Oracle error ORA-01403 is +100, not -1403. Here is a link to a section of the online documentation that lists them:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/07_errs.htm#784
Here is an example that demonstrates that this is so:
scott@ORA92> declare
2 abc exception;
3 pragma exception_init (abc, +100);
4 begin
5 raise abc;
6 exception when abc then
7 dbms_output.put_line ('raised due to abc');
8 end;
9 /
raised due to abc
PL/SQL procedure successfully completed.
scott@ORA92> declare
2 v_dummy varchar2(1);
3 begin
4 select dummy into v_dummy from dual where dummy = 'Y';
5 end;
6 /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
scott@ORA92> declare
2 abc exception;
3 pragma exception_init (abc, +100);
4 v_dummy varchar2(1);
5 begin
6 select dummy into v_dummy from dual where dummy = 'Y';
7 exception when abc then
8 dbms_output.put_line ('raised due to abc');
9 end;
10 /
raised due to abc
PL/SQL procedure successfully completed.
scott@ORA92>
|
|
|