Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Stu.

Asked: February 22, 2001 - 12:03 pm UTC

Last updated: October 24, 2017 - 2:18 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

I developed the script and had it running on a different version of Oracle, 8.1.5, I believe. The server was upgraded and the script no longer works. The 2nd pass thru the loop gens an Exception.
I am running the script as oracle, the "file" owner.

$ ls -ld /tmp /tmp/ora2ora
drwxrwxrwx 10 bin bin 3072 Feb 22 11:54 /tmp
drwxrwxrwx 2 oracle dba 96 Feb 22 11:39 /tmp/ora2ora

Output from execution:

start utl
Select log From aradmin.TT_ACCUWAN Where Ticket___ = '000000000010440'
TT_ACCUWAN_10440.dat
Len: 6606
Offset: 1
Bytes1: 2000
Offset: 2001
Bytes1: 2000
Write Error
User-Defined Exception

PL/SQL procedure successfully completed.

total 0
-rw-r----- 1 oracle dba 0 Feb 22 11:36 TT_ACCUWAN_10440.dat

Code Snipet:
lFileName := lLobTable || '_' || ltrim(lTicketNb,'0') || '.dat';
dbms_output.put_line(lFileName);
lDataFile := UTL_FILE.FOPEN('/tmp/ora2ora', lFileName, 'W');

lLobLength := dbms_lob.getLength(lLobLocator);
dbms_output.put_line('Len: ' || to_char(lLobLength));

lDataOffset := 1;
WHILE lDataOffset < lLobLength LOOP -- Until no more data
dbms_output.put_line('Offset: ' || to_char(lDataOffset));
dbms_lob.read(lLobLocator, kBufferSize, lDataOffset, lDataBuffer
);
dbms_output.put_line('Bytes1: ' || length(lDataBuffer));
UTL_FILE.PUT_LINE(lDataFile, lDataBuffer);
-- UTL_FILE.PUT_LINE(lDataFile, kNewLine);
lDataOffset := lDataOffset + kBufferSize;
END LOOP;

EXCEPTION
WHEN No_Data_Found THEN
UTL_FILE.PUT_LINE(lDataFile, kNewLine);
UTL_FILE.FFLUSH(lDataFile);
UTL_FILE.FCLOSE(lDataFile);
dbms_output.put_line('Wrote: ' || to_char(lDataOffset - 1));



and Tom said...

It would be to your benefit to use an exception block like this:

exception
when utl_file.invalid_path then
raise_application_error(-20001,
'INVALID_PATH: File location or filename was
invalid.');
when utl_file.invalid_mode then
raise_application_error(-20002,
'INVALID_MODE: The open_mode parameter in FOPEN was
invalid.');
when utl_file.invalid_filehandle then
raise_application_error(-20002,
'INVALID_FILEHANDLE: The file handle was invalid.');
when utl_file.invalid_operation then
raise_application_error(-20003,
'INVALID_OPERATION: The file could not be opened or
operated on as requested.');
when utl_file.read_error then
raise_application_error(-20004,
'READ_ERROR: An operating system error occurred
during the read operation.');
when utl_file.write_error then
raise_application_error(-20005,
'WRITE_ERROR: An operating system error occurred
during the write operation.');
when utl_file.internal_error then
raise_application_error(-20006,
'INTERNAL_ERROR: An unspecified error in PL/SQL.');


but the answer here is easy to see. I do not think the upgrade has anything to do with it -- the data in the blob must be different.

UTL_FILE has a default limit of 1022 bytes per line. Unless your blob has a NEWLINE somewhere in the middle of it -- the write of 2000 bytes blows up because you exceeeded this default limit.

In the earlier release -- you must have been lucky and encountered a newline in every 2000 byte chunk, that broke the line into 2 lines and it works.


All you need to do is:


lDataFile := UTL_FILE.FOPEN('/tmp/ora2ora', lFileName, 'W',
32000 );

and it should be OK. that'll let you create upto a 32k line length without failing.



Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

UTL_FILE max line size problem

David Seibold, July 16, 2001 - 5:18 pm UTC

This article had the solution for our write error.

UTL_FILE exception handler

Jeremy Becker, August 13, 2002 - 2:06 pm UTC

Thanks for the tip. Glad I could find it on the site, as I left my copy of your book at home. Very functional exception handling for UTL file operations and makes me rethink just 'copping out' and using WHEN OTHERS all the time.

OK

Ram, May 11, 2004 - 8:21 am UTC

Dear Tom,
I have a text file with some 10 lines of characters.I would
like to insert a string into the second line.I used the
*fseek* function but I am getting the "file read error".
After opening the file,I used

sql> utl_file.fseek(v_handle,2,0);

Could you please tell me how to insert a string at
appropriate lines with in a file?
Please do reply.

Tom Kyte
May 11, 2004 - 9:57 am UTC

think about how files work.

In order to "insert a second line", you would need to move ALL of the data in the file from the beginning of the new second line.

Say you have a file like this:


How now brown cow
The fox is going to run away
With a spoon
or something like that


If you wanted a "new line two with 15 characters", you would need to rewrite the file as so:

How now brown cow
_______________ <<<=== 15 spaces
The fox is going to run away
With a spoon
or something like that


you need to physically shove the file "down".



So, what you need to do is

a) read the first line from existing file
b) write first line to new file
c) write NEW second line to new file
d) read/write rest of lines from existing file to new file


If fseek worked -- all you would do is end up overwriting what ever was there. files are not rows in a table! the are just big globs of data.







UTL_FILE to print one line more than 1023 char in a file

Kishor, October 23, 2017 - 6:38 am UTC

Hi Tom,
I am trying to print a data into a file which contanins more than 1023+ characters in it. But it's ending with UTL_FILE.WRITE_ERROR.If the data is less than 1020, then the file is generating. Could u please help me in creating the txt file which have single line with more than 1100 chars.

Thanks,
Kishor
Connor McDonald
October 24, 2017 - 2:18 am UTC

That is the default. You can extend this to 32k, eg

l_log_file := UTL_FILE.fopen('mydir','myfile.dat','W',32767);


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here