Hello Guys,
i am running PL/SQL procedure to extract a blob file and write it on the file system (Solaris 10) it gives "ORA-29283: invalid file operation at "sys.UTL_File" error .
first i created a directory to save blob files in it
#mkdir /oracle/files/
sql>create or replace directory file_dir as '/oracle/files';
sql> grant read,write on directory file_dir to dbuser; --> using sys user
the PL/SQL code :
CREATE OR REPLACE PROCEDURE export_file (num in varchar2)
IS
v_lob_loc BLOB;
v_buffer RAW(32767);
v_buffer_size BINARY_INTEGER;
v_amount BINARY_INTEGER;
v_offset NUMBER(38) := 1;
v_chunksize INTEGER;
v_out_file UTL_FILE.FILE_TYPE;
BEGIN
-- +-------------------------------------------------------------+
-- | SELECT THE LOB LOCATOR |
-- +-------------------------------------------------------------+
SELECT DER_ENCODING
INTO v_lob_loc
FROM files
WHERE file_num = num and client = 'testclient';
-- +-------------------------------------------------------------+
-- | FIND OUT THE CHUNKSIZE FOR THIS LOB COLUMN |
-- +-------------------------------------------------------------+
v_chunksize := DBMS_LOB.GETCHUNKSIZE(v_lob_loc);
IF (v_chunksize < 32767) THEN
v_buffer_size := v_chunksize;
ELSE
v_buffer_size := 32767;
END IF;
v_amount := v_buffer_size;
-- +-------------------------------------------------------------+
-- | OPENING THE LOB IS OPTIONAL |
-- +-------------------------------------------------------------+
DBMS_LOB.OPEN(v_lob_loc, DBMS_LOB.LOB_READONLY);
v_out_file := UTL_FILE.FOPEN(
location => 'file_DIR',
filename => num || '.txt',
open_mode => 'wb',
max_linesize => 32767);
WHILE v_amount >= v_buffer_size
LOOP
DBMS_LOB.READ(
lob_loc => v_lob_loc,
amount => v_amount,
offset => v_offset,
buffer => v_buffer);
v_offset := v_offset + v_amount;
UTL_FILE.PUT_RAW (
file => v_out_file,
buffer => v_buffer,
autoflush => true);
UTL_FILE.FFLUSH(file => v_out_file);
END LOOP;
UTL_FILE.FFLUSH(file => v_out_file);
UTL_FILE.FCLOSE(v_out_file);
-- +-------------------------------------------------------------+
-- | CLOSING THE LOB IS MANDATORY IF YOU HAVE OPENED IT |
-- +-------------------------------------------------------------+
DBMS_LOB.CLOSE(v_lob_loc);
END;
=================================================================================
Notes:
i did the following :
-directory permissions is 777
the code was working successfully until i deleted the directory and recreated it again.