Re: PLP/SQL problem in dbms_output.put

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 04 Sep 1999 08:43:52 -0400
Message-ID: <MhPRNxStqgQ=Nt4F1nNY922OGE=K@4ax.com>

A copy of this was sent to "Chow Hoi Ka, Eric" <eric138_at_yahoo.com> (if that email address didn't require changing) On Sat, 04 Sep 1999 16:23:59 +0800, you wrote:

>Hello,
>
>When I want to output a strinig over 255 bytes using
>dbms_output.put_line(), it will be failed.
>Would you please to tell me howw to solve this ???
>
>Best regards,
>Eric

you have to break the line up. dbms_output is limited to 255 bytes as it is implemented with a plsql table type of varchar2(255). there is no way around this limit.

you can write your own procedure p like:

create or replace procedure p( p_string in varchar2 ) as
  l_n number default 1;
  l_len number default length(p_string); begin
  loop

      exit when l_n > l_len;
      dbms_output.put_line( substr( p_string, l_n, 255 ) );
      l_n := l_n+255;

end loop;
end;

it will break a big line up -- it will introduce newlines into the text.

If you need to capture output on a single line, you can look into utl_file. In 8.0 and before, it was limited to 1033 bytes/line. In 8.1 and up, its 32k/line.

--
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 Sat Sep 04 1999 - 07:43:52 CDT

Re: PLP/SQL problem in dbms_output.put_line ????