4 Replies Latest reply: Aug 17, 2008 4:14 PM by Marco Gralike RSS

    Testing Protocol Server  WSDL functionality

    Marco Gralike
      After installing / enabling the WSDL service as described in http://otnbeta.us.oracle.com/doc/db11r1/appdev.111/b28369/xdb_web_services.htm#CHDECACB; is there a "easy" way to test the service from within the database or is it only possible to test this via, for instance, with the use of JDeveloper Studio...
        • 1. Re: Testing Protocol Server  WSDL functionality
          Marco Gralike
          Problem solved on 11.1.0.6 (just updated the database from 11.1.0.5 to 11.1.0.6)

          Query in browser works now (following the examples in the mentioned chapter)...

          http://localhost:8080/orawsv/XDB/SALARY_CALCULATOR/TOTALDEPARTMENTSALARY?wsdl

          2 points...

          - Yes I know, I should NOT install under the XDB account
          (I will remove the test and use a proper user schema).
          - The URL is case-sensitive !!!
          Packages and procedures and functions created the normal way are in upper case. Using double quotes is probably the way to be able to create lowercase entries...

          (in other words the following will give an soap error: http://localhost:8080/orawsv/XDB/salary_CALCULATOR/TOTALDEPARTMENTSALARY?wsdl)

          Message was edited by:
          Marco Gralike
          • 2. Re: Testing Protocol Server  WSDL functionality
            mdrake
            connect sys/ as sysdba
            --
            def USERNAME=SCOTT
            --
            def PASSWORD=tiger
            --
            begin
              dbms_network_acl_admin.create_acl('localhost.xml', 'ACL for 127.0.0.1', '&USERNAME', true, 'connect');
              dbms_network_acl_admin.assign_acl('localhost.xml', '127.0.0.1');
            end;
            /
            COMMIT
            /
            grant XDB_WEBSERVICES to &USERNAME
            /
            grant XDB_WEBSERVICES_OVER_HTTP to &USERNAME
            /
            grant XDB_WEBSERVICES_WITH_PUBLIC to &USERNAME
            /
            connect &USERNAME/&PASSWORD
            --
            var url varchar2(700)
            --
            set long 10000
            --
            BEGIN
              :url :=   'http://&USERNAME:&PASSWORD@127.0.0.1:' || dbms_xdb.getHttpPort() || '/orawsv?wsdl';
            end;
            /
            print url
            --
            set long 10000 pages 0
            --
            select  httpuritype( :url ).getXML() from dual
            /
            create or replace procedure SQUARE ( parm in out number)
            as
            begin
              parm := parm * 2;
            end;
            /
            BEGIN
              :url :=   'http://&USERNAME:&PASSWORD@127.0.0.1:' || dbms_xdb.getHttpPort() || '/orawsv/SCOTT/SQUARE?wsdl';
            end;
            /
            print url
            --
            set long 10000 pages 0
            --
            select  httpuritype( :url ).getXML() from dual
            /
            C:\Documents and Settings\Mark D Drake>sqlplus / as sysdba
            
            SQL*Plus: Release 11.1.0.6.0 - Production on Mon Aug 13 22:41:22 2007
            
            Copyright (c) 1982, 2007, Oracle.  All rights reserved.
            
            
            Connected to:
            Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
            With the Partitioning, OLAP and Data Mining options
            
            SQL> connect sys/ as sysdba
            Enter password:
            Connected.
            SQL> def USERNAME=SCOTT
            SQL> --
            SQL> def PASSWORD=tiger
            SQL> --
            SQL> begin
              2    dbms_network_acl_admin.create_acl('localhost.xml', 'ACL for 127.0.0.1', '&USERNAME', true, 'connect');
              3    dbms_network_acl_admin.assign_acl('localhost.xml', '127.0.0.1');
              4  end;
              5  /
            old   2:   dbms_network_acl_admin.create_acl('localhost.xml', 'ACL for 127.0.0.1', '&USERNAME', true, 'connect');
            new   2:   dbms_network_acl_admin.create_acl('localhost.xml', 'ACL for 127.0.0.1', 'SCOTT', true, 'connect');
            
            PL/SQL procedure successfully completed.
            
            SQL> COMMIT
              2  /
            
            Commit complete.
            
            SQL> grant XDB_WEBSERVICES to &USERNAME
              2  /
            old   1: grant XDB_WEBSERVICES to &USERNAME
            new   1: grant XDB_WEBSERVICES to SCOTT
            
            Grant succeeded.
            
            SQL> grant XDB_WEBSERVICES_OVER_HTTP to &USERNAME
              2  /
            old   1: grant XDB_WEBSERVICES_OVER_HTTP to &USERNAME
            new   1: grant XDB_WEBSERVICES_OVER_HTTP to SCOTT
            
            Grant succeeded.
            
            SQL> grant XDB_WEBSERVICES_WITH_PUBLIC to &USERNAME
              2  /
            old   1: grant XDB_WEBSERVICES_WITH_PUBLIC to &USERNAME
            new   1: grant XDB_WEBSERVICES_WITH_PUBLIC to SCOTT
            
            Grant succeeded.
            
            SQL> connect &USERNAME/&PASSWORD
            Connected.
            SQL> --
            SQL> var url varchar2(700)
            SQL> --
            SQL> set long 10000
            SQL> --
            SQL> BEGIN
              2    :url :=   'http://&USERNAME:&PASSWORD@127.0.0.1:' || dbms_xdb.getHttpPort() || '/orawsv?wsdl';
              3  end;
              4  /
            old   2:   :url :=   'http://&USERNAME:&PASSWORD@127.0.0.1:' || dbms_xdb.getHttpPort() || '/orawsv?wsdl';
            new   2:   :url :=   'http://SCOTT:tiger@127.0.0.1:' || dbms_xdb.getHttpPort() || '/orawsv?wsdl';
            
            PL/SQL procedure successfully completed.
            
            SQL> print url
            
            URL
            --------------------------------------------------------------------------------
            http://SCOTT:tiger@127.0.0.1:80/orawsv?wsdl
            
            SQL> --
            SQL> set long 10000 pages 0
            SQL> --
            SQL> connect &USERNAME/&PASSWORD
            Connected.
            SQL> --
            SQL> var url varchar2(700)
            SQL> --
            SQL> set long 10000
            SQL> --
            SQL> BEGIN
              2    :url :=   'http://&USERNAME:&PASSWORD@127.0.0.1:' || dbms_xdb.getHttpPort() || '/orawsv?wsdl';
              3  end;
              4  /
            old   2:   :url :=   'http://&USERNAME:&PASSWORD@127.0.0.1:' || dbms_xdb.getHttpPort() || '/orawsv?wsdl';
            new   2:   :url :=   'http://SCOTT:tiger@127.0.0.1:' || dbms_xdb.getHttpPort() || '/orawsv?wsdl';
            
            PL/SQL procedure successfully completed.
            
            SQL> print url
            http://SCOTT:tiger@127.0.0.1:80/orawsv?wsdl
            
            SQL> --
            SQL> set long 10000 pages 0
            SQL> --
            SQL> select  httpuritype( :url ).getXML() from dual
              2  /
            <definitions name="orawsv" targetNamespace="http://xmlns.oracle.com/orawsv" xmln
            s="http://schemas.xmlsoap.org/wsdl/" xmlns:tns="http://xmlns.oracle.com/orawsv"
            xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:xsd="http://www.w3.org/
            2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schema
            Location="http://schemas.xmlsoap.org/wsdl/ http://schemas.xmlsoap.org/wsdl/">
              <types>
                <xsd:schema targetNamespace="http://xmlns.oracle.com/orawsv" elementFormDefa
            ult="qualified">
                  <xsd:element name="query">
                    <xsd:complexType>
                      <xsd:sequence>
                        <xsd:element name="query_text">
                          <xsd:complexType>
                            <xsd:simpleContent>
                              <xsd:extension base="xsd:string">
                                <xsd:attribute name="type">
                                  <xsd:simpleType>
                                    <xsd:restriction base="xsd:NMTOKEN">
                                      <xsd:enumeration value="SQL"/>
                                      <xsd:enumeration value="XQUERY"/>
                                    </xsd:restriction>
                                  </xsd:simpleType>
                                </xsd:attribute>
                              </xsd:extension>
                            </xsd:simpleContent>
                          </xsd:complexType>
                        </xsd:element>
                        <xsd:choice minOccurs="0" maxOccurs="unbounded">
                          <xsd:element name="bind">
                            <xsd:complexType>
                              <xsd:simpleContent>
                                <xsd:extension base="xsd:string">
                                  <xsd:attribute name="name" type="xsd:string"/>
                                </xsd:extension>
                              </xsd:simpleContent>
                            </xsd:complexType>
                          </xsd:element>
                          <xsd:element name="bindXML">
                            <xsd:complexType>
                              <xsd:sequence>
                                <xsd:any/>
                              </xsd:sequence>
                            </xsd:complexType>
                          </xsd:element>
                        </xsd:choice>
                        <xsd:element name="null_handling" minOccurs="0">
                          <xsd:simpleType>
                            <xsd:restriction base="xsd:NMTOKEN">
                              <xsd:enumeration value="DROP_NULLS"/>
                              <xsd:enumeration value="NULL_ATTR"/>
                              <xsd:enumeration value="EMPTY_TAG"/>
                            </xsd:restriction>
                          </xsd:simpleType>
                        </xsd:element>
                        <xsd:element name="max_rows" type="xsd:positiveInteger" minOccurs="0
            "/>
                        <xsd:element name="skip_rows" type="xsd:positiveInteger" minOccurs="
            0"/>
                        <xsd:element name="pretty_print" type="xsd:boolean" minOccurs="0"/>
                        <xsd:element name="indentation_width" type="xsd:positiveInteger" min
            Occurs="0"/>
                        <xsd:element name="rowset_tag" type="xsd:string" minOccurs="0"/>
                        <xsd:element name="row_tag" type="xsd:string" minOccurs="0"/>
                        <xsd:element name="item_tags_for_coll" type="xsd:boolean" minOccurs=
            "0"/>
                      </xsd:sequence>
                    </xsd:complexType>
                  </xsd:element>
                  <xsd:element name="queryOut">
                    <xsd:complexType>
                      <xsd:sequence>
                        <xsd:any/>
                      </xsd:sequence>
                    </xsd:complexType>
                  </xsd:element>
                </xsd:schema>
              </types>
              <message name="QueryInput">
                <part name="body" element="tns:query"/>
              </message>
              <message name="XMLOutput">
                <part name="body" element="tns:queryOut"/>
              </message>
              <portType name="ORAWSVPortType">
                <operation name="XMLFromQuery">
                  <input message="tns:QueryInput"/>
                  <output message="tns:XMLOutput"/>
                </operation>
              </portType>
              <binding name="ORAWSVBinding" type="tns:ORAWSVPortType">
                <soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/ht
            tp"/>
                <operation name="XMLFromQuery">
                  <soap:operation soapAction="http://127.0.0.1/orawsv"/>
                  <input>
                    <soap:body use="literal"/>
                  </input>
                  <output>
                    <soap:body use="literal"/>
                  </output>
                </operation>
              </binding>
              <service name="ORAWSVService">
                <documentation>Oracle Web Service</documentation>
                <port name="ORAWSVPort" binding="tns:ORAWSVBinding">
                  <soap:address location="http://127.0.0.1/orawsv"/>
                </port>
              </service>
            </definitions>
            
            
            SQL> create or replace procedure SQUARE ( parm in out number)
              2  as
              3  begin
              4    parm := parm * 2;
              5  end;
              6  /
            
            Procedure created.
            
            SQL> BEGIN
              2    :url :=   'http://&USERNAME:&PASSWORD@127.0.0.1:' || dbms_xdb.getHttpPort() || '/orawsv/SCOTT/SQUARE?wsdl';
              3  end;
              4  /
            old   2:   :url :=   'http://&USERNAME:&PASSWORD@127.0.0.1:' || dbms_xdb.getHttpPort() || '/orawsv/SCOTT/SQUARE?wsdl';
            new   2:   :url :=   'http://SCOTT:tiger@127.0.0.1:' || dbms_xdb.getHttpPort() || '/orawsv/SCOTT/SQUARE?wsdl';
            
            PL/SQL procedure successfully completed.
            
            SQL> print url
            http://SCOTT:tiger@127.0.0.1:80/orawsv/SCOTT/SQUARE?wsdl
            
            SQL> --
            SQL> set long 10000 pages 0
            SQL> --
            SQL> select  httpuritype( :url ).getXML() from dual
              2  /
            <definitions name="SQUARE" targetNamespace="http://xmlns.oracle.com/orawsv/SCOTT
            /SQUARE" xmlns="http://schemas.xmlsoap.org/wsdl/" xmlns:tns="http://xmlns.oracle
            .com/orawsv/SCOTT/SQUARE" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soa
            p="http://schemas.xmlsoap.org/wsdl/soap/">
              <types>
                <xsd:schema targetNamespace="http://xmlns.oracle.com/orawsv/SCOTT/SQUARE" el
            ementFormDefault="qualified">
                  <xsd:element name="SQUAREInput">
                    <xsd:complexType>
                      <xsd:sequence>
                        <xsd:element name="PARM-NUMBER-INOUT" type="xsd:double"/>
                      </xsd:sequence>
                    </xsd:complexType>
                  </xsd:element>
                  <xsd:element name="SQUAREOutput">
                    <xsd:complexType>
                      <xsd:sequence>
                        <xsd:element name="PARM" type="xsd:double"/>
                      </xsd:sequence>
                    </xsd:complexType>
                  </xsd:element>
                </xsd:schema>
              </types>
              <message name="SQUAREInputMessage">
                <part name="parameters" element="tns:SQUAREInput"/>
              </message>
              <message name="SQUAREOutputMessage">
                <part name="parameters" element="tns:SQUAREOutput"/>
              </message>
              <portType name="SQUAREPortType">
                <operation name="SQUARE">
                  <input message="tns:SQUAREInputMessage"/>
                  <output message="tns:SQUAREOutputMessage"/>
                </operation>
              </portType>
              <binding name="SQUAREBinding" type="tns:SQUAREPortType">
                <soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/ht
            tp"/>
                <operation name="SQUARE">
                  <soap:operation soapAction="SQUARE"/>
                  <input>
                    <soap:body parts="parameters" use="literal"/>
                  </input>
                  <output>
                    <soap:body parts="parameters" use="literal"/>
                  </output>
                </operation>
              </binding>
              <service name="SQUAREService">
                <documentation>Oracle Web Service</documentation>
                <port name="SQUAREPort" binding="tns:SQUAREBinding">
                  <soap:address location="http://127.0.0.1/orawsv/SCOTT/SQUARE"/>
                </port>
              </service>
            </definitions>
            
            
            SQL>
            • 3. Re: Testing Protocol Server  WSDL functionality
              654518
              Can I call web services via REST invocations -- the articles that referenced me to here implied SOAP only. Are there XSLT transforms I can call and store them within Oracle?
              • 4. Re: Testing Protocol Server  WSDL functionality
                Marco Gralike
                You can use XSLT transformations from within the database but also via using dburi calls (http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb15dbu.htm#i1027988)

                An old example is given in a 9.2 demo here: http://download.oracle.com/otndocs/tech/xml/xmldb/9.2.0.2.0/basicDemo.pdf

                look for

                http://localhost:8080/oradb/SCOTT/PURCHASEORDER/ROW/PurchaseOrder[Reference=“BLAKE-
                2002100912333601PDT.XML”]?contenttype=text/html?&transform=/home/SCOTT/poSource/xsl/po.xsl

                I have no idea if the source is still valid but this can be found on the main index page http://www.oracle.com/technology/tech/xml/xmldb/9.2.0.2.0/index.html