Hi,
I am trying to load the XML document into XML Table. Though there exists the Directory loaded with xml file, It's giving an error while inserting into the table.
My Code is as follows,
CREATE TABLE XmlData
(
KEYVALUE varchar2(10) primary key,
XMLCOLUMN xmltype
);
CREATE TABLE XMLTABLE OF XMLType;
-- Loaded the File purchaseorder.xml into OS directory "FXLMBAH"
/*
<PurchaseOrder
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://www.oracle.com/xdb/po.xsd">
<Reference>ADAMS-20011127121040988PST</Reference>
<Actions>
<Action>
<User>SCOTT</User>
<Date>2002-03-31</Date>
</Action>
</Actions>
<Reject/>
<Requestor>Julie P. Adams</Requestor>
<User>ADAMS</User>
<CostCenter>R20</CostCenter>
<ShippingInstructions>
<name>Julie P. Adams</name>
<address>Redwood Shores, CA 94065</address>
<telephone>650 506 7300</telephone>
</ShippingInstructions>
<SpecialInstructions>Ground</SpecialInstructions>
<LineItems>
<LineItem ItemNumber="1">
<Description>The Ruling Class</Description>
<Part Id="715515012423" UnitPrice="39.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="2">
<Description>Diabolique</Description>
<Part Id="037429135020" UnitPrice="29.95" Quantity="3"/>
</LineItem>
<LineItem ItemNumber="3">
<Description>8 1/2</Description>
<Part Id="037429135624" UnitPrice="39.95" Quantity="4"/>
</LineItem>
</LineItems>
</PurchaseOrder>
*/
CREATE OR REPLACE FUNCTION fn_Ins_CLOBDOCUMENT(FILENAME IN VARCHAR2,
CHARSET IN VARCHAR2 DEFAULT NULL)
RETURN CLOB DETERMINISTIC IS
FILE BFILE := BFILENAME('/usr1/spool/FXLMBAH', FILENAME);
CHARCONTENT CLOB := ' ';
TARGETFILE BFILE;
LANG_CTX NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
CHARSET_ID NUMBER := 0;
SRC_OFFSET NUMBER := 1;
DST_OFFSET NUMBER := 1;
WARNING NUMBER;
BEGIN
IF CHARSET IS NOT NULL THEN
CHARSET_ID := NLS_CHARSET_ID(CHARSET);
END IF;
TARGETFILE := FILE;
DBMS_LOB.FILEOPEN(TARGETFILE, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADCLOBFROMFILE(CHARCONTENT,
TARGETFILE,
DBMS_LOB.GETLENGTH(TARGETFILE),
SRC_OFFSET,
DST_OFFSET,
CHARSET_ID,
LANG_CTX,
WARNING);
DBMS_LOB.FILECLOSE(TARGETFILE);
RETURN CHARCONTENT;
END;
INSERT INTO XMLTABLE
VALUES(XMLTYPE(fn_Ins_CLOBDOCUMENT('purchaseorder.xml', '/usr1/spool/FXLMBAH')));
INSERT INTO XMLTABLE
VALUES(XMLTYPE(fn_Ins_CLOBDOCUMENT('purchaseorder.xml', '/usr1/spool/FXLMBAH')))
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at "LCC.FN_INS_CLOBDOCUMENT", line 18
ORA-06512: at line 1
SELECT * FROM All_Directories
Owner Directory_Name Directory_Path
SYS FXLMBAH usr1/spool
What could be the readon causing the error.
Raja K