Home » SQL & PL/SQL » SQL & PL/SQL » Unhandled user Exception- except handled
Unhandled user Exception- except handled [message #219017] Mon, 12 February 2007 10:26 Go to next message
SaraC
Messages: 81
Registered: August 2005
Member
Hi Can anyone help?

I have a piece of code that defines an exception in the declare section of my proc -

e_not_found EXCEPTION.

In the body of my proc I have a
OPEN vrc_tab1 FOR
	SELECT *
	FROM tab1
	WHERE tab1= 1;
	
FETCH vrc_tab1
INTO vrt_tab1;


IF vrc_tab1%NOTFOUND THEN
CLOSE vrc_tab1;
RAISE e_not_found;
END IF;

EXCEPTION
WHEN e_not_found THEN
RAISE;


When I run the code I get ORA-06510 Unhandled user defined exception pointing to the raise statement in the exception block.
I have checked and the code does go into the e_not_found block but then raises the error. There is no row in the table with the id 1 - I am trying to test what happens when the ref cursor evaluates to NOTFOUND. All variables are defined correctly etc
Have I missed something!
Thx
S
Re: Unhandled user Exception- except handled [message #219021 is a reply to message #219017] Mon, 12 February 2007 10:40 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
There is no row in the table with the id 1

As there are no rows, the control is going to the exception handler.
As you have RAISE command there, it is raising the same exception.
It is an expected behaviour only.

IF you want to ignore the exception, even there are no rows, you have to give NULL in the specific exception handler section.
EXCEPTION
WHEN e_not_found THEN
NULL;
By
Vamsi
Re: Unhandled user Exception- except handled [message #219029 is a reply to message #219017] Mon, 12 February 2007 11:03 Go to previous messageGo to next message
SaraC
Messages: 81
Registered: August 2005
Member
Thanks for your reply Vamsi.

I want the code to pass an exception back up to the calling procedure, to show there has been an error, I dont want it to go to the exception handler as it does and then say all is well which a NULL;statement in there would do - I hope that make sense.

I have added logs and I know that it goes into the %NOTFOUND section and raises the error as I expect it to - I just want it to pass back out that it didnt execute successfully. Thats what I thought the RAISE was doing in my WHEN e_not_found THEN section.

S
Re: Unhandled user Exception- except handled [message #219116 is a reply to message #219029] Tue, 13 February 2007 00:33 Go to previous messageGo to next message
frenchm
Messages: 4
Registered: February 2007
Junior Member
You haven't associated an Oracle error number with the exception. So ...

DECLARE
   --
   e_not_found EXCEPTION;
   --
BEGIN
   --
   RAISE e_not_found;
   --
EXCEPTION
   WHEN e_not_found THEN
      RAISE;
   --
END;


Gives you ...

ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 11

but ...

DECLARE
   --
   e_not_found EXCEPTION;
   PRAGMA EXCEPTION_INIT(e_not_found, -20001);
   --
BEGIN
   --
   RAISE e_not_found;
   --
EXCEPTION
   WHEN e_not_found THEN
      RAISE;
   --
END;


Gives you ...

ERROR at line 1:
ORA-20001:
ORA-06512: at line 12

Depending on what you're trying to do (ie. do you need a meaningful error message to propogate?), RAISE_APPLICATION_ERROR may fit your requirements better.
Re: Unhandled user Exception- except handled [message #219191 is a reply to message #219017] Tue, 13 February 2007 09:32 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

If I understand what you want then what you have done is basically correct. The %NOTFOUND raises the exception which by it's definition goes to the exception handler. Instead of just doing a RAISE in that you need to raise your relevant exception so that the calling program can handle. I'll see if I can explain that properly

calling proc
procedure calling_proc is
begin
.
.
.
   begin

      get_record;

   exception
      when record_not_found then
        error_trace('Record not found');
   end;
.
.
.
end calling_proc;


called procedure

procedure get_record is
begin

-- Your code 
OPEN vrc_tab1 FOR
	SELECT *
	FROM tab1
	WHERE tab1= 1;
	
FETCH vrc_tab1
INTO vrt_tab1;


IF vrc_tab1%NOTFOUND THEN
CLOSE vrc_tab1;
RAISE e_not_found;
END IF;

EXCEPTION
WHEN e_not_found THEN
   RAISE calling_proc.record_not_found;

end called_proc;


So you call your procedure, which gets the %NOTFOUND error, raises your exception, goes to the exception handler, which raises the calling packages exception, which is handled in the calling program.

This is just an example (probably a bad one Embarassed ) but hopefully it'll point you in the right direction?
Re: Unhandled user Exception- except handled [message #219223 is a reply to message #219017] Tue, 13 February 2007 10:51 Go to previous message
SaraC
Messages: 81
Registered: August 2005
Member
Thanks for your help. will try that.

S
Previous Topic: error:service not avail
Next Topic: Query do not show the time 00:00:00
Goto Forum:
  


Current Time: Fri Dec 09 05:40:50 CST 2016

Total time taken to generate the page: 0.13627 seconds