EXTRACTVALUE and datatype errors Oracle Tips by Burleson Consulting June 5, 2008

Question:

I’m getting this error while extracting a value with extractvalue:

SQL> SELECT
2 EXTRACTVALUE(xmldoc, '/EMP/ROW/EMPNO'),
3 EXTRACTVALUE(xmldoc, '/EMP/ROW/ENAME')
4 FROM
5 xml_tab;
EXTRACTVALUE(xmldoc, '/EMP/ROW/ENAME')
*
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected - got -


This is the xml file content stored in a table as xml_tab in clob column :

emp>
<row>
<empno>3</empno>
<ename>ra</ename>
</row>
<row>
<empno>9</empno>
<ename>a</ename>
</row>
<row>
<empno>6</empno>
<ename>r</ename>
</row>
</emp>


Answer:

 Use this command:

EXTRACTVALUE(xmltype(xmldoc), '/EMP/ROW/EMPNO')

The extractvalue error, ORA-00932, was being thrown because you had the wrong datatype.

Documentation provides this information:

The EXTRACTVALUE function takes as arguments an XMLType instance and an XPath expression and returns a scalar value of the resultant node.

From:  https://MOSC.oracle.com/MOSC/plsql/f?p=130:14:7354832556719628952::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,400213.1,1,1,1,helvetica

Also note that you may be experiencing a Bug when ORA-00932 is thrown with EXTRACTVALUE:

Bug 4116896 ORA-00932 using extractValue() in SQL inside PL/SQL
             Bug 4116896 ORA-00932 using extractValue() in SQL inside PL/SQL
              Component: XDB
              Fixed Ver(s): 9207
               Symptom(s):
                - This problem is exposed in 9.2.0.6. PL/SQL procedure creation can fail with ORA-932 if it has a call to extractValue().
                   For example:
                      CREATE OR REPLACE PROCEDURE getDependants(path IN VARCHAR2) IS
                        myval varchar2(4000);
                      BEGIN
                        select any_path into myval from resource_view r, content_ref c
                         where extractValue(r.res, '/Resource/XMLRef') = c.from_content_object;
                      END getDependants;
                      /
                      ^
               Available Workaround(s):
                               Use sys_op_r2o in the select statement.
                               For example:
                                   select any_path into myval from resource_view r, content_ref c
                                   where sys_op_r2o(extractValue(r.res, '/Resource/XMLRef'))=sys_op_r2o(c.from_content_object);
               References:
                 Note 4116896.8 Bug 4116896 - ORA-932 using extractValue() in SQL inside PL/SQL

For more information on ORA-00932, you may want to check out the following link:

http://www.dba-oracle.com/sf_ora_00932_inconsistent_datatypes_expected_string_got_string.htm

---