UTL_FILE.FOPEN() how to declare and assign the directory path?

Question

I have this code to extract Images from an Oracle database using toad but i have challenges when its comes to declare the directory where to save the images once extracted. I want these images to be saved in C:\Images\ a folder in a local machine. Is this possible or how should i specify the directory?.

when i execute the code am getting Invalid file operation

DECLARE
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
-- Get length of blob
SELECT DBMS_LOB.getlength (PHOTO), ename || '_1.jpg'
INTO t_TotalSize, t_file_name FROM DEMO WHERE ENAME ='moon';
t_remain := t_TotalSize;
-- The directory TEMPDIR should exist before executing
t_output := UTL_FILE.fopen ('C:\Images\', t_file_name, 'wb', 32760);
-- Get BLOB
SELECT PHOTO INTO t_blob FROM DEMO WHERE ENAME ='moon';
-- Retrieving BLOB
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;

Possible duplicate of [UTL\_FILE.FOPEN() procedure not accepting path for directory?](http://stackoverflow.com/questions/2751113/utl-file-fopen-procedure-not-accepting-path-for-directory) — Nitish, Mar 10 '17 at 06:03

score 0 · Accepted Answer · answered Mar 10 '17 at 09:20

0

you should use a DIRECTORY object,

create or replace directory my_dir as 'c:\Images\';

declare
v_dir varchar2(10) := 'MY_DIR';
.....
begin
....
t_output := utl_file.fopen(v_dir, t_file_name, 'wb', 32760);
....

answered Mar 10 '17 at 09:20

M Zippka

93
13

This only work if the script run in the same machine that Oracle Instance run. – hackvan Mar 10 '17 at 18:02
Thanks a lot, I have added the code to my initial one but now am getting this error `09:31:28 Error: ORA-00911: invalid character` what could be the issue? – Muzurl Mar 13 '17 at 06:34

score 0 · Answer 2 · answered Mar 10 '17 at 18:01

First to know, when you execute any PL/SQL script that handle files this always execute into the scope of the server that run the oracle instance.

In this case the path "C:\Image\" need to exists on the server that Oracle run.

One strategy that you can use is execute the script into the folder of the server and then obtain the folder data via Copy Command on the terminal/console.

UTL_FILE.FOPEN() how to declare and assign the directory path?

2 Answers2