OraFAQ Forum: SQL & PL/SQL » line Length Overflow, limit of 255 bytes per line ERROR

Home » SQL & PL/SQL » SQL & PL/SQL » line Length Overflow, limit of 255 bytes per line ERROR

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

line Length Overflow, limit of 255 bytes per line ERROR [message #42113]

Sun, 02 March 2003 12:09

neel mukherjee
Messages: 3
Registered: February 2003

Junior Member

Hello to all Oracle gurus.
I am having a problem where I create a really long string (which is basically a dynamicaly created update statment where I keep generating the column name and their respective values) and try to store it in a variable. The error mesage I get is

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

Is there any way of circumventing this problem - of storing a long string and then executing it with the "execute immediate" COMMAND. Please let me know
Thx in advance

Report message to a moderator

Re: line Length Overflow, limit of 255 bytes per line ERROR [message #42116 is a reply to message #42113]

Sun, 02 March 2003 15:55

Paul
Messages: 164
Registered: April 1999

Senior Member

if you have some way of keeping track of how many characters have been added to the string, you could concatenate CHR(13) || CHR(10) into it at intervals to force line feeds.

Hope this helps,
Paul

Report message to a moderator

Re: line Length Overflow, limit of 255 bytes per line ERROR [message #42142 is a reply to message #42113]

Mon, 03 March 2003 11:23

Ivan
Messages: 180
Registered: June 2000

Senior Member

I created a procedure that displays a string of up to 32767 (VARCHAR2 limit) characters (or should I say bytes) long

Here's the code

CREATE OR REPLACE PROCEDURE DISPLAY_LINE
(inString VARCHAR2,
inLineLength NUMBER DEFAULT 80) IS
vRemString VARCHAR2 (32767) := inString;
vLineLength NUMBER := inLineLength;
vPos NUMBER;
BEGIN
IF NVL (vLineLength, 0) <= 0 OR
vLineLength > 255 THEN
vLineLength := 80;
END IF;

WHILE LENGTH (vRemString) > vLineLength LOOP
IF INSTR (SUBSTR (vRemString, 1, vLineLength), CHR (10), -1) > 0 THEN
vPos := INSTR (SUBSTR (vRemString, 1, vLineLength), CHR (10), -1);
ELSE
vPos := INSTR (SUBSTR (vRemString, 1, vLineLength), ' ', -1);
END IF;

IF vPos = 0 THEN
DBMS_OUTPUT.PUT_LINE (SUBSTR (vRemString, 1, vLineLength));
vRemString := SUBSTR (vRemString, vLineLength + 1);
ELSE
DBMS_OUTPUT.PUT_LINE (SUBSTR (vRemString, 1, vPos - 1));
vRemString := SUBSTR (vRemString, vPos + 1);
END IF;
END LOOP;

DBMS_OUTPUT.PUT_LINE (vRemString);
END;
/

Make sure you increase the size of the system output buffer to accommodate an output of more than 2000 (SQL*Plus default) characters.

DBMS_OUTPUT.ENABLE (1000000); -- Maximum allowed

Report message to a moderator

Previous Topic:	Displaying all fields in COUNT
Next Topic:	Using SQL IN Statement

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 22 02:02:52 CDT 2025