Home » SQL & PL/SQL » SQL & PL/SQL » regarding pragma_exception_init (Oracle 9i Release 2, Windows XP)
regarding pragma_exception_init [message #314120] Tue, 15 April 2008 16:12 Go to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hi Friends,
First let me thank you for your suggestions and help you all have offered in solving my previous problems.
Now coming to the point. I am currently working out on EXCEPTIONS. Though I have dealt with all these topics earlier due to a long break as a PL/SQL developer I am unable to recollect some parts.

As per my study: PRAGMA_EXCEPTION_INIT turns a Oracle Error into a named exception. Even in http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_packages.htm#sthref925http://download-uk.oracle.com/docs/cd/B 19306_01/appdev.102/b14251/adfns_packages.htm#sthref925 I have noted as follows:
Quote:
WHEN clause requires an exception name. If the exception that is raised does not have a name, such as those generated with RAISE_APPLICATION_ERROR, then one can be assigned using PRAGMA_EXCEPTION_INIT, as shown in the following example:


DECLARE
    ...
    Null_salary EXCEPTION;
    PRAGMA EXCEPTION_INIT(Null_salary, -20101);
BEGIN
    ...
    RAISE_APPLICATION_ERROR(-20101, 'salary is missing');
    ...
EXCEPTION
    WHEN Null_salary THEN


Now I have a procedure
create or replace procedure vam_del_dept_sp
  (v_deptid IN dept.deptno%type)
  is
  begin
  delete from dept where deptno=v_deptid;
  end;


The procedure executes successfully.
Now I am trying to use the procedure as follows:
begin
vam_del_dept_sp(10);
end;
/

And the output is
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found
ORA-06512: at "SCOTT.VAM_DEL_DEPT_SP", line 5
ORA-06512: at line 2


Because it's a known fact Dept table is related to Emp table and 'Deptno' column in Dept has child records in EMP table.

Now I request you how can I handle this
Quote:
ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found

using PRAGMA_EXCEPTION_INIT in the above code. It would be helpful if you can provide me with the code snippets if possible. Hope I am clear with my problem. Please get back if you need some other clarifications from my side.
Thanks and regards,
Vamsi K Gummadi.
Re: regarding pragma_exception_init [message #314141 is a reply to message #314120] Tue, 15 April 2008 21:08 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi,

as you quoted from the documentation

> If the exception that is raised does not have a name, such as those generated with RAISE_APPLICATION_ERROR, then one can be assigned using PRAGMA_EXCEPTION_INIT, ...

user generated exceptions are just an example of those which shall be assigned using PRAGMA_EXCEPTION_INIT. The same mechanism may be used for pre-defined errors. After you did not handle it, you know its code as it was raised.

Just and addition from PL/SQL User's Guide and Reference:
EXCEPTION_INIT Pragma: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/exceptioninit_pragma.htm#sthref2654
Example: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm#BABGIIBI (also the chapter containing it contains useful information).

Re: regarding pragma_exception_init [message #315022 is a reply to message #314141] Fri, 18 April 2008 12:30 Go to previous messageGo to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hi Friends,
Thank you for the support. My special thanks to flyboy for his valuable suggestion. As per the information provided I have used PRAGMA_EXCEPTION_INIT in a procedure as follows:
CREATE OR REPLACE procedure vam_del_dept_sp
  (v_deptid IN dept.deptno%type)
  is
  parent_detected exception;
  pragma_exception_init(parent_detected,-02292);
  begin
  delete from dept where deptno=v_deptid;
  exception
  when parent_detected then
  dbms_output.put_line(v_deptid||'has a child record in emp table');
  end;
/

When I tried to compile using TOAD for Oracle the error is as follows:
Quote:
PLS-00103: Encountered the symbol "(" when expecting one of the following:

constant exception <an identifier>
<a double-quoted delimited-identifier> table LONG_ double ref
char time timestam

and as per Knowledge Expert Instant Message
Quote:
PLS-00103 found 'string' but expected one of the following: 'string'"},

Cause: This error message is from the parser. It found a token (language element) that is inappropriate in this context.

Action: Check previous tokens as well as the one given in the error message. The line and column numbers given in the error message refer to the end of the faulty language construct.



I am unable to identify where I went wrong. So I request you to suggest me where I went wrong by making the necessary corrections in the code.
Thanks and regards,
Vamsi K Gummadi.
Re: regarding pragma_exception_init [message #315025 is a reply to message #315022] Fri, 18 April 2008 12:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Syntax is
pragma exception_init...

not
pragma_exception_init...

Regards
Michel

[Updated on: Fri, 18 April 2008 12:35]

Report message to a moderator

Re: regarding pragma_exception_init [message #315026 is a reply to message #315025] Fri, 18 April 2008 12:41 Go to previous message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hi Michel Cadot,
Thanks for the correction. I have not noticed that upto now and instead of that I am checking the rest of the code. This is a realization to me showing how careful should we be in writing the code.
Thanks and regards,
Vamsi K Gummadi.
Previous Topic: order of element in a string array (collection)
Next Topic: procedure - subset of string ?
Goto Forum:
  


Current Time: Mon Dec 05 12:41:46 CST 2016

Total time taken to generate the page: 0.16955 seconds