Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

oracle directory issue

773851Dec 7 2010 — edited Dec 7 2010
I created an oracle directory in the D:\ drive of my server. And I have got a procedure which is writing the value of a blob column into a text file which is to be store into the directory I have created. But I am getting 'invalid directory' path error. is there any privileges that I have to give on the directory that I have created from the OS side. if So what permissions should I provide and how? Also should the directory path be inside the oracle home?
My oracle version is

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
Below is the code for the stored proc I am trying to execute

SQL> exec write_to_file('dir_temp','test.txt');

begin write_to_file('dir_temp','test.txt'); end;

ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 424
ORA-06512: at "PART.WRITE_TO_FILE", line 18
ORA-06512: at line 1

CREATE OR REPLACE PROCEDURE write_to_file(
pcdir IN VARCHAR2,
pcflnm IN VARCHAR2
)
IS

vblob BLOB;
vstart NUMBER := 1;
bytelen NUMBER := 32000;
len NUMBER;
my_vr RAW(32000);
x NUMBER;
l_output utl_file.file_type;

BEGIN

-- define output directory
l_output := utl_file.fopen('pcdir', 'pcflnm','w',32760);
vstart := 1;
-- get length of blob
SELECT dbms_lob.getlength(txt_dat)
INTO len
FROM blob_test
WHERE id = 1;

-- save blob length
x := len;

-- select blob into variable
SELECT txt_dat
INTO vblob
FROM blob_test
WHERE id = 1;

-- if small enough for a single write
IF len < 32760 THEN
utl_file.put_raw(l_output,vblob);
utl_file.fflush(l_output);
ELSE -- write in pieces
vstart := 1;

WHILE vstart < len and bytelen > 0
LOOP
dbms_lob.read(vblob,bytelen,vstart,my_vr);
utl_file.put_raw(l_output,my_vr);
utl_file.fflush(l_output);
-- set the start position for the next cut
vstart := vstart + bytelen;
-- set the end position if less than 32000 bytes
x := x - bytelen;
IF x < 32000 THEN
bytelen := x;
END IF;
end loop;
END IF;
utl_file.fclose(l_output);
end write_to_file;


SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------
SYS UTL_FILE_TMP D:\temp/tmp
SYS VWC D:\Oracle9iserver\vwcode
SYS MYDIR C:\load_SLD\
SYS DIR_TEMP D:\temp
SYS UTL_FILE_DIR D:\temp/dump
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 4 2011
Added on Dec 7 2010
4 comments
352 views