OraFAQ Forum: SQL & PL/SQL » XMLtype throwing an error ORA-00932

Home » SQL & PL/SQL » SQL & PL/SQL » XMLtype throwing an error ORA-00932 (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)

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

XMLtype throwing an error ORA-00932 [message #685329]

Thu, 09 December 2021 07:41

wtolentino
Messages: 422
Registered: March 2005

Senior Member

we are attempting to debug this xmltype text:

Code: [Select all] [Show/ hide]

  <WEPICD-XPARM-INQ>
    <DIST-CD>66</DIST-CD>
    <CASE-NO>000001234S</CASE-NO>
    <FROM-DATE>022997</FROM-DATE>
    <TO-DATE>012120</TO-DATE>
    <REC-NO>0</REC-NO>
  </WEPICD-XPARM-INQ>

when run on a query it is throwing an error:

Code: [Select all] [Show/ hide]

ORA-00932: inconsistent datatypes: expected - got -
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
Error at Line: 44 Column: 26

i'm not much familiar with XMLtypes. tried some like wrapping in CAST, XMLTYPE, ...etc. but could not figure out.

this was the code that is throwing an error:

Code: [Select all] [Show/ hide]

select extractvalue(value(p), '/WEPICS-XPARM-INQ/DIST-CD/text()')  as pDistrinctCd,
       extractvalue(value(p), '/WEPICS-XPARM-INQ/CASE-NO/text()')  as pCaseNumId,
       extractvalue(value(p), 'WEPICS-XPARM-INQ/FROM-DATE/text()') as pDateIn1,
       extractvalue(value(p), 'WEPICS-XPARM-INQ/TO-DATE/text()')   as pDateIn2,
       extractvalue(value(p), 'WEPICS-XPARM-INQ/REC-NO/text()')    as pRecNumI
  from table(xmlsequence(extract('<WEPICD-XPARM-INQ>
                                    <DIST-CD>66</DIST-CD>
                                    <CASE-NO>000001234S</CASE-NO>
                                    <FROM-DATE>022997</FROM-DATE>
                                    <TO-DATE>012120</TO-DATE>
                                    <REC-NO>0</REC-NO>
                                  </WEPICD-XPARM-INQ>', '/WEPICS-XPARM-INQ/*')));

please help. thank you.

Report message to a moderator

Re: XMLtype throwing an error ORA-00932 [message #685330 is a reply to message #685329]

Thu, 09 December 2021 08:05

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Code: [Select all] [Show/ hide]

SQL> with
  2    data as (
  3      select XMLTYPE(
  4  '<WEPICD-XPARM-INQ>
  5     <DIST-CD>66</DIST-CD>
  6     <CASE-NO>000001234S</CASE-NO>
  7     <FROM-DATE>022997</FROM-DATE>
  8     <TO-DATE>012120</TO-DATE>
  9     <REC-NO>0</REC-NO>
 10   </WEPICD-XPARM-INQ>') p from dual
 11    )
 12  select extractvalue(p, '/WEPICD-XPARM-INQ/DIST-CD')  as pDistrinctCd,
 13         extractvalue(p, '/WEPICD-XPARM-INQ/CASE-NO')  as pCaseNumId,
 14         extractvalue(p, 'WEPICD-XPARM-INQ/FROM-DATE') as pDateIn1,
 15         extractvalue(p, 'WEPICD-XPARM-INQ/TO-DATE')   as pDateIn2,
 16         extractvalue(p, 'WEPICD-XPARM-INQ/REC-NO')    as pRecNumI
 17    from data;
PDISTRINCTCD
----------------------------------------------------------------------------
PCASENUMID
----------------------------------------------------------------------------
PDATEIN1
----------------------------------------------------------------------------
PDATEIN2
----------------------------------------------------------------------------
PRECNUMI
----------------------------------------------------------------------------
66
000001234S
022997
012120
0

1 row selected.

Code: [Select all] [Show/ hide]

SQL> with
  2    data as (
  3      select XMLTYPE(
  4  '<WEPICD-XPARM-INQ>
  5     <DIST-CD>66</DIST-CD>
  6     <CASE-NO>000001234S</CASE-NO>
  7     <FROM-DATE>022997</FROM-DATE>
  8     <TO-DATE>012120</TO-DATE>
  9     <REC-NO>0</REC-NO>
 10   </WEPICD-XPARM-INQ>') p from dual
 11    )
 12  select t.*
 13  from data,
 14       xmltable('/' passing p
 15         columns
 16           pDistrinctCd number       path '/WEPICD-XPARM-INQ/DIST-CD',
 17           pCaseNumId   varchar2(10) path '/WEPICD-XPARM-INQ/CASE-NO',
 18           pDateIn1     varchar2(8)  path '/WEPICD-XPARM-INQ/FROM-DATE',
 19           pDateIn2     varchar2(8)  path '/WEPICD-XPARM-INQ/TO-DATE',
 20           pRecNumI     number       path '/WEPICD-XPARM-INQ/REC-NO') t
 21  /
PDISTRINCTCD PCASENUMID PDATEIN1 PDATEIN2   PRECNUMI
------------ ---------- -------- -------- ----------
          66 000001234S 022997   012120            0

1 row selected.

Report message to a moderator

Re: XMLtype throwing an error ORA-00932 [message #685331 is a reply to message #685329]

Thu, 09 December 2021 08:28

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

extractvalue is deprecated EXTRACTVALUE:

Note:The EXTRACTVALUE function is deprecated. It is still supported for backward compatibility. However, Oracle recommends that you use the XMLTABLE function, or the XMLCAST and XMLQUERY functions instead. See XMLTABLE, XMLCAST, and XMLQUERY for more information.

SY.

Report message to a moderator

Re: XMLtype throwing an error ORA-00932 [message #685334 is a reply to message #685330]

Thu, 09 December 2021 09:50

wtolentino
Messages: 422
Registered: March 2005

Senior Member

thanks that works

Report message to a moderator

Previous Topic:	Using Procedure Update Date day -1
Next Topic:	UTL_FILE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 06 04:48:33 CDT 2025