Hi all,
I'm working with the following database version:
select * from v$version;
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
| PL/SQL Release 9.2.0.8.0 - Production |
|
| CORE 9.2.0.8.0 Production |
|
| TNS for HPUX: Version 9.2.0.8.0 - Production |
|
| NLSRTL Version 9.2.0.8.0 - Production |
|
5 rows selected.
The following query is working fine in our development environment (same DB version):
WITH sample_data
AS (SELECT xmltype ('<?xml version="1.0" encoding="UTF-8"?>
<GenericRequest xmlns="http://webservices.mysite.com/test" xmlns:ns="http://webservices.mysite.com/test0" xmlns:ns1="http://webservices.mysite.com/test1" xmlns:ns2="http://webservices.mysite.com/test2">
<ns1:order>
<ns1:orderItems>
<ns1:orderItem>
<ns1:operation>create</ns1:operation>
<ns1:brickId>TST123</ns1:brickId>
<ns1:brickAttributes>
<ns1:attribute>
<ns2:name>COUNTRY</ns2:name>
<ns2:value>US</ns2:value>
</ns1:attribute>
<ns1:attribute>
<ns2:name>CUST_ID</ns2:name>
<ns2:value>12345</ns2:value>
</ns1:attribute>
<ns1:attribute>
<ns2:name>CITY</ns2:name>
<ns2:value>New York</ns2:value>
</ns1:attribute>
</ns1:brickAttributes>
</ns1:orderItem>
</ns1:orderItems>
</ns1:order>
</GenericRequest>') xmldoc
FROM DUAL)
SELECT EXTRACTVALUE (VALUE (x), '/ns1:attribute/ns2:name', 'xmlns:ns1="http://webservices.mysite.com/test1", xmlns:ns2="http://webservices.mysite.com/test2"') AS prmname
, EXTRACTVALUE (VALUE (x), '/ns1:attribute/ns2:value', 'xmlns:ns1="http://webservices.mysite.com/test1", xmlns:ns2="http://webservices.mysite.com/test2"') AS prmval
FROM sample_data t
, TABLE (XMLSEQUENCE (EXTRACT (t.xmldoc, '/GenericRequest/ns1:order/ns1:orderItems/ns1:orderItem/ns1:brickAttributes/ns1:attribute', 'xmlns="http://webservices.mysite.com/test", xmlns:ns1="http://webservices.mysite.com/test1"'))) x;
but when run in preproduction and production environment is not working and I'm getting the following error:
ORA-29900: operator binding does not exist
ORA-06553: PLS-306: wrong number or types of arguments in call to 'EXTRACT'
It looks like the namespace parameter is not accepted in the EXTRACT function but I don't understand why it is working fine in test.
Edit: after some investigations I found the following:
In production and pre-production EXTRACT is defined as an operator and bind to package SYS.XMLINDEXOPS.
Here are the query:
select * from all_operators;
OWNER OPERATOR_NAME NUMBER_OF_BINDS
------------------------------ ------------------------------ ---------------
SYS EXTRACT 1
SYS EXISTSNODE 1
SYS OLAP_EXPRESSION 1
SYS XMLSEQUENCE 3
4 rows selected.
select owner, operator_name, binding#, function_name, return_type from all_opbindings;
OWNER OPERATOR_NAME BINDING# FUNCTION_NAME RETURN_TYPE
------ ---------------- ---------- ----------------------------------- --------------------
SYS OLAP_EXPRESSION 1 "OLAP_NUMBER_SRF" NUMBER
SYS XMLSEQUENCE 1 "SYS"."XMLSEQUENCEFROMXMLTYPE" XMLSEQUENCETYPE
SYS EXTRACT 1 "XMLINDEXOPS"."EXTRACT" XMLTYPE
SYS EXISTSNODE 1 "XMLINDEXOPS"."EXISTSNODE" NUMBER
SYS XMLSEQUENCE 2 "SYS"."XMLSEQUENCEFROMREFCURSOR" XMLSEQUENCETYPE
SYS XMLSEQUENCE 3 "SYS"."XMLSEQUENCEFROMREFCURSOR2" XMLSEQUENCETYPE
6 rows selected.
desc sys.xmlindexops;
FUNCTION EXISTSNODE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COLVAL XMLTYPE IN
TEXT VARCHAR2 IN
FUNCTION EXTRACT RETURNS XMLTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COLVAL XMLTYPE IN
TEXT VARCHAR2 IN
In my test environment the EXISTSNODE and EXTRACT are not defined as operators and the package SYS.XMLINDEXOPS does not exist.
Any suggestion/ideas?
Regards.
Alberto