We help IT Professionals succeed at work.

UTL_FILE invalid file operation

Muzurl Francen
on
211 Views
Last Modified: 2017-03-14
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

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

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

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.