How to set relative path of local filesystem in pl / sql block
I am trying to insert a clob from an xml file that is on my local filesystem. Below is a snippet of the 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 the absolute path (/ home / abc / data / emp / clobs) it works. But when I give a 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 do I provide a relative path here, since I want this to run on any computer, not just mine. Any help is appreciated.
thank
source to share
Relative path if something comes from the directory that Oracle "start" command started, eg / home / oracle. One way to test this and make sure that relative paths will work (not used themselves) is to create a directory pointing to ".", And run a test to create the file, and then search for that file. The directory where you find your search will be your starting path. However, I think this is unsafe as Oracle can be started from any folder (potentially) depending on whether it was auto-started or whatever DBA was on hand to start it.
source to share