Hi,
I am trying to insert a clob from a xml file which is in my local file system. Below is the piece of pl/sql block.
declare
xmlClobFile BFILE := BFILENAME(BFILE_DIR, 'clob.xml');
tempClob CLOB;
begin
EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY BFILE_DIR AS '||''''||'/home/abc/data/emp/clobs'||''''
--CLOB INSERT
DBMS_LOB.createtemporary(tempClob, TRUE);
DBMS_LOB.open(xmlClobFile, DBMS_LOB.lob_readonly);
DBMS_LOB.loadfromfile(tempClob, xmlClobFile, DBMS_LOB.lobmaxsize);
EXECUTE IMMEDIATE 'insert into emp_data (id, clob_data) values (1000, :1)' using tempClob;
end;
/
Here when I give absolute path (/home/abc/data/emp/clobs) it works. But when I give relative path(like data/emp/clobs) and run this sql from /home/abc, it doesn't work.
[exec] ERROR at line 1:
[exec] ORA-22285: non-existent directory or file for FILEOPEN operation
[exec] ORA-06512: at "SYS.DBMS_LOB", line 937
[exec] ORA-06512: at line 57
How to provide a relative path here, as I want this to be run in any machine and not just mine. Any help is highly appreciated.
Thanks