How to load XML files into Oracle table?

Question

I am new at this so bear with me. Thanks.

I have a directory in my C drive. Like so, C:/Temp. it contains XML files that I would like to load into an Oracle table called xml_tab.

  CREATE TABLE "OMBIL"."XML_TAB" 
  ("ID" NUMBER(10,0), 
   "FILENAME" VARCHAR2(100 BYTE), 
   "XML" "XMLTYPE", 
    CONSTRAINT "XML_TAB_PK" PRIMARY KEY ("ID")
  )

My XML files have the same structure and look like the following

<?xml version="1.0" encoding="us-ascii"?>
<Plant name="Red Sea" schemaVersion="1.2">
   <Unit name="G #01">
     <BOps event_type="U1" cause_code="7110" amp_code="C0" begin_time="2018-06-01T07:08:00">XXXXXXXX</BOps>
    <BOps event_type="GE" cause_code="0001" begin_time="2018-06-01T11:20:33" />
    <BOps event_type="PO" cause_code="7110" amp_code="C0" begin_time="2018-06-20T10:01:00">YTYYYYYYYY</BOps>
    <BOps event_type="GE" cause_code="0001" begin_time="2018-06-20T14:48:46" />
    <Generation begin_time="2018-06-01T00:00:00" end_time="2018-06-30T23:59:59" xxx_produced="50592.90" yyy_consumed="0.00" zzzz_delivered="0.00" />
</Unit>
<Unit name="G #02">
    <Generation begin_time="2018-06-01T00:00:00" end_time="2018-06-30T23:59:59" xxx_produced="0.00" yyy_consumed="0.00" zzzz_delivered="0.00" />
</Unit>
<Unit name="G #03">
    <BOps event_type="GE" cause_code="0001" begin_time="2018-06-27T12:20:00">Something off grid</BOps>
    <BOps event_type="RS" cause_code="0000" begin_time="2018-06-27T12:21:00">open grid</BOps>
    <BOps event_type="GE" cause_code="0001" begin_time="2018-06-27T12:32:00">closed grid</BOps>
    <BOps event_type="RS" cause_code="0000" begin_time="2018-06-27T12:53:58" />
    <Generation begin_time="2018-06-01T00:00:00" end_time="2018-06-30T23:59:59" xxx_produced="25.34" yyy_consumed="0.00" zzzz_delivered="0.00" />
</Unit>
<StationServiceUsage begin_time="2018-06-01T00:00:00" end_time="2018-06-30T23:59:59" xxx="1944.30" />
</Plant>

I would like to write a PL/SQL procedure to look inside my C:\Temp directory and load the xml files into xml_tab oracle table. Do you guys have examples that I can follow to get this done.

I do have the following code, but its not working properly, also I would like the code to loop through all the xml files in the C:\Temp directory, how can I do that???

 create or replace PROCEDURE load_xml (p_dir       IN  VARCHAR2,
                                  p_filename  IN  VARCHAR2) AS
 l_bfile  BFILE := BFILENAME(p_dir, p_filename);
 l_clob   CLOB;

 l_dest_offset   INTEGER := 1;
 l_src_offset    INTEGER := 1;
 l_bfile_csid    NUMBER  := 0;
 l_lang_context  INTEGER := 0;
 l_warning       INTEGER := 0;
 BEGIN
  DBMS_LOB.createtemporary (l_clob, TRUE);

 DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
 -- loadfromfile deprecated.
 -- DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));
 DBMS_LOB.loadclobfromfile (
   dest_lob      => l_clob,
   src_bfile     => l_bfile,
   amount        => DBMS_LOB.lobmaxsize,
   dest_offset   => l_dest_offset,
   src_offset    => l_src_offset,
   bfile_csid    => l_bfile_csid ,
   lang_context  => l_lang_context,
   warning       => l_warning);
   DBMS_LOB.fileclose(l_bfile);

  INSERT INTO xml_tab (
   id,
   filename,
    xml
  )
 VALUES (
    xml_tab_seq.NEXTVAL,
    p_filename,
    XMLTYPE.createXML(l_clob)
 );
 COMMIT;

 DBMS_LOB.freetemporary (l_clob);
 END load_xml;

Getting the following error:

 BEGIN load_xml(p_dir => 'XML_DIR', p_filename => 'test.xml'); END;
Error report -
ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory
ORA-06512: at "SYS.DBMS_LOB", line 805
ORA-06512: at "OMBIL.LOAD_XML", line 14
ORA-06512: at line 1
22288. 00000 -  "file or LOB operation %s failed\n%s"
*Cause:    The operation attempted on the file or LOB failed.
 *Action:   See the next error message in the error stack for more detailed
       information.  Also, verify that the file or LOB exists and that
       the necessary privileges are set for the specified operation. If
       the error still persists, report the error to the DBA.

I did grant all on XML_DIR to public.

Thank you.

Answer 1

Seems that your file is not in the provided directory:

declare
   l_bfile  BFILE := BFILENAME('ORACLE_UTL_DIR', 'test.lst');
begin
   DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
end; 

PL/SQL procedure successfully completed.

But with a not existing file:

declare
   l_bfile  BFILE := BFILENAME('ORACLE_UTL_DIR', 'test.lstxxx');
begin
   DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
end; 

Error report -
ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory
ORA-06512: at "SYS.DBMS_LOB", line 805
ORA-06512: at line 4
22288. 00000 -  "file or LOB operation %s failed\n%s"
*Cause:     The operation attempted on the file or LOB failed.
*Action:    See the next error message in the error stack for more detailed
              information.  Also, verify that the file or LOB exists and that
              the necessary privileges are set for the specified operation. If
              the error still persists, report the error to the DBA.

To debug you can use: dbms_lob.fileexists(l_bfile) before opening the file.