Home » SQL & PL/SQL » SQL & PL/SQL » Exception handling (TOAD,oracle 10, win XP)
Exception handling [message #338769] Tue, 05 August 2008 21:30 Go to next message
saberfang
Messages: 11
Registered: June 2008
Location: M'sia
Junior Member
Hi all,

i got a situation here where i need an exception to be handled in an exception handler..

something like this:
loop

begin
select * into firstbilldate,bf,total_due from(select bill_date,bal_forward,prev_bal from temp_rinv where bal_forward<=0 and account=rec.account order by bill_date desc) where rownum<=1;

exception
when NO_DATA_FOUND then
select * into firstbilldate from (select bill_date from temp_rinv where account=rec.acocunt order by bill_date) where rownum<=1;

end;

bolded select stmt is to get the very first bill that having bring forward<=0 but some account will not have this kind of bill at all. so it goes to the exception handler.

in the exception handler the select stmt is to get the earliest bill date without any condition. but somehow there are some account that appears without any bills. so this select stmt will failed also in this kind of situation.

i need suggestion here, can i make another exception handler within the exception handler up there? or is there any other efficient way.

thanx

Re: Exception handling [message #338772 is a reply to message #338769] Tue, 05 August 2008 22:08 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated in URL above
Re: Exception handling [message #338773 is a reply to message #338772] Tue, 05 August 2008 22:11 Go to previous messageGo to next message
saberfang
Messages: 11
Registered: June 2008
Location: M'sia
Junior Member
anacedent wrote on Wed, 06 August 2008 11:08
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated in URL above



sry.. but whats wrong with my post? messy code?
tell me pls.. thanx
Re: Exception handling [message #338779 is a reply to message #338769] Tue, 05 August 2008 23:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It is possible to nest blocks. You can have a begin... exception... end structure within your exception handler.

Re: Exception handling [message #338781 is a reply to message #338769] Tue, 05 August 2008 23:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
saberfang wrote on Tue, 05 August 2008 19:30

... can i make another exception handler within the exception handler ...?



Yes, you can next your blocks and exceptions:

begin
  loop
    begin
      select *  
      into   firstbilldate,bf,total_due 
      from   (select bill_date,bal_forward,prev_bal 
              from   temp_rinv 
              where  bal_forward<=0 
              and    account=rec.account 
              order  by bill_date desc) 
      where  rownum<=1; 
    exception
    when NO_DATA_FOUND then
      begin
        select * 
        into   firstbilldate 
        from   (select bill_date 
                from   temp_rinv 
                where  account=rec.acocunt 
                order  by bill_date) 
        where  rownum<=1;
      exception
      when NO_DATA_FOUND then
        -- whatever you want to do here        
      end;
    end;
  end loop;
end;


Re: Exception handling [message #338796 is a reply to message #338769] Tue, 05 August 2008 23:37 Go to previous messageGo to next message
saberfang
Messages: 11
Registered: June 2008
Location: M'sia
Junior Member
thx for all the reply and suggestion..
Re: Exception handling [message #338798 is a reply to message #338796] Tue, 05 August 2008 23:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now I hope you will read the guide, format your post, don't use IM speak, give your version with 4 decimals...

Regards
Michel
Re: Exception handling [message #338858 is a reply to message #338798] Wed, 06 August 2008 02:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Realistically, I don't think that there is a version of Oracle which supports Pl/Sql in which the answer would have been different had that version number been supplied.
Re: Exception handling [message #338873 is a reply to message #338858] Wed, 06 August 2008 02:49 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Who knows if the next question he'll post will not depend on this. If we don't tell him, he will not do it in the next topic.

Regards
Michel


Previous Topic: Analytics functions help
Next Topic: can we use an outer joing instead of "NOT IN" and how? any example?
Goto Forum:
  


Current Time: Sun Dec 04 02:22:33 CST 2016

Total time taken to generate the page: 0.29320 seconds