1 Reply Latest reply: Apr 22, 2014 11:39 AM by Marco Gralike RSS

    Error on Extract function

    AlbertoFaenza

      Hi all,

       

      I'm working with the following database version:

       

      select * from v$version;

       

      BANNER                                                    

      ----------------------------------------------------------------

      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