Skip to Main Content

SQL & PL/SQL

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!

Error while loading XML file !!

598296Feb 18 2009 — edited Feb 23 2009
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 23 2009
Added on Feb 18 2009
20 comments
1,809 views