Home » SQL & PL/SQL » SQL & PL/SQL » printing a variable larger than 225
printing a variable larger than 225 [message #277575] Tue, 30 October 2007 11:31 Go to next message
khresmoi
Messages: 24
Registered: October 2007
Junior Member
Hi,

i'm using a variable in function which is declred as varchar2(2000). this would be the out variable for the function.
Is there a way i can print this value? if i use dbms_output, error occurs as 225 if the max size which can be printed.

TIA..
Re: printing a variable larger than 225 [message #277576 is a reply to message #277575] Tue, 30 October 2007 11:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Before 10g the line width limit is 255.
No workaround.

Regards
Michel
Re: printing a variable larger than 225 [message #277577 is a reply to message #277575] Tue, 30 October 2007 11:37 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
UTL_FILE
Re: printing a variable larger than 225 [message #277580 is a reply to message #277577] Tue, 30 October 2007 12:01 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Now, that's funny.

According to this here :

http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/x/Utl_File/start.htm

Quote:

The line length limit for Utl_File.Get_Line and Utl_File.Put_Line has been increased from 1K to 32K.


For Oracle 9.2.0.0

But when I run this code :
DECLARE
  output_file  utl_file.file_type;
  v_test       VARCHAR(2000);
BEGIN
  output_file := utl_file.fopen ('/tmp','testfile', 'W');

  FOR i IN 1..113 LOOP
    v_test := v_test ||'123456789' ;
  END LOOP;

  utl_file.put_line(output_file, v_test);
  utl_file.fclose(output_file);
END;
/


It still works with 1..113 and creates a file with 1018 characters, but when I increase the loop to 1..114 it fails with an :

ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 562
ORA-06512: at line 12


Version :

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production


*Scratches Head*

It seems it's still limited to 1k per line on my box.
Re: printing a variable larger than 225 [message #277581 is a reply to message #277580] Tue, 30 October 2007 12:04 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Ah, found it.

Default maximal line legth is 1024, but can be set higher in the fopen, for example to 3000:

output_file := utl_file.fopen ('/tmp','testfile', 'W',3000);

[Updated on: Tue, 30 October 2007 12:06]

Report message to a moderator

Re: printing a variable larger than 225 [message #277583 is a reply to message #277577] Tue, 30 October 2007 12:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Note that there is an important difference between dbms_output and utl_file: one write on client and the other one on server.

Regards
Michel
Re: printing a variable larger than 225 [message #277638 is a reply to message #277575] Wed, 31 October 2007 00:50 Go to previous messageGo to next message
khresmoi
Messages: 24
Registered: October 2007
Junior Member
True michel.
i need to write it on client. What i've is Oracle 9i. So no work around? Sad
Re: printing a variable larger than 225 [message #277650 is a reply to message #277638] Wed, 31 October 2007 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can insert your lines in a table and then select this table into a spool.

Regards
Michel
Re: printing a variable larger than 225 [message #277693 is a reply to message #277575] Wed, 31 October 2007 03:57 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
If you don't mind a really really really clunky solution you could use a substring in DBMS_OUTPUT

dbms_output.put_line(substring(var1,1,255));
dbms_output.put_line(substring(var1,256,511));


It's not nice or elegant but it might suit certain circumstances.
Re: printing a variable larger than 225 [message #277813 is a reply to message #277575] Wed, 31 October 2007 14:34 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Just curious,

Why would you want to do a dbms_output to the screen greater then 255. However, if you need to go to 2000 characters, then use

declare
strt number;
string varchar2(2000);

begin
... fill string with junk

strt := 1;

loop
  if strt > length(string) then
    exit;
  end if;
  if strt + 250 > length(string) then
     dbms_output.put_line(substr(string,strt));
  else
     dbms_output.put(substr(string,strt,250);
  end if;
  strt := strt + 250;
end loop;
end;
/
Previous Topic: Creating Index while Adding Constraint
Next Topic: Return rows deleted Using PL/SQL
Goto Forum:
  


Current Time: Wed Dec 07 09:06:47 CST 2016

Total time taken to generate the page: 0.07884 seconds