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  |
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  |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
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 #422089 is a reply to message #422014] |
Mon, 14 September 2009 06:02 |
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 #422198 is a reply to message #422106] |
Tue, 15 September 2009 01:31 |
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>
|
|
|
|
Goto Forum:
Current Time: Sun Feb 09 09:37:26 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
