| how to handle exceptions in stored procedures? [message #85758] |
Tue, 27 July 2004 21:02  |
G.Srinivasa Rao
Messages: 7
Registered: January 2003
|
Junior Member |
|
|
hi every body,
i am working on stored procedures with forms6i. i am unable to provide a userfriendly message to the end user. please go thru the sample example.
I created a stored procedure successfully with the following script.
Create or replace procedure get_ename (emp_no in number, ename out varchar2) as begin select ename into ename from emp where empno=emp_no; exception when no_data_found then raise_application_error(-20009,'No such employee.'); end;
i created a form with two items empno,ename and when i call the stored procedure get_ename(1254,:ename) i am getting unhandled exception error with ORA-20009 but the message is not coming.
can any body help me in this regard.
Thanks in advance.
Srinivas
|
|
|
|
| Re: how to handle exceptions in stored procedures? [message #85767 is a reply to message #85758] |
Wed, 28 July 2004 03:46  |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Hi,
Instead of making use of Raise application error, make use of user defined messages as follows:
Create or replace procedure get_ename (emp_no in number, ename out varchar2) as
begin
select ename into ename from emp where empno=emp_no; exception
when no_data_found then
Message('No such employee.',No_Acknowledge);
Raise Form_trigger_failure;
When Others then
Message(Sqlerrm,No_Acknowledge);
Raise Form_trigger_failure;
end;
HTH
Regards
Himanshu
|
|
|
|
|
|
| Re: how to handle exceptions in stored procedures? [message #85796 is a reply to message #85792] |
Thu, 29 July 2004 06:00 |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Srinivas,
Now your requirment is clear to me.
What you have to do here is like this:
Create or replace procedure get_ename (emp_no in number, ename out varchar2,errmsg out varchar2,errcd outnumber) as
begin
select ename into ename from emp where empno=emp_no;
errcd:=0;
errmsg:=null;
exception
when no_data_found then
errcd:=-1;
errmsg:='No such employee.';
--Raise Apllication_error or what ever you want;
When Others then
errcd:=-1;
errmsg:=sqlerrm;
end;
Here Errmsg & errcd are out parameters.
Now from your form call this Procedure as follows:
Declare
a number(10):=1000;
b varchar2(50);
c varchar2(2000);
d number(10);
Begin
get_ename (a,b,c,d);
if d=-1 then
Message(c,No_acknowledge);
end if;
end;
HTH
Regards
Himanshu
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
