Oracle, Web, Script, SQLserver, Tips & Trick

October 28, 2010

ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line

Filed under: oracle — sikathabis @ 3:53 am
Tags:

Mengatasi error ketika menjalankan dbms_output.put_line melebihi 255 char

Tulisan ini dibuat oleh Karl Reitschuster dari link ini dan ini
Do you like the full featured supplied PL/SQL Package DBMS_OUTPUT? It seems to be the package which ranks in top position with it’s limitations😉

  • limited maximal output buffer
  • limited length of line (255)
  • limited support of output of data types like CLOB, XML, …

Some points will be worked out with Oracle 10.2 – but why Oracle let its users wait so long?

At the current project I developed a source code generator generating a table api with special demand for an OR-Mapper. For some easy usage the code templates are printed to output, stored as CLOB. After 255 digits line buffer exceeds i made this experience :

SET SERVEROUTPUT ON

DECLARE
  l_Str    VARCHAR2(1024) := ‘01234567890123456789012345678901234567890123456789’;
l_Xl_Str VARCHAR2(1024);
BEGIN
— constructing STRING WITH 255 chars
l_Xl_Str := l_Str || Chr(10) || l_Str || Chr(10) || l_Str || Chr(10) ||
l_Str || Chr(10) || l_Str || Chr(10);
Dbms_Output.Put_Line(‘LENGTH : ‘ || Length(l_Xl_Str));
Dbms_Output.Put_Line(l_Xl_Str);
— constructing STRING WITH 255 + chars
l_Xl_Str := l_Xl_Str || Chr(10) || l_Str;
Dbms_Output.Put_Line(‘LENGTH : ‘ || Length(l_Xl_Str));
Dbms_Output.Put_Line(l_Xl_Str);
END;
/

Output :

LENGTH : 255

01234567890123456789012345678901234567890123456789

01234567890123456789012345678901234567890123456789

01234567890123456789012345678901234567890123456789

01234567890123456789012345678901234567890123456789

01234567890123456789012345678901234567890123456789

LENGTH : 306

DECLARE
l_Str VARCHAR2(1024) := ‘01234567890123456789012345678901234567890123456789’;
l_Xl_Str VARCHAR2(1024);
BEGIN
— constructing STRING WITH 255 chars
l_Xl_Str := l_Str || Chr(10) || l_Str || Chr(10) || l_Str || Chr(10) ||
l_Str || Chr(10) || l_Str || Chr(10);
Dbms_Output.Put_Line(‘LENGTH : ‘ || Length(l_Xl_Str));
Dbms_Output.Put_Line(l_Xl_Str);
— constructing STRING WITH 255 + chars
l_Xl_Str := l_Xl_Str || Chr(10) || l_Str;
Dbms_Output.Put_Line(‘LENGTH : ‘ || Length(l_Xl_Str));
Dbms_Output.Put_Line(l_Xl_Str);
END;

ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line

ORA-06512: at "SYS.DBMS_OUTPUT", line 35

ORA-06512: at "SYS.DBMS_OUTPUT", line 133

ORA-06512: at line 11

Immediately i searched for a workaround on the web – even on Tom Kytes AskTom site i did not found something which really helped. So i had to find my own workaround. The Procedure can print maximal 32K sized string (with maximal 255 chars per line) due to the PL/SQL limitation. But it could be overloaded with CLOB data type parameter and the use of DBMS_LOB package inside of the procedure Put_Xl_Line :

DECLARE

l_Str VARCHAR2(1024) := ‘01234567890123456789012345678901234567890123456789’;
l_Xl_Str VARCHAR2(1024);
PROCEDURE Put_Xl_Line(p_Str IN VARCHAR2) IS
l_Length PLS_INTEGER := 0;
l_Offset PLS_INTEGER := 0;
l_Cr_Pos PLS_INTEGER := 0;
l_Line VARCHAR2(256);
BEGIN
l_Length := Length(p_Str);
l_Offset := 1;
WHILE l_Offset &Lt ; = l_Length LOOP
l_Cr_Pos := Instr(Substr(p_Str,
l_Offset,
255),
Chr(10));
— if last line without CR
IF (l_Cr_Pos = 0) THEN
l_Cr_Pos := l_Length – l_Offset + 2;
END IF;
l_Line := Substr(p_Str,
l_Offset,
l_Cr_Pos – 1);
Dbms_Output.Put_Line(l_Line);
l_Offset := l_Offset + l_Cr_Pos;
END LOOP;
END Put_Xl_Line;
BEGIN
l_Xl_Str := l_Str || Chr(10) || l_Str || Chr(10) || l_Str || Chr(10) ||
l_Str || Chr(10) || l_Str;
Dbms_Output.Put_Line(‘LENGTH : ‘ || Length(l_Xl_Str));
Put_Xl_Line(l_Xl_Str);
l_Xl_Str := l_Xl_Str || Chr(10) || l_Str;
Dbms_Output.Put_Line(‘LENGTH : ‘ || Length(l_Xl_Str));
Put_Xl_Line(l_Xl_Str);
END;
/

Output :

SQL>

LENGTH : 254

01234567890123456789012345678901234567890123456789

01234567890123456789012345678901234567890123456789

01234567890123456789012345678901234567890123456789

01234567890123456789012345678901234567890123456789

01234567890123456789012345678901234567890123456789

LENGTH : 305

01234567890123456789012345678901234567890123456789

01234567890123456789012345678901234567890123456789

01234567890123456789012345678901234567890123456789

01234567890123456789012345678901234567890123456789

01234567890123456789012345678901234567890123456789

01234567890123456789012345678901234567890123456789

PL/SQL procedure successfully completed

Wow it really worked!!

Carl😉

UPDATE : the procedure is able to ‘print’ large amount of generated code (32Kb) – but one code line may still not exceed 255 chars!
Carl; 19.11.2005
——————————————————————————————————–

The line size of a single line in the DBMS_OUTPUT buffer is limited to 255 characters. As such you must make sure that the line is terminated with an end-of-line marker before reaching this limit.
The end-of-line marker is written to the buffer using either the DBMS_OUTPUT.PUT_LINE or the BMS_OUTPUT.NEW_LINE procedure.
(From MetaLink)


Highlander

06-04-2002, 02:50 PM

If you really need it try this:
DECLARE
VAR NUMBER := 1;
BEGIN
WHILE VAR <= LENGTH(RESULTS) LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(RESULTS, I, 200));
I = I + 200;
END LOOP;
END;

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

Teknologi Pendidikan

Just another WordPress.com weblog

KATAKAMI

Jurnalisme Yang Dapat Dipercaya

Tonk Kosonk Baonk Bunyinya

Penampungan Serapah Tanpa Sumpah

Qey050489's Blog

Ayo Bangkit sobat..!!!!!

B. S. Totoraharjo

Orang Bodoh Yang Tak Kunjung Pandai

culturekitchen foodlab

New Food and Art in Yogyakarta

Wahyu Catur Wibowo

Musafir Dunia Maya

SQL.... Still Learning

My Reference on SQL Server

neinframe

Street Photography is like a box of a chocolates, you're never know what you're gonna get - Thomas Leuthard -

Keluarga Andrivito

-- Andrivito's Journal --

DRK™

This site is about Latest NEWS, Articles, Videos, Books, Course, Presentations, Tips & Tricks related to I.T Pro's

Gaurav Saxena

|| तत् त्वम् असि ||

Be a good Moeslim

Menuju Pribadi Muslim Sejati yang Bertaqwa

TuanSUFI

Manajemen Sholat, renungan spiritual, Teknologi Informasi

Pelangi di khatulistiwa....

Mari berbagi kebaikan, pengalaman dan senyum denganku

angkasa13

Ceritanya saya, Pikirannya saya, Hatinya saya

sikathabis

sikathabis semua informasi disini

%d bloggers like this: