Inserting an XML into an Oracle table gives file not found error

Question

I am trying to insert an XML file into an Oracle table.

I have movie.xml table:

name           type
TITLE          VARCHAR2(100)
ARTIST         VARCHAR2(100)
COUNTRY        VARCHAR2(15)
COMPANY        VARCHAR2(10)
PRICE          NUMBER(10,2)
YEAR           NUMBER(4)

procedure

   create or replace procedure moviesxml as
    movies_doc xmltype := xmltype( bfilename('isop','movie.xml'),nls_charset_id('UTF-8') );

BEGIN

   insert into moviexml(title,artist,country,company,price,year) 
   select title,artist,country,company,price,year from  xmltable(
 '/catalog/cd'
  passing movies_doc
  columns  title   varchar2(100) path  'title',
      artist    varchar(100)        path 'artist',
      country varchar2(15) path 'country',
      company varchar2(10) path 'company',
      price   number(10,2) path 'price',
      year number path 'year'
);
end moviesxml;

error is ORA-22288: file or LOB operation FILEOPEN failed The system cannot find the file specified. ORA-06512: at "SYS.DBMS_LOB", line 523 ORA-06512: at "SYS.XMLTYPE", line 287.

But, I have this file file in the isop directory.

ViKu ViKu · Accepted Answer · 2016-02-24T06:35:35

First you need to create a directory object for isop, so that it should locate the file path in the server.

CREATE DIRECTORY isop AS '/isop';

movies_doc xmltype := xmltype( bfilename('ISOP','movie.xml'),nls_charset_id('UTF-8') );

and isop contain the xml file, as in your case.

If you have absolute path then,

CREATE DIRECTORY isop AS 'C:\isop';

movies_doc xmltype := xmltype( bfilename('ISOP','movie.xml'),nls_charset_id('UTF-8') );

Inserting an XML into an Oracle table gives file not found error

1 Answers