dbms_lob.createtemporary(l_clob, cache=>FALSE); 
dbms_lob.loadFromFile(dest_lob => l_clob, 
src_lob => l_bfile, 
amount => dbms_lob.getLength(l_bfile)); 
l_parser := dbms_xmlparser.newParser; 
dbms_xmlparser.parseClob(l_parser, l_clob); 

dbms_lob.createtemporary(l_clob, cache=>FALSE); 

select xml_cfile,xml_bfile 
into l_clob ,l_bfile 
from xml_load_in 

l_parser := dbms_xmlparser.newParser; 
dbms_xmlparser.parseClob(l_parser, l_clob); --failed parse 

declare
l_bfile BFILE;
l_clob CLOB;
l_parser dbms_xmlparser.Parser;
l_doc dbms_xmldom.DOMDocument;
begin
dbms_lob.createtemporary(l_clob, cache=>FALSE);
select xml_cfile,xml_bfile
into l_clob ,l_bfile
from xml_load_in 
l_parser := dbms_xmlparser.newParser;
dbms_xmlparser.parseClob(l_parser, l_clob);
l_doc := dbms_xmlparser.getDocument(l_parser);
dbms_lob.freetemporary(l_clob);
dbms_xmlparser.freeParser(l_parser);
exception
dbms_lob.freetemporary(l_clob);
dbms_xmlparser.freeParser(l_parser);
dbms_xmldom.freeDocument(l_doc);
End;

SQL> declare
  2  l_bfile BFILE;
  3  l_clob CLOB;
  4  l_parser dbms_xmlparser.Parser;
  5  l_doc dbms_xmldom.DOMDocument;
  6  begin
  7  dbms_lob.createtemporary(l_clob, cache=>FALSE);
  8  select xml_cfile,xml_bfile
  9  into l_clob ,l_bfile
 10  from xml_load_in 
 11  l_parser := dbms_xmlparser.newParser;
 12  dbms_xmlparser.parseClob(l_parser, l_clob);
 13  l_doc := dbms_xmlparser.getDocument(l_parser);
 14  dbms_lob.freetemporary(l_clob);
 15  dbms_xmlparser.freeParser(l_parser);
 16  exception
 17  dbms_lob.freetemporary(l_clob);
 18  dbms_xmlparser.freeParser(l_parser);
 19  dbms_xmldom.freeDocument(l_doc);
 20  End;
 21  /
l_parser := dbms_xmlparser.newParser;
         *
ERROR at line 11:
ORA-06550: line 11, column 10:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 8, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 17, column 1:
PLS-00103: Encountered the symbol "DBMS_LOB" when expecting one of the following:
pragma when
ORA-06550: line 17, column 31:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
. ( , * % & - + / at mod remainder rem <an identifier>
<a double-quoted delimited-identifier> <an exponent (**)> as
from into || multiset bulk
ORA-06550: line 20, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
end not pragma final instantiable order overriding static
member constructor map

SQL> drop table xml_load_in;

Table dropped.

SQL>
SQL> CREATE TABLE XML_LOAD_IN
  2  (XML_CFILE       CLOB
  3  );

Table created.

SQL>
SQL>
SQL> INSERT INTO XML_LOAD_IN (  XML_CFILE)
  2  VALUES (
  3  '<CCR>
  4     <BundleCount>1</BundleCount>
  5     <ItemWithinCashLetterCount>2</ItemWithinCashLetterCount>
  6     <CashLetterTotalAmount>1500</CashLetterTotalAmount>
  7     <ImagesWithinCashLetterCount>2</ImagesWithinCashLetterCount>
  8     <ECEInstitutionName>Bank Asia</ECEInstitutionName>
  9     <SettlementDate>20090714</SettlementDate>
 10  </CCR>
 11  ');

1 row created.

SQL>
SQL> drop table ccr_in;

Table dropped.

SQL>
SQL> CREATE TABLE CCR_IN
  2  (
  3    "BundleCount"                  NUMBER(6)      NOT NULL,
  4    "ItemWithinCashLetterCount"    NUMBER(8)      NOT NULL,
  5    "CashLetterTotalAmount"        NUMBER(14)     NOT NULL,
  6    "ImagesWithinCashLetterCount"  NUMBER(9),
  7    "ECEInstitutionName"           VARCHAR2(18 BYTE),
  8    "SettlementDate"               VARCHAR2(8 BYTE)
  9  )
 10  /

Table created.

SQL>


SQL> CREATE OR REPLACE procedure Dpr_Insert_From_Xml_CCR
  2  is
  3     l_bfile     BFILE;
  4     l_clob      CLOB;
  5     l_parser    dbms_xmlparser.Parser;
  6     l_doc       dbms_xmldom.DOMDocument;
  7     l_nl        dbms_xmldom.DOMNodeList;
  8     l_n         dbms_xmldom.DOMNode;
  9  
 10     TYPE tab_type IS TABLE OF CCR_IN%ROWTYPE;
 11     t_tab  tab_type := tab_type();
 12  
 13  BEGIN
 14  
 15  
 16     dbms_lob.createtemporary(l_clob, cache=>FALSE);
 17  
 18      Begin
 19          select xml_cfile
 20            into l_clob
 21            from xml_load_in;
 22      Exception
 23          When no_data_found then
 24            raise_application_error(-2001,'Inward XML File Not Found.');
 25          When others then null;
 26      End;
 27  
 28     l_parser := dbms_xmlparser.newParser;
 29     dbms_xmlparser.parseClob(l_parser, l_clob);
 30     l_doc := dbms_xmlparser.getDocument(l_parser);
 31     dbms_lob.freetemporary(l_clob);
 32     dbms_xmlparser.freeParser(l_parser);
 33     l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/CCR');
 34  
 35  --FOR ECR
 36     FOR CUR_CCR IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP
 37       l_n := dbms_xmldom.item(l_nl, CUR_CCR);
 38       t_tab.extend;
 39       -- Use XPATH syntax to assign values to he elements of the collection.
 40       dbms_xslprocessor.valueOf(l_n,'BundleCount/text()'                 ,t_tab(t_tab.last)."BundleCount"                 );
 41       dbms_xslprocessor.valueOf(l_n,'ItemWithinCashLetterCount/text()'   ,t_tab(t_tab.last)."ItemWithinCashLetterCount"   );
 42       dbms_xslprocessor.valueOf(l_n,'CashLetterTotalAmount/text()'       ,t_tab(t_tab.last)."CashLetterTotalAmount"       );
 43       dbms_xslprocessor.valueOf(l_n,'ImagesWithinCashLetterCount/text()' ,t_tab(t_tab.last)."ImagesWithinCashLetterCount" );
 44       dbms_xslprocessor.valueOf(l_n,'ECEInstitutionName/text()'          ,t_tab(t_tab.last)."ECEInstitutionName"          );
 45       dbms_xslprocessor.valueOf(l_n,'SettlementDate/text()'              ,t_tab(t_tab.last)."SettlementDate"              );
 46     END LOOP;
 47  
 48  
 49     FOR CUR_CCR IN t_tab.first .. t_tab.last LOOP
 50  
 51       INSERT INTO CCR_IN
 52       ("BundleCount"                 ,
 53        "ItemWithinCashLetterCount"   ,
 54        "CashLetterTotalAmount"       ,
 55        "ImagesWithinCashLetterCount" ,
 56        "ECEInstitutionName"          ,
 57        "SettlementDate"
 58        )
 59       VALUES
 60       (t_tab(CUR_CCR)."BundleCount"                 ,
 61        t_tab(CUR_CCR)."ItemWithinCashLetterCount"   ,
 62        t_tab(CUR_CCR)."CashLetterTotalAmount"       ,
 63        t_tab(CUR_CCR)."ImagesWithinCashLetterCount" ,
 64        t_tab(CUR_CCR)."ECEInstitutionName"          ,
 65        t_tab(CUR_CCR)."SettlementDate"
 66        );
 67  
 68     END LOOP;
 69  
 70     COMMIT;
 71  
 72      dbms_xmldom.freeDocument(l_doc);
 73  
 74   EXCEPTION
 75     WHEN OTHERS THEN
 76       dbms_lob.freetemporary(l_clob);
 77       dbms_xmlparser.freeParser(l_parser);
 78       dbms_xmldom.freeDocument(l_doc);
 79   END;
 80  /

Procedure created.

SQL> exec Dpr_Insert_From_Xml_CCR;
BEGIN Dpr_Insert_From_Xml_CCR; END;

*
ERROR at line 1:
ORA-22275: invalid LOB locator specified 
ORA-06512: at "SYS.DBMS_LOB", line 533 
ORA-06512: at "TTT.DPR_INSERT_FROM_XML_CCR", line 76 
ORA-22275: invalid LOB locator specified 
ORA-06512: at line 1 


SQL> exit

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE procedure Dpr_Insert_From_Xml_CCR
  2   is
  3      l_bfile     BFILE;
  4      l_clob      CLOB;
  5      l_parser    dbms_xmlparser.Parser;
  6      l_doc       dbms_xmldom.DOMDocument;
  7      l_nl        dbms_xmldom.DOMNodeList;
  8      l_n         dbms_xmldom.DOMNode;
  9      TYPE tab_type IS TABLE OF CCR_IN%ROWTYPE;
 10      t_tab  tab_type := tab_type();
 11   BEGIN
 12      dbms_lob.createtemporary(l_clob, cache=>FALSE);
 13       Begin
 14           select xml_cfile
 15             into l_clob
 16             from xml_load_in;
 17       Exception
 18           When no_data_found then
 19             raise_application_error(-2001,'Inward XML File Not Found.');
 20           When others then null;
 21       End;
 22      l_parser := dbms_xmlparser.newParser;
 23      dbms_xmlparser.parseClob(l_parser, l_clob);
 24      l_doc := dbms_xmlparser.getDocument(l_parser);
 25      dbms_lob.freetemporary(l_clob);
 26      dbms_xmlparser.freeParser(l_parser);
 27      l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/CCR');
 28   --FOR ECR
 29      FOR CUR_CCR IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP
 30        l_n := dbms_xmldom.item(l_nl, CUR_CCR);
 31        t_tab.extend;
 32        -- Use XPATH syntax to assign values to he elements of the collection.
 33        dbms_xslprocessor.valueOf(l_n,'BundleCount/text()'                 ,t_tab(t_tab.last)."BundleCount"                 );
 34        dbms_xslprocessor.valueOf(l_n,'ItemWithinCashLetterCount/text()'   ,t_tab(t_tab.last)."ItemWithinCashLetterCount"   );
 35        dbms_xslprocessor.valueOf(l_n,'CashLetterTotalAmount/text()'       ,t_tab(t_tab.last)."CashLetterTotalAmount"       );
 36        dbms_xslprocessor.valueOf(l_n,'ImagesWithinCashLetterCount/text()' ,t_tab(t_tab.last)."ImagesWithinCashLetterCount" );
 37        dbms_xslprocessor.valueOf(l_n,'ECEInstitutionName/text()'          ,t_tab(t_tab.last)."ECEInstitutionName"          );
 38        dbms_xslprocessor.valueOf(l_n,'SettlementDate/text()'              ,t_tab(t_tab.last)."SettlementDate"              );
 39      END LOOP;
 40      FOR CUR_CCR IN t_tab.first .. t_tab.last LOOP
 41        INSERT INTO CCR_IN
 42        ("BundleCount"                 ,
 43         "ItemWithinCashLetterCount"   ,
 44         "CashLetterTotalAmount"       ,
 45         "ImagesWithinCashLetterCount" ,
 46         "ECEInstitutionName"          ,
 47         "SettlementDate"
 48         )
 49        VALUES
 50        (t_tab(CUR_CCR)."BundleCount"                 ,
 51         t_tab(CUR_CCR)."ItemWithinCashLetterCount"   ,
 52         t_tab(CUR_CCR)."CashLetterTotalAmount"       ,
 53         t_tab(CUR_CCR)."ImagesWithinCashLetterCount" ,
 54         t_tab(CUR_CCR)."ECEInstitutionName"          ,
 55         t_tab(CUR_CCR)."SettlementDate"
 56         );
 57      END LOOP;
 58      COMMIT;
 59       dbms_xmldom.freeDocument(l_doc);
 60  ---  EXCEPTION
 61    ----  WHEN OTHERS THEN
 62     --   dbms_lob.freetemporary(l_clob);
 63      --  dbms_xmlparser.freeParser(l_parser);
 64     ---   dbms_xmldom.freeDocument(l_doc);
 65*   END;
SQL> /

Procedure created.

SQL> exec  Dpr_Insert_From_Xml_CCR
BEGIN Dpr_Insert_From_Xml_CCR; END;

*
ERROR at line 1:
ORA-22275: invalid LOB locator specified
ORA-06512: at "SYS.DBMS_LOB", line 533
ORA-06512: at "TTT.DPR_INSERT_FROM_XML_CCR", line 25
ORA-06512: at line 1


SQL>