oracle - error reading xml from the plsql procedure -

i trying read xml plsql procedure using xmlparser package, getting error

 ora-31020: operation not allowed, reason: not supported ora-06512: @ "xdb.dbms_xmlparser", line 395 ora-06512: @ "sys.domsample", line 75 ora-06512: @ line 2 

domsample procedure name, , no statements there @ line number 75, , next line contains p := xmlparser.newparser.

can please me in resolving problem. or suggest simple way read xml in plsql.

you have provided scant few details doing, i'm afraid can guess.

i cannot reproduce error message have, have tried few things. perhaps you're calling oracle xml apis incorrectly? perhaps there's odd xml document you're attempting parse? i'm afraid have no idea, since haven't given source of domsample procedure nor xml document you're attempting parse.

i cannot believe line 75 of procedure blank line. line 75 of procedure, or line 75 of file contains procedure?

here's example using dbms_xmlparser , dbms_xmldom. merely reads out name of root element of xml string given:

set serveroutput on;  declare    p    dbms_xmlparser.parser;    d    dbms_xmldom.domdocument;    e    dbms_xmldom.domelement; begin    p := dbms_xmlparser.newparser;    dbms_xmlparser.parsebuffer(p, '<thisisatest />');    d := dbms_xmlparser.getdocument(p);    e := dbms_xmldom.getdocumentelement(d);    dbms_output.put_line('tag name ' || dbms_xmldom.gettagname(e)); end; / 

when run gives me output tag name thisisatest.

as simpler ways read xml, there's 1 in question answered earlier. don't know whether you, because know little you're trying achieve.

finally, please don't create objects in sys schema.

edit: in comment, mention you're using dbms_xmlparser.parse instead of dbms_xmlparser.parsebuffer. had play dbms_xmlparser.parse , hit same 'invalid resource handle or path name' error several times before finding worked. below managed working; there may better solution want this.

before can file i/o oracle, , appears include using dbms_xmlparser.parse, must first create oracle 'directory'. directories in oracle correspond directories on filesystem. note filesystem on machine on oracle database runs. if xml file isn't on same filesystem (e.g. oracle database on server , xml file on development pc), won't able use dbms_xmlparser.parse, unless first transfer file directory on database server's filesystem.

i'll start creating oracle directory corresponding directory on filesystem:

 sql> create or replace directory ora_dir '/home/luke/ora_dir';  directory created. 

i'm using linux here. if you're using windows, feel free reverse direction of slashes.

before go further, let's take quick @ xml file we'll read in:

 sql> host cat /home/luke/ora_dir/example.xml <?xml version="1.0" ?> <root>   <child /> </root> 

in sql*plus, host sends rest of line shell, or cmd.exe on windows. on windows you'd use type instead of cat.

finally, here's pl/sql block reads xml file:

 sql> set serveroutput on sql> declare   2     p    dbms_xmlparser.parser;   3     d    dbms_xmldom.domdocument;   4     e    dbms_xmldom.domelement;   5  begin   6     p := dbms_xmlparser.newparser;   7     dbms_xmlparser.setbasedir(p, 'ora_dir');   8     dbms_xmlparser.parse(p, 'example.xml');   9     d := dbms_xmlparser.getdocument(p);  10     e := dbms_xmldom.getdocumentelement(d);  11     dbms_output.put_line('tag name ' || dbms_xmldom.gettagname(e));  12  end;  13  / tag name root  pl/sql procedure completed.  sql> 

the difference between block , 1 further line called dbms_xmlparser.parsebuffer has been replaced 2 lines. first of these 2 lines calls dbms_xmlparser.setbasedir set base directory parser, , second calls dbms_xmlparser.parse using filename relative directory.

edit 2: code, wasn't working quite had hoped, , edited answer, follows:

create or replace procedure printelements(doc xmldom.domdocument) nl xmldom.domnodelist; len number; n xmldom.domnode; e xmldom.domelement; nodeval varchar2(100); begin    -- elements    nl := xmldom.getelementsbytagname(doc, '*');    len := xmldom.getlength(nl);       -- loop through elements    in 0..len-1 loop       n := xmldom.item(nl, i);       e := xmldom.makeelement(n => n);       dbms_output.put(xmldom.getnodename(n) || ' ');       nodeval := xmldom.getnodevalue(n);       -- here nodeval getting null, mistake doing?       dbms_output.put_line('  value: '|| nodeval );     end loop;     dbms_output.put_line(''); end printelements; 

this apparently returning values null, suggested last of 3 comments.

to quote a previous answer of mine on similar question:

in xml dom, elements don't have 'value' speak of. element nodes contain text nodes children, , these nodes contain values want.

so, try replacing line

      nodeval := xmldom.getnodevalue(n); 

with

      nodeval := xmldom.getnodevalue(xmldom.getfirstchild(n));