| regarding pragma_exception_init [message #314120] |
Tue, 15 April 2008 16:12  |
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 #315022 is a reply to message #314141] |
Fri, 18 April 2008 12:30  |
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 #315026 is a reply to message #315025] |
Fri, 18 April 2008 12:41 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
