| XMLtype throwing an error ORA-00932 [message #685329] |
Thu, 09 December 2021 07:41  |
wtolentino
Messages: 398
Registered: March 2005
|
Senior Member |
|
|
we are attempting to debug this xmltype text:
<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:
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:
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.
|
|
|
|
| Re: XMLtype throwing an error ORA-00932 [message #685330 is a reply to message #685329] |
Thu, 09 December 2021 08:05  |
 |
Michel Cadot
Messages: 68637
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
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.
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. |
|
|
|
| Re: XMLtype throwing an error ORA-00932 [message #685331 is a reply to message #685329] |
Thu, 09 December 2021 08:28 |
Solomon Yakobson
Messages: 3270
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
