Home » Developer & Programmer » JDeveloper, Java & XML » How can i took xml field data form clob clumn directly ? (oracle 10.2.0.4 windows xp)
How can i took xml field data form clob clumn directly ? [message #422005] Sun, 13 September 2009 00:13 Go to next message
halim
Messages: 100
Registered: September 2008
Senior Member

Dear,

i have been suffreing a problem about xml.
i have a table with two columns, one is bfile data type other is clob data type. A xml data is stored into both cloumns
as clob and bfile. My xml data format is correct.
when i took xml data from bfile column, then dbms_xmlparser.parseClob successfully parse my xml data.

Code below:
===============
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); 


But when i took xml data form clob clumn directly, then dbms_xmlparser.parseClob parse failed and go to exception.

Code below:
============
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 



How can i took xml field data form clob clumn directly ?

my procedure
------------
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;


Re: How can i took xml field data form clob clumn directly ? [message #422007 is a reply to message #422005] Sun, 13 September 2009 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
dbms_xmlparser.parseClob parse failed and go to exception.

Which one?
Use SQL*Plus and copy and paste your session.
Indent the code.

Put a WORKING test case we can reproduce including data. What you posted does not compile.
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

Regards
Michel
Re: How can i took xml field data form clob clumn directly ? [message #422009 is a reply to message #422007] Sun, 13 September 2009 02:29 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member

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



Hi michel i want to insert data in a table directly from clob data type.
I have a long xml document.
I am useing this way because i want to
keep save the xml in my database table first.

regards
Halim
Re: How can i took xml field data form clob clumn directly ? [message #422014 is a reply to message #422009] Sun, 13 September 2009 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove the WHEN OTHERS clause and restart then you and we will know the real error.

Regards
Michel

[Updated on: Sun, 13 September 2009 05:03]

Report message to a moderator

Re: How can i took xml field data form clob clumn directly ? [message #422089 is a reply to message #422014] Mon, 14 September 2009 06:02 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member

hi michel

The Error is -

ORA-22275: invalid LOB locator specified

Cause
There are several causes: (1) the LOB locator was never
initialized; (2) the locator is for a BFILE and the routine
expects a BLOB/CLOB/NCLOB locator; (3) the locator is for a
BLOB/CLOB/NCLOB and the routine expects a BFILE locator; (4)
trying to update the LOB in a trigger body -- LOBs in trigger
bodies are read only; (5) the locator is for a BFILE/BLOB and
the routine expects a CLOB/NCLOB locator; (6) the locator is
for a CLOB/NCLOB and the routine expects a BFILE/BLOB locator;

Action
For (1), initialize the LOB locator by selecting into the
locator variable or by setting the LOB locator to empty. For
(2),(3), (5) and (6)pass the correct type of locator into the
routine. For (4), remove the trigger body code that updates the LOB value.

but don't understand, what actually will do?

[Updated on: Mon, 14 September 2009 06:05]

Report message to a moderator

Re: How can i took xml field data form clob clumn directly ? [message #422106 is a reply to message #422089] Mon, 14 September 2009 07:47 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-22275: invalid LOB locator specified

From which line.
Once again "Use SQL*Plus and copy and paste your session."

Regards
Michel
Re: How can i took xml field data form clob clumn directly ? [message #422198 is a reply to message #422106] Tue, 15 September 2009 01:31 Go to previous message
halim
Messages: 100
Registered: September 2008
Senior Member

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>

Previous Topic: XML in database - what are is your experience?
Next Topic: How to use Collection or array (Oracle) in java
Goto Forum:
  


Current Time: Sat Oct 01 00:24:08 CDT 2016

Total time taken to generate the page: 0.19329 seconds