UTL_FILE invalid file operation

Muzurl Francen
Muzurl Francen used Ask the Experts™
on
I am having this code to extract images from oracle Db to a folder but i am getting the following error
01:49:53 Error: ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 17

--create or replace directory my_dir as '/export/home/haruser/Desktop/Images';
DECLARE
v_dir varchar2(10):='MY_DIR';
t_blob BLOB;
t_len NUMBER;
t_file_name VARCHAR2(100);
t_output UTL_FILE.file_type;
t_TotalSize number;
t_position number := 1;
t_chucklen NUMBER := 4096;
t_chuck raw(4096);
t_remain number;
BEGIN
SELECT DBMS_LOB.getlength (IMAGE), brid || '_1.jpg'
INTO t_TotalSize, t_file_name FROM SA.T_CLIENTIDENTIFICATIONS WHERE BRID ='0000024' AND IMAGEID='4';
t_remain := t_TotalSize;
t_output := UTL_FILE.fopen (v_dir, t_file_name, 'wb', 32760);
SELECT IMAGE INTO t_blob FROM SA.T_CLIENTIDENTIFICATIONS WHERE BRID ='0000024' AND IMAGEID='4';
WHILE t_position < t_TotalSize
LOOP
DBMS_LOB.READ (t_blob, t_chucklen, t_position, t_chuck);
UTL_FILE.put_raw (t_output, t_chuck);
UTL_FILE.fflush (t_output);
t_position := t_position + t_chucklen;
t_remain := t_remain - t_chucklen;
IF t_remain < 4096
THEN
t_chucklen := t_remain;
END IF;
END LOOP;
END;

Anyone with solution where i could be doing wrong?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
My guess is the file already exists on the disk and Oracle doesn't have permissions on it.

Remove the file and try again.

Author

Commented:
Thanks for feedback, However i have removed the file and added but still the same error
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Does the oracle user have write permission in the folder?

The file IO is done as the oracle user since it is performed by the database itself and not the user executing the PL/SQL.

If the create directory path is the same, as the oracle user can you do:
touch /export/home/haruser/Desktop/Images/mytest.txt

If not, that is the problem.

Author

Commented:
I just assigned permission 777 to the folder and it worked.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>> just assigned permission 777 to the folder

That probably isn't a good solution since that also enables EVERYONE full access to the folder.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial