Home » SQL & PL/SQL » SQL & PL/SQL » Max File length with utl_file (Oracle 10g, Sun Solaris)
Max File length with utl_file [message #404679] Fri, 22 May 2009 15:00 Go to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
I have written a procedure that will open a file, read the content and display it back to my browser. It works great on files that are up to about 77kb in size. Anything greater, and I keep on getting the following error...

ERROR:
ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06512: at line 1


Is there a maximum size file that utl_file.fopen can open, or am I having some other problem I don't recognize.

The code is really pretty simple at this point...

 declare
   vSFile UTL_FILE.FILE_TYPE;
   vNewLine VARCHAR2(32767);
 BEGIN
   vSFile := UTL_FILE.FOPEN ('CUSTOMER_REPORTS','090810204_2427_1124.txt','r',32767);
   IF UTL_FILE.IS_OPEN(vSFile) THEN
      utl_file.get_line (vSFile, vNewLine);
      dbms_output.put_line(vNewLine);
      utl_file.get_line (vSFile, vNewLine);
      dbms_output.put_line(vNewLine);
      utl_file.get_line (vSFile, vNewLine);
      dbms_output.put_line(vNewLine);
      utl_file.get_line (vSFile, vNewLine);
      dbms_output.put_line(vNewLine);
      utl_file.get_line (vSFile, vNewLine);
      dbms_output.put_line(vNewLine);
      utl_file.fclose(vSFile);
   END IF;
  end;


and, like I say, it works on smaller files, but not my bigger files.

Any hints or suggestions would be appreciated.

Regards,
Ron
Re: Max File length with utl_file [message #404698 is a reply to message #404679] Fri, 22 May 2009 19:35 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
SQL> @utl_file
SQL> declare
  2  	vSFile UTL_FILE.FILE_TYPE;
  3  	vNewLine VARCHAR2(32400);
  4   BEGIN
  5  	vSFile := UTL_FILE.FOPEN ('CUSTOMER_REPORTS','090810204_2427_1124.txt','r',32400);
  6  	IF UTL_FILE.IS_OPEN(vSFile) THEN
  7  	   utl_file.get_line (vSFile, vNewLine);
  8  	   dbms_output.put_line(vNewLine);
  9  	   utl_file.get_line (vSFile, vNewLine);
 10  	   dbms_output.put_line(vNewLine);
 11  	   utl_file.get_line (vSFile, vNewLine);
 12  	   dbms_output.put_line(vNewLine);
 13  	   utl_file.get_line (vSFile, vNewLine);
 14  	   dbms_output.put_line(vNewLine);
 15  	   utl_file.get_line (vSFile, vNewLine);
 16  	   dbms_output.put_line(vNewLine);
 17  	   utl_file.fclose(vSFile);
 18  	END IF;
 19    end;
 20  /

PL/SQL procedure successfully completed.

SQL> !ls -l /tmp/cr/090810204_2427_1124.txt
-rw-r--r-- 1 bcm oinstall 8673050 2009-05-22 17:30 /tmp/cr/090810204_2427_1124.txt


It works for me.
Re: Max File length with utl_file [message #404736 is a reply to message #404679] Sat, 23 May 2009 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> declare
  2     vSFile UTL_FILE.FILE_TYPE;
  3     vNewLine VARCHAR2(32767);
  4   BEGIN
  5     vSFile := UTL_FILE.FOPEN ('CUSTOMER_REPORTS','090810204_2427_1124.txt','r',32767);
  6     IF UTL_FILE.IS_OPEN(vSFile) THEN
  7        utl_file.get_line (vSFile, vNewLine);
  8        dbms_output.put_line(vNewLine);
  9        utl_file.get_line (vSFile, vNewLine);
 10        dbms_output.put_line(vNewLine);
 11        utl_file.get_line (vSFile, vNewLine);
 12        dbms_output.put_line(vNewLine);
 13        utl_file.get_line (vSFile, vNewLine);
 14        dbms_output.put_line(vNewLine);
 15        utl_file.get_line (vSFile, vNewLine);
 16        dbms_output.put_line(vNewLine);
 17        utl_file.fclose(vSFile);
 18     END IF;
 19    end;
 20  /
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890

PL/SQL procedure successfully completed.

SQL> @v

Version Oracle : 10.2.0.4.0

SQL> host dir E:\Oracle\Work\MIKA\090810204_2427_1124.txt
...
23/05/2009  08:47           102 000 090810204_2427_1124.txt
...

Works for me too, what is your Oracle version with 4 decimals?

Regards
Michel
Re: Max File length with utl_file [message #405171 is a reply to message #404736] Tue, 26 May 2009 13:21 Go to previous message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Well, I don't know what "magical thing" happened over the weekend, but when I ran the procedure this morning, it worked properly for all files. The only thing I can come up with is a caching problem with Oracle Portal 10g.

In any event, the procedure is working as it's supposed to, and both my customers and I are quite happy.

Thanks for the replies.

Regards, Ron
Previous Topic: how to load default picture in blob field
Next Topic: Oracle Regular Expression
Goto Forum:
  


Current Time: Sun Dec 11 02:06:07 CST 2016

Total time taken to generate the page: 0.04152 seconds