SQL & PL/SQL

Hello all,

I have a procedure which reads an xml file and inserts and updates tables accordingly. It works fine with most files, but with this one, it gives me an ORA-22275 error. I have tried many things, and I don't know why. Here is the file, simplified:

<EQUIPMENT>

<EQUIPITEM>

<SPID>5612001</SPID>

<REGDATE>2016-11-22</REGDATE>

<VESSELREGID>5612</VESSELREGID>

<VESSELNAME>FAAS Seagull</VESSELNAME>

<EQUIPREGID>001</EQUIPREGID>

<EQUIPNAME>PORT MAIN ENGINE</EQUIPNAME>

<LUBID>10409</LUBID>

<LUBNAME>CAT DEO (15W40)</LUBNAME>

<SLATENAME>Diesel Engine</SLATENAME>

<SYSCAPACITY>0</SYSCAPACITY>

<ALARMLEVELS>

<LABTEST>

<TESTID>8401</TESTID>

<TEST>ALUMINUM</TEST>

<UNITS>PPM</UNITS>

<MSCH>5</MSCH>

<MSCHH>10</MSCHH>

</LABTEST>

</ALARMLEVELS>

</EQUIPITEM>

</EQUIPMENT>

Here's PART of the procedure:

PROCEDURE form_load_xml(p_filename IN  VARCHAR2) IS

  l_bfile        BFILE := BFILENAME('XML_DIR', p_filename);

  l_clob         CLOB;

  l_parser       dbms_xmlparser.Parser;

  l_doc          dbms_xmldom.DOMDocument;

  l_nl           dbms_xmldom.DOMNodeList;

  l_n2           dbms_xmldom.DOMNodeList;

  l_n3           dbms_xmldom.DOMNodeList;

  l_n4           dbms_xmldom.DOMNodeList;

  l_n            dbms_xmldom.DOMNode;

  w_nodename     VARCHAR2(100);

  ................

  .................

  ................

BEGIN

  rec_total := 0; rec_processed := 0;

  DBMS_LOB.createtemporary (l_clob, TRUE);

  DBMS_LOB.fileopen(l_bfile, 0);

  DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));

  DBMS_LOB.fileclose(l_bfile);

  -- Create a parser.

  l_parser := dbms_xmlparser.newParser;

-- Parse the document and create a new DOM document.

  dbms_xmlparser.parseClob(l_parser, l_clob);

  l_doc := dbms_xmlparser.getDocument(l_parser);

-- Free resources associated with the CLOB and Parser now they are no longer needed.

  DBMS_LOB.freetemporary (l_clob);

  dbms_xmlparser.freeParser(l_parser);

-- Get a list of all the EMP nodes in the document using the XPATH syntax.

  l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/EQUIPMENT/EQUIPITEM');

-- Loop through the list and create a new record in the table

  FOR cur_xml IN 0 .. dbms_xmldom.getLength(l_nl) - 1

  LOOP

       w_equipname := '';

       l_n := dbms_xmldom.item(l_nl, cur_xml);

     ....................

     ....................

     ....................

  END LOOP;

-- Free any resources associated with the document now it is no longer needed.

  dbms_xmldom.freeDocument(l_doc);

  :NAVY_TEMP.f_comment := :NAVY_TEMP.f_comment || to_char(f_custno)||'-'||to_char(f_plantno)||

                          ', records total - '||to_char(rec_total)||', processed - ' || to_char(rec_processed);

  FORMS_DDL('COMMIT');

  COMMIT;

:REC_OF := 'Record ' ||:System.Cursor_Record || ' of ' || :total_r;

  SYNCHRONIZE;

  EXCEPTION

    WHEN OTHERS THEN

      dbms_lob.freetemporary(l_clob);

      dbms_xmlparser.freeParser(l_parser);

      dbms_xmldom.freeDocument(l_doc); 

END;

The name of this file is: ER_FAAS_Seagull(Ver15)_20161122.xml

I know it is hard to understand this, and it is hard to test. But maybe from your experience you might have at least some ideas as to what could be wrong?

Error: ORA-22275 - Invalid LOB locator specified

Thank you.