Re: dbms_output.put_line - Handling ORU-10027 exceptions

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 29 Jul 1999 11:32:59 GMT
Message-ID: <37a13bb3.88387895@newshost.us.oracle.com>

A copy of this was sent to "Jim" <lermontovb_at_hotmail.com> (if that email address didn't require changing) On Thu, 29 Jul 1999 00:35:15 +0100, you wrote:

>Does anyone know if it's possible to catch one of these buffer overflow
>errors with an exception handler?
>
>I've tried the following but nothing in the exception handler seems to be
>executed.
>
>set serveroutput on size 2000
>
>declare
> v_Long_String Varchar2(30) := 'abcdefghijklmnopqrst';
> e_buffer exception;
> pragma exception_init(e_buffer, -10027); -- params might be the wrong
>way round - it was a long day
>begin
> for iCount in 1..300 loop
> begin
> dbms_output.put_line(v_Long_string);
> exception
> when e_buffer then
> null; -- or whatever
> when others then
> null; -- or whatever
> end;
> end loop;
>end;
>

the others is being triggered in this case. the sqlcode you need to map to is -20000, not 10027.

Here is an example (shows how to 'reset' the buffer to the bit bucket if you need to)

  1 declare
  2 v_Long_String Varchar2(30) := 'abcdefghijklmnopqrst';   3 e_buffer exception;
  4 pragma exception_init(e_buffer, -20000);

  5    bitbucket        dbms_output.chararr;
  6    numlines         number default 999999999999;
  7  begin
  8     for iCount in 1..3000 loop
  9          begin
 10              dbms_output.put_line(v_Long_string);
 11          exception
 12              when e_buffer then
 13                  dbms_output.get_lines( bitbucket, numlines );
 14                  dbms_output.put_line( 'Buffer Exceeded and caught' );
 15                  exit;
 16          end;

 17 end loop;
 18* end;
SQL> /
Buffer Exceeded and caught

PL/SQL procedure successfully completed.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jul 29 1999 - 06:32:59 CDT