OraFAQ Forum: JDeveloper, Java & XML » Problem to load XML file into table

Home » Developer & Programmer » JDeveloper, Java & XML » Problem to load XML file into table (Oracle 10.2.0.3 )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem to load XML file into table [message #434974]

Sat, 12 December 2009 09:41

rafaelrc
Messages: 2
Registered: December 2009
Location: Bruxelles

Junior Member

Hi all,
I am getting a problem here after loading a xml into the database. Checking some messages previously in this forum helped me a lot.

the sequence is like this:

Table

SQL> desc tbl_xml
Nome Nulo? Tipo
----------------------------------------- -------- --------
XML XMLTYPE

Procedure to import, from a file, the xml to a table

create or replace PROCEDURE p_load_xml_rafa (p_dir IN VARCHAR2,
p_filename IN VARCHAR2)
IS
l_bfile BFILE := BFILENAME(p_dir, p_filename);
l_clob CLOB;
BEGIN
DBMS_LOB.createtemporary (l_clob, TRUE);

DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));
DBMS_LOB.fileclose(l_bfile);

INSERT INTO tbl_xml (xml) VALUES (XMLTYPE.createXML(l_clob));
COMMIT;

DBMS_LOB.freetemporary (l_clob);
END;
/

The procedure above runs ok.

Procedure that get the xml and get the values of eache node. I put just one part of the source code.

create or replace
procedure p_carrega_xml_arq_tabela
is
l_bfile BFILE;
l_clob CLOB;
l_parser dbms_xmlparser.Parser;
l_doc dbms_xmldom.DOMDocument;
l_doc_ender_emit dbms_xmldom.DOMDocument;
l_nl dbms_xmldom.DOMNodeList;
l_nl_ender_emit dbms_xmldom.DOMNodeList;
l_n dbms_xmldom.DOMNode;
l_temp VARCHAR2(4000);

v_caminho_emit varchar2(20);
v_caminho_dest varchar2(20);
v_caminho_ender_emit varchar2(40);
v_caminho_nfe varchar2(40);
v_caminho_ender_dest varchar2(40);
TYPE tab_type IS TABLE OF tab_ide%ROWTYPE;
t_tab tab_type := tab_type();

V_DOM XMLDOM.DOMDOCUMENT;
L_NORAIZ XMLDOM.DOMNODE;
L_ITEM XMLDOM.DOMNODELIST;

v_xml xmltype;

FUNCTION f_obtem_doc_dom
RETURN XMLDOM.DOMDOCUMENT IS
v_xml XMLTYPE;
v_parser DBMS_XMLPARSER.PARSER;
v_xmldoc XMLDOM.DOMDOCUMENT;
BEGIN
select xml into v_xml from tbl_xml;

v_parser := DBMS_XMLPARSER.NEWPARSER;
DBMS_XMLPARSER.SETVALIDATIONMODE(v_parser, FALSE);
DBMS_XMLPARSER.PARSECLOB(v_parser, v_xml.GETCLOBVAL());
V_XMLDOC := DBMS_XMLPARSER.GETDOCUMENT(v_parser);
DBMS_XMLPARSER.FREEPARSER(v_parser);
RETURN v_xmldoc;

EXCEPTION
WHEN OTHERS THEN
NULL ;
RAISE;
END f_obtem_doc_dom;

BEGIN

V_DOM := f_obtem_doc_dom;
l_noraiz := XMLDOM.MAKENODE(v_dom);
l_item := dbms_xslprocessor.selectNodes(l_noraiz,'/NFe/infNFe/ide');

dbms_output.put_line(' DBMS_XMLDOM.GETLENGTH(L_NL) -1) = '|| DBMS_XMLDOM.GETLENGTH(L_item));

FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_item) - 1 LOOP
l_n := dbms_xmldom.item(l_item, cur_emp);
.
.
.
.

the error happens in the red line above.

When I use this XML
<?xml version="1.0" encoding="UTF-8" ?><NFe><infNFe versao="1.10"><ide><cUF>32</cUF><cNF>044956616</cNF> .....

it runs ok.. no errors

but this one I receive an error message

<NFe xmlns="here there is a http address"><infNFe Id="NFe32091031757503000130550000000053690449566168" versao="1.10"><ide><cUF>32</cUF> ....

the error message is:
ORA-06502: PL/SQL: erro: invalid LOB locator specified: ORA-22275 number or value error
ORA-06512: em "SYS.DBMS_LOB", line 533
ORA-06512: em "SYSNFE.P_CARREGA_XML_ARQ_TABELA", line 147
ORA-06502: PL/SQL: erro numérico ou de valor
ORA-06512: em line 1

Does anyone have any idea?

Thanks in advance
Rafael

Report message to a moderator

Re: Problem to load XML file into table [message #434976 is a reply to message #434974]

Sat, 12 December 2009 09:45

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

// *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.

We can not reproduce problem without the whole & functioning procedure.

....
is inadequate

[Updated on: Sat, 12 December 2009 09:48]

Report message to a moderator

Re: Problem to load XML file into table [message #434978 is a reply to message #434976]

Sat, 12 December 2009 10:19

rafaelrc
Messages: 2
Registered: December 2009
Location: Bruxelles

Junior Member

this is the proc

create or replace
procedure p_carrega_xml_arq_tabela
is
l_bfile BFILE;
l_clob CLOB;
l_parser dbms_xmlparser.Parser;
l_doc dbms_xmldom.DOMDocument;
l_doc_ender_emit dbms_xmldom.DOMDocument;
l_nl dbms_xmldom.DOMNodeList;
l_nl_ender_emit dbms_xmldom.DOMNodeList;
l_n dbms_xmldom.DOMNode;
l_temp VARCHAR2(4000);

v_caminho_emit varchar2(20);
v_caminho_dest varchar2(20);
v_caminho_ender_emit varchar2(40);
v_caminho_nfe varchar2(40);
v_caminho_ender_dest varchar2(40);
TYPE tab_type IS TABLE OF tab_ide%ROWTYPE;
t_tab tab_type := tab_type();

V_DOM XMLDOM.DOMDOCUMENT;
L_NORAIZ XMLDOM.DOMNODE;
L_ITEM XMLDOM.DOMNODELIST;

v_xml xmltype;

FUNCTION f_obtem_doc_dom
RETURN XMLDOM.DOMDOCUMENT IS
v_xml XMLTYPE;
v_parser DBMS_XMLPARSER.PARSER;
v_xmldoc XMLDOM.DOMDOCUMENT;
BEGIN
select xml into v_xml from tbl_xml;
v_parser := DBMS_XMLPARSER.NEWPARSER;
DBMS_XMLPARSER.SETVALIDATIONMODE(v_parser, FALSE);
DBMS_XMLPARSER.PARSECLOB(v_parser, v_xml.GETCLOBVAL());
V_XMLDOC := DBMS_XMLPARSER.GETDOCUMENT(v_parser);
DBMS_XMLPARSER.FREEPARSER(v_parser);
RETURN v_xmldoc;

EXCEPTION
WHEN OTHERS THEN
NULL ;
RAISE;
END f_obtem_doc_dom;

BEGIN

V_DOM := f_obtem_doc_dom;
l_noraiz := XMLDOM.MAKENODE(v_dom);
l_item := dbms_xslprocessor.selectNodes(l_noraiz,'//NFe/infNFe/ide');

dbms_output.put_line(' DBMS_XMLDOM.GETLENGTH(L_NL) -1) = '|| DBMS_XMLDOM.GETLENGTH(L_item));

FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_item) -1 LOOP
l_n := dbms_xmldom.item(l_item, cur_emp);

t_tab.extend;
v_caminho_nfe := '/NFe/infNFe/ide/';
dbms_xslprocessor.valueOf(l_n,v_caminho_nfe||'cUF/text()',t_tab(t_tab.last).ide_cuf);
dbms_xslprocessor.valueOf(l_n,v_caminho_nfe||'cNF/text()',t_tab(t_tab.last).ide_cnf);
dbms_xslprocessor.valueOf(l_n,v_caminho_nfe||'natOp/text()',t_tab(t_tab.last).ide_natop);
dbms_xslprocessor.valueOf(l_n,v_caminho_nfe||'indPag/text()',t_tab(t_tab.last).ide_indpag);
dbms_xslprocessor.valueOf(l_n,v_caminho_nfe||'mod/text()',t_tab(t_tab.last).ide_mod);
dbms_xslprocessor.valueOf(l_n,v_caminho_nfe||'serie/text()',t_tab(t_tab.last).ide_serie);
dbms_xslprocessor.valueOf(l_n,v_caminho_nfe||'nNF/text()',t_tab(t_tab.last).ide_nnf);
dbms_xslprocessor.valueOf(l_n,v_caminho_nfe||'dEmi/text()',t_tab(t_tab.last).ide_demi);
dbms_xslprocessor.valueOf(l_n,v_caminho_nfe||'dSaiEnt/text()',t_tab(t_tab.last).ide_dsaient);
dbms_xslprocessor.valueOf(l_n,v_caminho_nfe||'tpNF/text()',t_tab(t_tab.last).ide_tpnf);
dbms_xslprocessor.valueOf(l_n,v_caminho_nfe||'cMunFG/text()',t_tab(t_tab.last).ide_cmunfg);
dbms_xslprocessor.valueOf(l_n,v_caminho_nfe||'tpImp/text()',t_tab(t_tab.last).ide_tpimp);
dbms_xslprocessor.valueOf(l_n,v_caminho_nfe||'tpEmis/text()',t_tab(t_tab.last).ide_tpemis);
dbms_xslprocessor.valueOf(l_n,v_caminho_nfe||'cDV/text()',t_tab(t_tab.last).ide_cdv);
dbms_xslprocessor.valueOf(l_n,v_caminho_nfe||'tpAmb/text()',t_tab(t_tab.last).ide_tpamb);
dbms_xslprocessor.valueOf(l_n,v_caminho_nfe||'finNFe/text()',t_tab(t_tab.last).ide_finnfe);
dbms_xslprocessor.valueOf(l_n,v_caminho_nfe||'procEmi/text()',t_tab(t_tab.last).ide_procemi);
dbms_xslprocessor.valueOf(l_n,v_caminho_nfe||'verProc/text()',t_tab(t_tab.last).ide_verproc);

v_caminho_emit := '/NFe/infNFe/emit/';

dbms_xslprocessor.valueOf(l_n,v_caminho_emit ||'xNome/text()',t_tab(t_tab.last).emit_xnome);
dbms_xslprocessor.valueOf(l_n,v_caminho_emit ||'CNPJ/text()',t_tab(t_tab.last).emit_cnpj);
dbms_xslprocessor.valueOf(l_n,v_caminho_emit ||'xNome/text()',t_tab(t_tab.last).emit_xnome);
dbms_xslprocessor.valueOf(l_n,v_caminho_emit ||'xFant/text()',t_tab(t_tab.last).emit_xfant);
dbms_xslprocessor.valueOf(l_n,v_caminho_emit ||'IE/text()',t_tab(t_tab.last).emit_ie);

v_caminho_ender_emit := '/NFe/infNFe/emit/enderEmit/';
dbms_xslprocessor.valueOf(l_n,v_caminho_ender_emit||'xLgr/text()',t_tab(t_tab.last).ender_xlgr);
dbms_xslprocessor.valueOf(l_n,v_caminho_ender_emit||'nro/text()',t_tab(t_tab.last).ender_nro);
dbms_xslprocessor.valueOf(l_n,v_caminho_ender_emit||'xCpl/text()',t_tab(t_tab.last).ender_xcpl);
dbms_xslprocessor.valueOf(l_n,v_caminho_ender_emit||'xBairro/text()',t_tab(t_tab.last).ender_xbairro);
dbms_xslprocessor.valueOf(l_n,v_caminho_ender_emit||'cMun/text()',t_tab(t_tab.last).ender_cmun);
dbms_xslprocessor.valueOf(l_n,v_caminho_ender_emit||'xMun/text()',t_tab(t_tab.last).ender_xmun);
dbms_xslprocessor.valueOf(l_n,v_caminho_ender_emit||'UF/text()',t_tab(t_tab.last).ender_uf);
dbms_xslprocessor.valueOf(l_n,v_caminho_ender_emit||'CEP/text()',t_tab(t_tab.last).ender_cep);
dbms_xslprocessor.valueOf(l_n,v_caminho_ender_emit||'cPais/text()',t_tab(t_tab.last).ender_cpais);
dbms_xslprocessor.valueOf(l_n,v_caminho_ender_emit||'xPais/text()',t_tab(t_tab.last).ender_xpais);
dbms_xslprocessor.valueOf(l_n,v_caminho_ender_emit||'fone/text()',t_tab(t_tab.last).ender_fone);

v_caminho_dest := '/NFe/infNFe/dest/';

dbms_xslprocessor.valueOf(l_n,v_caminho_dest||'CNPJ/text()',t_tab(t_tab.last).dest_cnpj);
dbms_xslprocessor.valueOf(l_n,v_caminho_dest||'xNome/text()',t_tab(t_tab.last).dest_xnome);

v_caminho_ender_dest := '/NFe/infNFe/dest/enderDest';

dbms_xslprocessor.valueOf(l_n,v_caminho_ender_dest||'xCpl/text()',t_tab(t_tab.last).enderdest_xcpl);
dbms_xslprocessor.valueOf(l_n,v_caminho_ender_dest||'xBairro/text()',t_tab(t_tab.last).enderdest_xbairro);
dbms_xslprocessor.valueOf(l_n,v_caminho_ender_dest||'cMun/text()',t_tab(t_tab.last).enderdest_cmun);
dbms_xslprocessor.valueOf(l_n,v_caminho_ender_dest||'xMun/text()',t_tab(t_tab.last).enderdest_xmun);
dbms_xslprocessor.valueOf(l_n,v_caminho_ender_dest||'UF/text()',t_tab(t_tab.last).enderdest_uf);
dbms_xslprocessor.valueOf(l_n,v_caminho_ender_dest||'CEP/text()',t_tab(t_tab.last).enderdest_cep);
dbms_xslprocessor.valueOf(l_n,v_caminho_ender_dest||'cPais/text()',t_tab(t_tab.last).enderdest_cpais);
dbms_xslprocessor.valueOf(l_n,v_caminho_ender_dest||'xPais/text()',t_tab(t_tab.last).enderdest_xpais);
--dbms_xslprocessor.valueOf(l_n,v_caminho_ender_dest||'fone/text()',t_tab(t_tab.last).enderdest_fone);

END LOOP;

FOR cur_emp IN t_tab.first .. t_tab.last LOOP
dbms_output.put_line ('IDE '||t_tab(cur_emp).ide_natop );
dbms_output.put_line ('EMIT '||t_tab(cur_emp).emit_xnome );
dbms_output.put_line ('ENDER_EMIT '||t_tab(cur_emp).ender_fone );

insert into temp_ide values (t_tab(cur_emp).ide_natop,t_tab(cur_emp).emit_xnome,t_tab(cur_emp).ender_fone);
commit;

end loop;

--dbms_xmldom.freeDocument(l_doc);

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||sqlerrm);

END;

Report message to a moderator

Re: Problem to load XML file into table [message #434985 is a reply to message #434978]

Sat, 12 December 2009 19:05

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Get rid off all the stupid "when other" exception handlers first, which do nothing but mask the real errors.

Report message to a moderator

Previous Topic:	How to create an data model of an Oracle to XML schema
Next Topic:	Problem in Extending VO in Jdev10g-View links

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 03 17:36:44 CST 2025