| printing a variable larger than 225 [message #277575] |
Tue, 30 October 2007 11:31  |
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 #277580 is a reply to message #277577] |
Tue, 30 October 2007 12:01  |
ThomasG
Messages: 3212
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 #277693 is a reply to message #277575] |
Wed, 31 October 2007 03:57 |
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 |
Bill B
Messages: 1971
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;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
