Home » SQL & PL/SQL » SQL & PL/SQL » ERROR : about utl_file package ....................
ERROR : about utl_file package .................... [message #231182] Sun, 15 April 2007 17:54 Go to next message
m_arafa
Messages: 36
Registered: June 2006
Member
hi all,

i write ...

declare
f utl_file.file_type;
str varchar2(32767);
begin
for i in 1..50 loop <<=================== 50
str := str || 'Hello World! ';
end loop;
f := utl_file.fopen('c:\', 'mm.txt', 'w');
utl_file.put(f, str );
utl_file.fclose(f);
end;
/

PL/SQL procedure successfully completed.


but when i type ....

declare
f utl_file.file_type;
str varchar2(32767);
begin
for i in 1..5000 loop <<=================== 5000
str := str || 'Hello World! ';
end loop;
f := utl_file.fopen('c:\', 'mm.txt', 'w');
utl_file.put(f, str );
utl_file.fclose(f);
end;
/

that return an errors ...

ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 125
ORA-06512: at "SYS.UTL_FILE", line 384
ORA-06512: at line 10


I try to convert varchar2(32767) to long, but i return with same error , and to clob but it give me error about extend temp segment ..........

i don't know, Why?


thanx,
Re: ERROR : about utl_file package .................... [message #231183 is a reply to message #231182] Sun, 15 April 2007 18:02 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
According to
http://www.orafaq.com/forum/t/42428/74940/
you should be using "code tags" & you are NOT doing so.
It might help if we (TINW) could know for sure which line is #10.
It might help if you had searched this forum for similar problem BEFORE posting this FAQ.


HTH & YMMV
Re: ERROR : about utl_file package .................... [message #231221 is a reply to message #231182] Mon, 16 April 2007 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
13 * 5000 = 65000 > 32767.
LONG PL/SQL type is limited to 32760 bytes as stated in the documentation: http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/datatypes.htm#sthref705

Regards
Michel
Re: ERROR : about utl_file package .................... [message #231261 is a reply to message #231182] Mon, 16 April 2007 02:41 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't think you do get that error.
When I run the code, I get a completely different error, caused by you trying to put 65000 characters of data into a string with a maximum size of 32767 characters:
SQL> declare
  2  f utl_file.file_type;
  3  str varchar2(32767);
  4  begin
  5  for i in 1..5000 loop -- <<=================== 5000
  6  str := str || 'Hello World! ';
  7  end loop;
  8  f := utl_file.fopen('c:\', 'mm.txt', 'w');
  9  utl_file.put(f, str );
 10  utl_file.fclose(f);
 11  end;
 12  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 6
Previous Topic: MERGE - Statement
Next Topic: error in redaing clob record
Goto Forum:
  


Current Time: Wed Dec 07 18:40:50 CST 2016

Total time taken to generate the page: 0.08712 seconds