Let me take it back. UTL_FILE still is a package to read and write operating system
text files. So main restriction is still there: file is a stream file with NEW_LINE serving as record (line) separator. Max record size is whatever specified in FOPEN as parameter max_linesize and can not exceed 32767 (including new line). Below is an example:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SQL> DECLARE
2 f UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN('C:\TEMP', 'FileName.jpg', 'w', 32767 );
3 buffer raw( 10000 ) := LPAD('A', 19999 ,'B') || 'A';
4 BEGIN
5 UTL_FILE.PUT_RAW(f, buffer, TRUE);
6 UTL_FILE.PUT_RAW(f, buffer, TRUE);
7 UTL_FILE.PUT_RAW(f, buffer, TRUE);
8 UTL_FILE.FCLOSE(f);
9 END;
10 /
PL/SQL procedure successfully completed.
When total size does not exceed 32K, it works fine, but as soon as it exceeds 32K:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
SQL> DECLARE
2 f UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN('C:\TEMP', 'FileName.jpg', 'w', 32767 );
3 buffer raw( 10000 ) := LPAD('A', 19999 ,'B') || 'A';
4 BEGIN
5 UTL_FILE.PUT_RAW(f, buffer, TRUE);
6 UTL_FILE.PUT_RAW(f, buffer, TRUE);
7 UTL_FILE.PUT_RAW(f, buffer, TRUE);
8 UTL_FILE.PUT_RAW(f, buffer, TRUE);
9 UTL_FILE.FCLOSE(f);
10 END;
11 /
DECLARE
*
ERROR at line 1 :
ORA- 29285 : file write error
ORA- 06512 : at "SYS.UTL_FILE" , line 18
ORA- 06512 : at "SYS.UTL_FILE" , line 1007
ORA- 06512 : at line 8
It errors out. And when we add NEW_LINE after third PUT_RAW, we are OK again (however it is not what you are looking for since you are, as far as I see, creating a JPG which is a binary file and NEW_LINE will mess it up):
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SQL> DECLARE
2 f UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN('C:\TEMP', 'FileName.jpg', 'w', 32767 );
3 buffer raw( 10000 ) := LPAD('A', 19999 ,'B') || 'A';
4 BEGIN
5 UTL_FILE.PUT_RAW(f, buffer, TRUE);
6 UTL_FILE.PUT_RAW(f, buffer, TRUE);
7 UTL_FILE.PUT_RAW(f, buffer, TRUE);
8 UTL_FILE.NEW_LINE(f);
9 UTL_FILE.PUT_RAW(f, buffer, TRUE);
10 UTL_FILE.FCLOSE(f);
11 END;
12 /
PL/SQL procedure successfully completed.
As you can see, the best you can do is to create a JPG no longer than 32K. Otherwise you could use BFILEs.
SY
