Home » SQL & PL/SQL » SQL & PL/SQL » continue after except. raised
continue after except. raised [message #255253] Tue, 31 July 2007 03:46 Go to next message
dusoo
Messages: 41
Registered: March 2007
Member
Hi,
what i need to do, when an expection is raised, to do something in the exception block and then just continue with other operations using inserted value in that expection block?

example

declare
i_date date;
v_fid number;

begin
for i in (select * from temp_a where date > i_date)
loop
begin

select fid into v_fid from temp_b where colb_1 = i.cola_1;
-- When no data found, insert into temp_b new fid value in the excep. block

-- later here, i need to use the selected / inserted fid ...
insert into temp_c (fid, colc_5, colc_8) values (V_FID, i.cola_5, i.cola_8);

exception
when no_data_found
then insert into temp_b (fid, colb_1) values (tempb_sequence.next_val, i.cola_1);
end;
end loop;
end;

thanks a lot for any ideas.
Re: continue after except. raised [message #255255 is a reply to message #255253] Tue, 31 July 2007 03:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
First,
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Then you can embed PL/SQL blocks into other ones:
begin
  begin
    <do something>
  exception when ... then <local handler>
  end;
  <continue work>
exception
  when ... then <general handler>
end;
/

Regards
Michel

[Updated on: Tue, 31 July 2007 03:51]

Report message to a moderator

Re: continue after except. raised [message #255260 is a reply to message #255255] Tue, 31 July 2007 04:01 Go to previous messageGo to next message
dusoo
Messages: 41
Registered: March 2007
Member
aah, yes. You're right, it's for ora 9 by the way.
And i was thinking, if is there other workarround then adding extra begin/end block inside. Like adding "continue" string in the exception block by keeping the value from the exception block ..
thanks anyway
Re: continue after except. raised [message #255270 is a reply to message #255260] Tue, 31 July 2007 04:33 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
No, there is no "continue", this is the way to do it in PL/SQL.

Regards
Michel
Previous Topic: birth day date
Next Topic: Duplicate Record and Update
Goto Forum:
  


Current Time: Wed Feb 12 17:25:56 CST 2025