Home » Developer & Programmer » JDeveloper, Java & XML » ORA-03113 with dbms_xslprocessor.selectnodes
ORA-03113 with dbms_xslprocessor.selectnodes [message #168489] Thu, 20 April 2006 12:53
Tafer
Messages: 64
Registered: July 2005
Location: Here!
Member
Hi!

I'm having this problem (see title) with a simple example found here (I'm pretty new with this XML on Oracle stuff): http://www.oracle-base.com/articles/9i/ParseXMLDocuments9i.php

My DB: 9.2.0.4 on Windows 2k


To run the example I had to modify this section of code:

  dbms_lob.loadFromFile(dest_lob => l_clob,
                        src_lob  => l_bfile,
                        amount   => dbms_lob.getLength(l_bfile));


to

  dbms_lob.loadFromFile(l_clob,
                        l_bfile,
                        dbms_lob.getLength(l_bfile));



Here is the entire code:

-- As SYS
CREATE DIRECTORY xml_dir AS 'c:\';
GRANT READ ON DIRECTORY xml_dir TO <user-name>;

-- As schema owner
CREATE TABLE EMP (
  EMPNO     NUMBER(4),
  ENAME     VARCHAR2(10),
  JOB       VARCHAR2(9),
  MGR       NUMBER(4),
  HIREDATE  DATE,
  SAL       NUMBER(7, 2),
  COMM      NUMBER(7, 2),
  DEPTNO   NUMBER(2));


DECLARE
  l_bfile   BFILE;
  l_clob    CLOB;
  l_parser  dbms_xmlparser.Parser;
  l_doc     dbms_xmldom.DOMDocument;
  l_nl      dbms_xmldom.DOMNodeList;
  l_n       dbms_xmldom.DOMNode;
  l_temp    VARCHAR2(1000);

  TYPE tab_type IS TABLE OF emp%ROWTYPE;
  t_tab  tab_type := tab_type();
BEGIN

  l_bfile := BFileName('XML_DIR', 'emp.xml');
  dbms_lob.createtemporary(l_clob, cache=>FALSE);
  dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
  dbms_lob.loadFromFile(l_clob,
                        l_bfile,
                        dbms_lob.getLength(l_bfile));
  dbms_lob.close(l_bfile);
  
  -- make sure implicit date conversions are performed correctly
  dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MON-YYYY''');

  -- Create a parser.
  l_parser := dbms_xmlparser.newParser;

  -- Parse the document and create a new DOM document.
  dbms_xmlparser.parseClob(l_parser, l_clob);
  l_doc := dbms_xmlparser.getDocument(l_parser);

  -- Free resources associated with the CLOB and Parser now they are no longer needed.
  dbms_lob.freetemporary(l_clob);
  dbms_xmlparser.freeParser(l_parser);

  -- Get a list of all the EMP nodes in the document using the XPATH syntax.
  l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/EMPLOYEES/EMP');

  -- Loop through the list and create a new record in a tble collection
  -- for each EMP record.
  FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP
    l_n := dbms_xmldom.item(l_nl, cur_emp);

    t_tab.extend;

    -- Use XPATH syntax to assign values to he elements of the collection.
    dbms_xslprocessor.valueOf(l_n,'EMPNO',t_tab(t_tab.last).empno);
    dbms_xslprocessor.valueOf(l_n,'ENAME',t_tab(t_tab.last).ename);
    dbms_xslprocessor.valueOf(l_n,'JOB',t_tab(t_tab.last).job);
    dbms_xslprocessor.valueOf(l_n,'MGR',t_tab(t_tab.last).mgr);
    dbms_xslprocessor.valueOf(l_n,'HIREDATE',t_tab(t_tab.last).hiredate);
    dbms_xslprocessor.valueOf(l_n,'SAL',t_tab(t_tab.last).sal);
    dbms_xslprocessor.valueOf(l_n,'COMM',t_tab(t_tab.last).comm);
    dbms_xslprocessor.valueOf(l_n,'DEPTNO',t_tab(t_tab.last).deptno);
  END LOOP;

  -- Insert data into the real EMP table from the table collection.
  -- Form better performance multiple collections should be used to allow
  -- bulk binding using the FORALL construct but this would make the code
  -- too long-winded for this example.
  FOR cur_emp IN t_tab.first .. t_tab.last LOOP
    INSERT INTO emp
    (empno,
     ename,
     job,
     mgr,
     hiredate,
     sal,
     comm,
     deptno)
    VALUES
    (t_tab(cur_emp).empno,
     t_tab(cur_emp).ename,
     t_tab(cur_emp).job,
     t_tab(cur_emp).mgr,
     t_tab(cur_emp).hiredate,
     t_tab(cur_emp).sal,
     t_tab(cur_emp).comm,
     t_tab(cur_emp).deptno);
  END LOOP;

  COMMIT; 

  -- Free any resources associated with the document now it
  -- is no longer needed.
  dbms_xmldom.freeDocument(l_doc);

EXCEPTION
  WHEN OTHERS THEN
    dbms_lob.freetemporary(l_clob);
    dbms_xmlparser.freeParser(l_parser);
    dbms_xmldom.freeDocument(l_doc);
END;



And the XML (http://www.oracle-base.com/articles/9i/emp.xml):

<?xml version="1.0"?>
<EMPLOYEES>
  <EMP><EMPNO>7369</EMPNO><ENAME>SMITH</ENAME><JOB>CLERK</JOB><MGR>7902</MGR><HIREDATE>17-DEC-80</HIREDATE><SAL>800</SAL></EMP>
  <EMP><EMPNO>7499</EMPNO><ENAME>ALLEN</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>20-FEB-81</HIREDATE><SAL>1600</SAL><COMM>300</COMM></EMP>

  <EMP><EMPNO>7521</EMPNO><ENAME>WARD</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>22-FEB-81</HIREDATE><SAL>1250</SAL><COMM>500</COMM></EMP>
  <EMP><EMPNO>7566</EMPNO><ENAME>JONES</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>02-APR-81</HIREDATE><SAL>2975</SAL></EMP>

  <EMP><EMPNO>7654</EMPNO><ENAME>MARTIN</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>28-SEP-81</HIREDATE><SAL>1250</SAL><COMM>1400</COMM></EMP>
  <EMP><EMPNO>7698</EMPNO><ENAME>BLAKE</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>01-MAY-81</HIREDATE><SAL>2850</SAL></EMP>

  <EMP><EMPNO>7782</EMPNO><ENAME>CLARK</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>09-JUN-81</HIREDATE><SAL>2450</SAL></EMP>
  <EMP><EMPNO>7788</EMPNO><ENAME>SCOTT</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>19-APR-87</HIREDATE><SAL>3000</SAL></EMP>
  <EMP><EMPNO>7839</EMPNO><ENAME>KING</ENAME><JOB>PRESIDENT</JOB><HIREDATE>17-NOV-81</HIREDATE><SAL>5000</SAL></EMP>

  <EMP><EMPNO>7844</EMPNO><ENAME>TURNER</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>08-SEP-81</HIREDATE><SAL>1500</SAL><COMM>0</COMM></EMP>
  <EMP><EMPNO>7876</EMPNO><ENAME>ADAMS</ENAME><JOB>CLERK</JOB><MGR>7788</MGR><HIREDATE>23-MAY-87</HIREDATE><SAL>1100</SAL></EMP>

  <EMP><EMPNO>7900</EMPNO><ENAME>JAMES</ENAME><JOB>CLERK</JOB><MGR>7698</MGR><HIREDATE>03-DEC-81</HIREDATE><SAL>950</SAL></EMP>
  <EMP><EMPNO>7902</EMPNO><ENAME>FORD</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>03-DEC-81</HIREDATE><SAL>3000</SAL></EMP>
  <EMP><EMPNO>7934</EMPNO><ENAME>MILLER</ENAME><JOB>CLERK</JOB><MGR>7782</MGR><HIREDATE>23-JAN-82</HIREDATE><SAL>1300</SAL></EMP>

</EMPLOYEES>


I'll appreciate any help!

UPDATE:
Looks like it was a known bug in other releases... (Supposedly, I shouldn't be affected)

Anyway, if you know a faster/easier way to do this kind of job I'll appreciate any example, link, etc...

Thanks for your time.

[Updated on: Thu, 20 April 2006 17:55]

Report message to a moderator

Previous Topic: SRDemo ???: TopLink Map 'SRMap' -> One of the packages is incomplete.
Next Topic: TOTALLY NOT ORACLE BUT THIS FORUM RESPONDS QUICK
Goto Forum:
  


Current Time: Thu Mar 28 11:57:39 CDT 2024