RE: Exceptions

From: Larry Elkins <elkinsl_at_flash.net>
Date: Thu, 23 Aug 2001 05:51:14 -0700
Message-ID: <F001.00374F7A.20010823054622@fatcity.com>

Leo,

Include a sub-block within the main block and include exception handling in the sub-block. The sub-block will contain the code for which you want to trap the error and continue/fail:

begin
  for c_rec in (select ename, sal from emp) loop

      begin
        insert into foo (ename, sal) values
(c_rec.ename||'XXXXXXXXXXXXXXXXXX',c_rec.sal);
        exception when others then dbms_output.put_line(C_Rec.Ename|| '
'||sqlerrm);
      end;

  end loop;
  exception when others then null;
end;

In the above example, the insert will fail due to ENAME being too big for foo.ename. Because the exception was handled in the sub-block's exception handler and no exception raised, the loop processing continued in the outer block. You will need to modify the exception handlers and code to meet your specific needs.

Also refer to the PL/SQL Users Guide, Chapter 6: "Error Handling", Section: "Useful Techniques", Sub Section: "Continuing After an Exception is Raised" for more details and examples. The section "How Exceptions Propagate", also in chapter 6, will help give you a bit more understanding.

I don't mean the above to sound like an "RTM" reply since I know it's difficult to find any documentation at many sites. If you don't have the manuals, harcopy/pdf/html, you can always use tahiti.oracle.com which includes search capabilities across manuals, much like the html version of the docs and the information navigator.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of
> Leo_Rajiv_at_satyam.com
> Sent: Thursday, August 23, 2001 3:21 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Exceptions
>
>
> Hi everyoene,
> Is there any way I could cause my pl/sql to proceed with
> executuion when an
> exception is raised? Im thinking of a kind of 'On Error Resume Next' thing
> we've got in VB. Ive got a loop and I need to continue executing
> the code in
> the loop after when an exception is raised and after I do some
> processing in
> the exception block.Pls send me your suggestions.
>
> Thanks and Regards,
> Leo.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Aug 23 2001 - 07:51:14 CDT