“file or LOB operation FILEOPEN failed” when loading an XML file into a table

Question

I am using 11gr2 and trying to load an XML file into a table.

Here is the code:

connected as sys:

GRANT ALL ON DIRECTORY XMLDIR TO user_1;

switch to user_1:

CREATE TABLE mytable1 (key_column VARCHAR2(10) PRIMARY KEY, 
                       xml_column XMLType);

CREATE DIRECTORY xmldir AS 'F:\OracleTestFiles\XMLs\';

the XML file:

<PurchaseOrder 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:noNamespaceSchemaLocation=
    "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd">
  <Reference>SBELL-2002100912333601PDT</Reference>
  <Actions>
    <Action>
      <User>SVOLLMAN</User>
    </Action>
  </Actions>
  <Reject/>
  <Requestor>Sarah J. Bell</Requestor>
  <User>SBELL</User>
  <CostCenter>S30</CostCenter>
  <ShippingInstructions>
    <name>Sarah J. Bell</name>
    <address>400 Oracle Parkway
      Redwood Shores
      CA
      94065
      USA</address>
    <telephone>650 506 7400</telephone>
  </ShippingInstructions>
  <SpecialInstructions>Air Mail</SpecialInstructions>
  <LineItems>
    <LineItem ItemNumber="1">
      <Description>A Night to Remember</Description>
      <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
    </LineItem>
    <LineItem ItemNumber="2">
      <Description>The Unbearable Lightness Of Being</Description>
      <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
    </LineItem>
    <LineItem ItemNumber="3">
      <Description>Sisters</Description>
      <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
    </LineItem>
  </LineItems>
</PurchaseOrder>

the insert statement:

INSERT INTO mytable1 VALUES ('1a', XMLType(bfilename('XMLDIR', 'PurchaseOrder.xml'), nls_charset_id('AL32UTF8')));

When I am executing the INSERT statement, I receive the following error:

Error report - ORA-22288: file or LOB operation FILEOPEN failed The system cannot find the path specified. ORA-06512: at "SYS.XMLTYPE", line 296 ORA-06512: at line 1

Can someone explain what is wrong, please?

Should I grant other privileges in the OS (Windows 7)?

I suppose you've double checked that the file F:\OracleTestFiles\XMLs\PurchaseOrder.xml does definitely exist? — Ben
yes, I already checked the existance of the XML file into the location. — mikcutu
Did you first GRANT ALL ON DIRECTORY and then CREATE DIRECTORY? In this case is should not work any you may did a typo. — Wernfried Domscheit
@WernfriedDomscheit: No. I initially created the directory and after that I granted the priviledgies. — mikcutu

mikcutu mikcutu · Accepted Answer · 2017-12-27T11:33:49

I found the problem. The phisically location of the directory from the hard drive was on a mapped driver (F:).

I changed it on C: and now it is working like a charm.

“file or LOB operation FILEOPEN failed” when loading an XML file into a table

1 Answers