7 Replies Latest reply on Nov 9, 2009 5:57 AM by mdrake-Oracle

    Database Native Web Services : XMLTYPE parameter in PL/SQL with XMLSCHEMA?

    Andreas Dörfler
      Hi,

      I would like to build a function with a XMLTYPE parameter which is schema based, something like this:

      CREATE FUNCTION xy (p_xml XMLTYPE XMLSCHEMA 'http://myschema.com') ...

      At the moment I do the following inside the code:

                l_xml := p_xml.createSchemaBasedXML('http://myschema.com');
                l_xml.schemavalidate();

                /* validate XML against XSD */
                l_nbr := l_xml.isschemavalidated();

      The purpose why I would like to directly put the schema in the declaration is that I would like to use this function as a Oracle Native Web Service. The web service doesn't know the structure of the xml parameter as the schema is not knows in the declaration.

      Thanks and kind regards,
      Andreas

      Edited by: mdrake on Nov 8, 2009 9:55 PM
        • 1. Re: XMLTYPE parameter in PL/SQL with XMLSCHEMA?
          Marco Gralike
          You could try to build a solution using varchar2 or uritypes (http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb15dbu.htm#sthref1894, http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/t_dburi.htm#ARPLS800)
          • 2. Re: XMLTYPE parameter in PL/SQL with XMLSCHEMA?
            Andreas Dörfler
            Thanks Marco, I never heard of uritypes before, I will have a look.

            My main problem though is that someone who is reading the WSDL of my web service function doesn't know what is behind the parameter. The WSDL just says <xsd:complexType> <xsd:sequence> <*xsd:any*/>.

            Inside the database I have registered a schema for that but I don't know how I can tell Oracle to show this schema inside the WSDL.

            Andreas
            • 3. Re: XMLTYPE parameter in PL/SQL with XMLSCHEMA?
              mdrake-Oracle
              At the moment there is no way for the WSDL generated by Database Native Web Services to incorpirate this information. The problem is there is no way of associating an XML Schema with a PL/SQL variable, parameter or return value. It would require work by both the XML DB and PL/SQL group to achieve this. We will consider it as an enhancement request.

              The only workarounds I can think of would be to have a seperate (Hand Crafted) WSDL for this service. Of course this would have to be at some other URL than the expected one, so this may not be viable, or to provide your own get (expanded) WSDL service as part of the package that adds the schema information into the WSDL. The Web Service consumer would then need to get the basic WDSL from /orawsv/package?wdsl and then get the expanded WSDL by callling the appropraite method on the package..
              • 4. Re: XMLTYPE parameter in PL/SQL with XMLSCHEMA?
                Andreas Dörfler
                Thanks a lot, Marc. That was exactly the info I needed.

                I will see if I create a WSDL that I pass through the web service. For the moment I modify the WSDL by hand to integrate the XML schema and gave it manually to the application that uses the web service. Would be a nice enhancement if Oracle would handle this automatically though... :)
                • 5. Re: XMLTYPE parameter in PL/SQL with XMLSCHEMA?
                  mdrake-Oracle
                  Just for kicks here's the best I can come up with..
                  SQL> connect &USERNAME/&PASSWORD
                  Connected.
                  SQL> --
                  SQL> --
                  SQL> --
                  SQL> declare
                    2    XMLSCHEMA XMLTYPE := XMLTYPE(
                    3  '<!-- edited with XML Spy v4.0 U (http://www.xmlspy.com) by Mark (Drake) -->
                    4  <xs:schema targetNamespace="http://xmlns.example.com/xsd/purchaseOrder"
                    5             xmlns="http://xmlns.example.com/xsd/purchaseOrder"
                    6             xmlns:xs="http://www.w3.org/2001/XMLSchema"
                    7             xmlns:xdb="http://xmlns.oracle.com/xdb" version="1.0" xdb:storeVarrayAsTable="true">
                    8          <xs:element name="PurchaseOrder" type="PurchaseOrderType" xdb:defaultTable="PURCHASEORDER"/>
                    9          <xs:complexType name="PurchaseOrderType" xdb:SQLType="PURCHASEORDER_T">
                   10                  <xs:sequence>
                   11                          <xs:element name="Reference" type="ReferenceType" xdb:SQLName="REFERENCE"/>
                   12                          <xs:element name="Actions" type="ActionsType" xdb:SQLName="ACTIONS"/>
                   13                          <xs:element name="Reject" type="RejectionType" minOccurs="0" xdb:SQLName="REJECTION"/>
                   14                          <xs:element name="Requestor" type="RequestorType" xdb:SQLName="REQUESTOR"/>
                   15                          <xs:element name="User" type="UserType" xdb:SQLName="USERID"/>
                   16                          <xs:element name="CostCenter" type="CostCenterType" xdb:SQLName="COST_CENTER"/>
                   17                          <xs:element name="ShippingInstructions" type="ShippingInstructionsType" xdb:SQLName="SHIPPING_INSTRUCTIONS"/>
                   18                          <xs:element name="SpecialInstructions" type="SpecialInstructionsType" xdb:SQLName="SPECIAL_INSTRUCTIONS"/>
                   19                          <xs:element name="LineItems" type="LineItemsType" xdb:SQLName="LINEITEMS"/>
                   20                  </xs:sequence>
                   21          </xs:complexType>
                   22          <xs:complexType name="LineItemsType" xdb:SQLType="LINEITEMS_T">
                   23                  <xs:sequence>
                   24                          <xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded" xdb:SQLName="LINEITEM" xdb:SQLCollType="LINEITEM_V"/>
                   25                  </xs:sequence>
                   26          </xs:complexType>
                   27          <xs:complexType name="LineItemType" xdb:SQLType="LINEITEM_T">
                   28                  <xs:sequence>
                   29                          <xs:element name="Description" type="DescriptionType" xdb:SQLName="DESCRIPTION"/>
                   30                          <xs:element name="Part" type="PartType" xdb:SQLName="PART"/>
                   31                  </xs:sequence>
                   32                  <xs:attribute name="ItemNumber" type="xs:integer" xdb:SQLName="ITEMNUMBER" xdb:SQLType="NUMBER"/>
                   33          </xs:complexType>
                   34          <xs:complexType name="PartType" xdb:SQLType="PART_T">
                   35                  <xs:attribute name="Id" xdb:SQLName="PART_NUMBER" xdb:SQLType="VARCHAR2">
                   36                          <xs:simpleType>
                   37                                  <xs:restriction base="xs:string">
                   38                                          <xs:minLength value="10"/>
                   39                                          <xs:maxLength value="14"/>
                   40                                  </xs:restriction>
                   41                          </xs:simpleType>
                   42                  </xs:attribute>
                   43                  <xs:attribute name="Quantity" type="moneyType" xdb:SQLName="QUANTITY"/>
                   44                  <xs:attribute name="UnitPrice" type="quantityType" xdb:SQLName="UNITPRICE"/>
                   45          </xs:complexType>
                   46          <xs:simpleType name="ReferenceType">
                   47                  <xs:restriction base="xs:string">
                   48                          <xs:minLength value="18"/>
                   49                          <xs:maxLength value="30"/>
                   50                  </xs:restriction>
                   51          </xs:simpleType>
                   52          <xs:complexType name="ActionsType" xdb:SQLType="ACTIONS_T">
                   53                  <xs:sequence>
                   54                          <xs:element name="Action" maxOccurs="4" xdb:SQLName="ACTION" xdb:SQLCollType="ACTION_V">
                   55                                  <xs:complexType xdb:SQLType="ACTION_T">
                   56                                          <xs:sequence>
                   57                                                  <xs:element name="User" type="UserType" xdb:SQLName="ACTIONED_BY"/>
                   58                                                  <xs:element name="Date" type="DateType" minOccurs="0" xdb:SQLName="DATE_ACTIONED"/>
                   59                                          </xs:sequence>
                   60                                  </xs:complexType>
                   61                          </xs:element>
                   62                  </xs:sequence>
                   63          </xs:complexType>
                   64          <xs:complexType name="RejectionType" xdb:SQLType="REJECTION_T">
                   65                  <xs:all>
                   66                          <xs:element name="User" type="UserType" minOccurs="0" xdb:SQLName="REJECTED_BY"/>
                   67                          <xs:element name="Date" type="DateType" minOccurs="0" xdb:SQLName="DATE_REJECTED"/>
                   68                          <xs:element name="Comments" type="CommentsType" minOccurs="0" xdb:SQLName="REASON_REJECTED"/>
                   69                  </xs:all>
                   70          </xs:complexType>
                   71          <xs:complexType name="ShippingInstructionsType" xdb:SQLType="SHIPPING_INSTRUCTIONS_T">
                   72                  <xs:sequence>
                   73                          <xs:element name="name" type="NameType" minOccurs="0" xdb:SQLName="SHIP_TO_NAME"/>
                   74                          <xs:element name="address" type="AddressType" minOccurs="0" xdb:SQLName="SHIP_TO_ADDRESS"/>
                   75                          <xs:element name="telephone" type="TelephoneType" minOccurs="0" xdb:SQLName="SHIP_TO_PHONE"/>
                   76                  </xs:sequence>
                   77          </xs:complexType>
                   78          <xs:simpleType name="moneyType">
                   79                  <xs:restriction base="xs:decimal">
                   80                          <xs:fractionDigits value="2"/>
                   81                          <xs:totalDigits value="12"/>
                   82                  </xs:restriction>
                   83          </xs:simpleType>
                   84          <xs:simpleType name="quantityType">
                   85                  <xs:restriction base="xs:decimal">
                   86                          <xs:fractionDigits value="4"/>
                   87                          <xs:totalDigits value="8"/>
                   88                  </xs:restriction>
                   89          </xs:simpleType>
                   90          <xs:simpleType name="UserType">
                   91                  <xs:restriction base="xs:string">
                   92                          <xs:minLength value="1"/>
                   93                          <xs:maxLength value="10"/>
                   94                  </xs:restriction>
                   95          </xs:simpleType>
                   96          <xs:simpleType name="RequestorType">
                   97                  <xs:restriction base="xs:string">
                   98                          <xs:minLength value="0"/>
                   99                          <xs:maxLength value="128"/>
                  100                  </xs:restriction>
                  101          </xs:simpleType>
                  102          <xs:simpleType name="CostCenterType">
                  103                  <xs:restriction base="xs:string">
                  104                          <xs:minLength value="1"/>
                  105                          <xs:maxLength value="4"/>
                  106                  </xs:restriction>
                  107          </xs:simpleType>
                  108          <xs:simpleType name="VendorType">
                  109                  <xs:restriction base="xs:string">
                  110                          <xs:minLength value="0"/>
                  111                          <xs:maxLength value="20"/>
                  112                  </xs:restriction>
                  113          </xs:simpleType>
                  114          <xs:simpleType name="PurchaseOrderNumberType">
                  115                  <xs:restriction base="xs:integer"/>
                  116          </xs:simpleType>
                  117          <xs:simpleType name="SpecialInstructionsType">
                  118                  <xs:restriction base="xs:string">
                  119                          <xs:minLength value="0"/>
                  120                          <xs:maxLength value="2048"/>
                  121                  </xs:restriction>
                  122          </xs:simpleType>
                  123          <xs:simpleType name="NameType">
                  124                  <xs:restriction base="xs:string">
                  125                          <xs:minLength value="1"/>
                  126                          <xs:maxLength value="20"/>
                  127                  </xs:restriction>
                  128          </xs:simpleType>
                  129          <xs:simpleType name="AddressType">
                  130                  <xs:restriction base="xs:string">
                  131                          <xs:minLength value="1"/>
                  132                          <xs:maxLength value="256"/>
                  133                  </xs:restriction>
                  134          </xs:simpleType>
                  135          <xs:simpleType name="TelephoneType">
                  136                  <xs:restriction base="xs:string">
                  137                          <xs:minLength value="1"/>
                  138                          <xs:maxLength value="24"/>
                  139                  </xs:restriction>
                  140          </xs:simpleType>
                  141          <xs:simpleType name="DateType">
                  142                  <xs:restriction base="xs:date"/>
                  143          </xs:simpleType>
                  144          <xs:simpleType name="CommentsType">
                  145                  <xs:restriction base="xs:string">
                  146                          <xs:minLength value="1"/>
                  147                          <xs:maxLength value="2048"/>
                  148                  </xs:restriction>
                  149          </xs:simpleType>
                  150          <xs:simpleType name="DescriptionType">
                  151                  <xs:restriction base="xs:string">
                  152                          <xs:minLength value="1"/>
                  153                          <xs:maxLength value="256"/>
                  154                  </xs:restriction>
                  155          </xs:simpleType>
                  156  </xs:schema>');
                  157  begin
                  158          dbms_xmlschema.registerSchema( SCHEMAURL => '/xsd/purchaseOrder.xsd', SCHEMADOC => XMLSCHEMA);
                  159  end;
                  160  /
                  
                  PL/SQL procedure successfully completed.
                  
                  Elapsed: 00:00:01.94
                  SQL> VAR URL VARCHAR2(1024)
                  SQL> VAR RESULT CLOB;
                  SQL> --
                  SQL> create or replace package TESTPROC
                    2  as
                    3    function getPurchaseOrder(P_REFERENCE VARCHAR2) return XMLTYPE;
                    4    function expandWSDL return XMLTYPE;
                    5  end;
                    6  /
                  
                  Package created.
                  
                  Elapsed: 00:00:00.02
                  SQL> --
                  SQL> create or replace package body TESTPROC
                    2  as
                    3  --
                    4  function getPurchaseOrder(P_REFERENCE VARCHAR2)
                    5  return XMLType
                    6  as
                    7    V_RESULT XMLTYPE;
                    8  begin
                    9    select object_value
                   10      into V_RESULT
                   11      from PURCHASEORDER
                   12     where XMLEXISTS('$p/PurchaseOrder[Reference=$ref]' passing P_REFERENCE as "ref");
                   13    return V_RESULT;
                   14  end;
                   15  --
                   16  function getWSDL
                   17  return XMLType
                   18  as
                   19    V_WSDL_URL VARCHAR2(700) :=  'http://&USERNAME:&PASSWORD@localhost:' || dbms_xdb.getHttpPort() || '/orawsv/&USERNAME/TESTPROC/GETPURCHASEORDER';
                   20    V_WSDL     XMLTYPE;
                   21  begin
                   22          select httpuritype( V_WSDL_URL || '?wsdl' ).getXML()
                   23            into V_WSDL
                   24            from dual;
                   25          return V_WSDL;
                   26  end;
                   27  --
                   28  function expandWSDL
                   29  return XMLTYPE
                   30  as
                   31    V_INTERNAL_URL VARCHAR2(700);
                   32    V_EXTERNAL_URL VARCHAR2(700);
                   33    V_NAMESPACE       VARCHAR2(4000);
                   34    V_WSDL         XMLTYPE := getWSDL();
                   35    V_XSD          XMLTYPE;
                   36  begin
                   37          -- Need to provide real logic to determine which XSD goes with which parameter...
                   38
                   39    -- get the TARGETNAMESPACE for the import element (Not sure how this would work with a noNamespace Schema since we'd be in Chamelian Land)
                   40    select SCHEMA_URL,
                   41           XMLCAST
                   42           (
                   43             XMLQUERY
                   44             (
                   45               'declare namespace xsd = "http://www.w3.org/2001/XMLSchema"; (::)
                   46               $xsd/xsd:schema/@targetNamespace'
                   47               passing SCHEMA as "xsd" returning content
                   48             )
                   49             as VARCHAR2(4000)
                   50           )
                   51      into V_INTERNAL_URL, V_NAMESPACE
                   52            from USER_XML_SCHEMAS
                   53           where SCHEMA_URL = '/xsd/purchaseOrder.xsd';
                   54
                   55    V_EXTERNAL_URL := '/sys/schemas/&USERNAME' || V_INTERNAL_URL;
                   56
                   57    -- Add  <xsd:import namespace="http://xmlns.example.com/xsd/purchaseOrder" schemaLocation="http://localhost:80/sys/schemas/WSDLTEST/xsd/purchaseOrder.xsd
                  "/>
                   58
                   59    select insertXMLBefore
                   60           (
                   61              V_WSDL,
                   62              '//xsd:schema/child::*[1]',
                   63              xmlElement("xsd:import",xmlAttributes('http://www.w3.org/2001/XMLSchema' as "xmlns:xsd",V_NAMESPACE as "namespace",V_EXTERNAL_URL as "schemaLoc
                  ation")),
                   64              'xmlns:xsd="http://www.w3.org/2001/XMLSchema"'
                   65           )
                   66      into V_WSDL
                   67      from dual;
                   68
                   69    select updateXML
                   70           (
                   71             V_WSDL,
                   72             '//xsd:schema/xsd:element[@name="GETPURCHASEORDEROutput"]/xsd:complexType/xsd:sequence/xsd:element[@name="RETURN"]/xsd:complexType',
                   73             XMLElement
                   74             (
                   75               "xsd:complexType",
                   76               XMLAttributes('http://www.w3.org/2001/XMLSchema' as "xmlns:xsd"),
                   77               XMLElement
                   78               (
                   79                 "xsd:sequence",
                   80                 XMLElement
                   81                 (
                   82                    "xsd:element",
                   83                    xmlAttributes('http://xmlns.example.com/xsd/purchaseOrder' as "xmlns:po",'po:PurchaseOrder' as "ref")
                   84                 )
                   85               )
                   86             ),
                   87             'xmlns:xsd="http://www.w3.org/2001/XMLSchema"'
                   88           )
                   89      into V_WSDL
                   90      from dual;
                   91
                   92    return V_WSDL;
                   93  end;
                   94  --
                   95  end;
                   96  /
                  old  19:   V_WSDL_URL VARCHAR2(700) :=  'http://&USERNAME:&PASSWORD@localhost:' || dbms_xdb.getHttpPort() || '/orawsv/&USERNAME/TESTPROC/GETPURCHASEORDER';
                  new  19:   V_WSDL_URL VARCHAR2(700) :=  'http://WSDLTEST:WSDLTEST@localhost:' || dbms_xdb.getHttpPort() || '/orawsv/WSDLTEST/TESTPROC/GETPURCHASEORDER';
                  old  55:   V_EXTERNAL_URL := '/sys/schemas/&USERNAME' || V_INTERNAL_URL;
                  new  55:   V_EXTERNAL_URL := '/sys/schemas/WSDLTEST' || V_INTERNAL_URL;
                  
                  Package body created.
                  
                  Elapsed: 00:00:00.05
                  SQL> show errors
                  No errors.
                  SQL> --
                  SQL> BEGIN
                    2    :URL := 'http://&USERNAME:&PASSWORD@localhost:' || dbms_xdb.getHttpPort() || '/orawsv/&USERNAME/TESTPROC';
                    3  end;
                    4  /
                  old   2:   :URL := 'http://&USERNAME:&PASSWORD@localhost:' || dbms_xdb.getHttpPort() || '/orawsv/&USERNAME/TESTPROC';
                  new   2:   :URL := 'http://WSDLTEST:WSDLTEST@localhost:' || dbms_xdb.getHttpPort() || '/orawsv/WSDLTEST/TESTPROC';
                  
                  PL/SQL procedure successfully completed.
                  
                  Elapsed: 00:00:00.00
                  SQL> print url
                  
                  URL
                  --------------------------------------------------------------------------------
                  http://WSDLTEST:WSDLTEST@localhost:80/orawsv/WSDLTEST/TESTPROC
                  
                  SQL> --
                  SQL> set long 10000000 pages 0 lines 160
                  SQL> --
                  SQL> select httpuritype( :URL || '?wsdl' ).getXML()
                    2    from dual
                    3  /
                  <definitions name="TESTPROC" targetNamespace="http://xmlns.oracle.com/orawsv/WSDLTEST/TESTPROC" xmlns="http://schemas.xmlsoap.org/wsdl/" xmlns:tns="http://xmlns
                  
                  .oracle.com/orawsv/WSDLTEST/TESTPROC" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/">
                    <types>
                      <xsd:schema targetNamespace="http://xmlns.oracle.com/orawsv/WSDLTEST/TESTPROC" elementFormDefault="qualified">
                        <xsd:element name="CXMLTYPE-GETPURCHASEORDERInput">
                          <xsd:complexType>
                            <xsd:sequence>
                              <xsd:element name="P_REFERENCE-VARCHAR2-IN" type="xsd:string"/>
                            </xsd:sequence>
                          </xsd:complexType>
                        </xsd:element>
                        <xsd:element name="GETPURCHASEORDEROutput">
                          <xsd:complexType>
                            <xsd:sequence>
                              <xsd:element name="RETURN">
                                <xsd:complexType>
                                  <xsd:sequence>
                                    <xsd:any/>
                                  </xsd:sequence>
                                </xsd:complexType>
                              </xsd:element>
                            </xsd:sequence>
                          </xsd:complexType>
                        </xsd:element>
                        <xsd:element name="CXMLTYPE-EXPANDWSDLInput">
                          <xsd:complexType>
                            <xsd:sequence>
                              </xsd:sequence>
                          </xsd:complexType>
                        </xsd:element>
                        <xsd:element name="EXPANDWSDLOutput">
                          <xsd:complexType>
                            <xsd:sequence>
                              <xsd:element name="RETURN">
                                <xsd:complexType>
                                  <xsd:sequence>
                                    <xsd:any/>
                                  </xsd:sequence>
                                </xsd:complexType>
                              </xsd:element>
                            </xsd:sequence>
                          </xsd:complexType>
                        </xsd:element>
                      </xsd:schema>
                    </types>
                    <message name="GETPURCHASEORDERInputMessage">
                      <part name="parameters" element="tns:CXMLTYPE-GETPURCHASEORDERInput"/>
                    </message>
                    <message name="GETPURCHASEORDEROutputMessage">
                      <part name="parameters" element="tns:GETPURCHASEORDEROutput"/>
                    </message>
                    <message name="EXPANDWSDLInputMessage">
                      <part name="parameters" element="tns:CXMLTYPE-EXPANDWSDLInput"/>
                    </message>
                    <message name="EXPANDWSDLOutputMessage">
                      <part name="parameters" element="tns:EXPANDWSDLOutput"/>
                    </message>
                    <portType name="TESTPROCPortType">
                      <operation name="GETPURCHASEORDER">
                        <input message="tns:GETPURCHASEORDERInputMessage"/>
                        <output message="tns:GETPURCHASEORDEROutputMessage"/>
                      </operation>
                      <operation name="EXPANDWSDL">
                        <input message="tns:EXPANDWSDLInputMessage"/>
                        <output message="tns:EXPANDWSDLOutputMessage"/>
                      </operation>
                    </portType>
                    <binding name="TESTPROCBinding" type="tns:TESTPROCPortType">
                      <soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>
                      <operation name="GETPURCHASEORDER">
                        <soap:operation soapAction="GETPURCHASEORDER"/>
                        <input>
                          <soap:body parts="parameters" use="literal"/>
                        </input>
                        <output>
                          <soap:body parts="parameters" use="literal"/>
                        </output>
                      </operation>
                      <operation name="EXPANDWSDL">
                        <soap:operation soapAction="EXPANDWSDL"/>
                        <input>
                          <soap:body parts="parameters" use="literal"/>
                        </input>
                        <output>
                          <soap:body parts="parameters" use="literal"/>
                        </output>
                      </operation>
                    </binding>
                    <service name="TESTPROCService">
                      <documentation>Oracle Web Service</documentation>
                      <port name="TESTPROCPort" binding="tns:TESTPROCBinding">
                        <soap:address location="http://localhost:80/orawsv/WSDLTEST/TESTPROC"/>
                      </port>
                    </service>
                  </definitions>
                  
                  
                  Elapsed: 00:00:00.33
                  SQL> select TESTPROC.expandWSDL()
                    2    from dual
                    3  /
                  <definitions name="GETPURCHASEORDER" targetNamespace="http://xmlns.oracle.com/orawsv/WSDLTEST/TESTPROC/GETPURCHASEORDER" xmlns="http://schemas.xmlsoap.org/wsdl/
                  
                  " xmlns:tns="http://xmlns.oracle.com/orawsv/WSDLTEST/TESTPROC/GETPURCHASEORDER" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.
                  
                  org/wsdl/soap/">
                    <types>
                      <xsd:schema targetNamespace="http://xmlns.oracle.com/orawsv/WSDLTEST/TESTPROC/GETPURCHASEORDER" elementFormDefault="qualified">
                        <xsd:import xmlns:xsd="http://www.w3.org/2001/XMLSchema" namespace="http://xmlns.example.com/xsd/purchaseOrder" schemaLocation="/sys/schemas/WSDLTEST/xsd/
                  
                  purchaseOrder.xsd"/>
                        <xsd:element name="CXMLTYPE-GETPURCHASEORDERInput">
                          <xsd:complexType>
                            <xsd:sequence>
                              <xsd:element name="P_REFERENCE-VARCHAR2-IN" type="xsd:string"/>
                            </xsd:sequence>
                          </xsd:complexType>
                        </xsd:element>
                        <xsd:element name="GETPURCHASEORDEROutput">
                          <xsd:complexType>
                            <xsd:sequence>
                              <xsd:element name="RETURN">
                                <xsd:complexType xmlns:xsd="http://www.w3.org/2001/XMLSchema">
                                  <xsd:sequence>
                                    <xsd:element xmlns:po="http://xmlns.example.com/xsd/purchaseOrder" ref="po:PurchaseOrder"/>
                                  </xsd:sequence>
                                </xsd:complexType>
                              </xsd:element>
                            </xsd:sequence>
                          </xsd:complexType>
                        </xsd:element>
                      </xsd:schema>
                    </types>
                    <message name="GETPURCHASEORDERInputMessage">
                      <part name="parameters" element="tns:CXMLTYPE-GETPURCHASEORDERInput"/>
                    </message>
                    <message name="GETPURCHASEORDEROutputMessage">
                      <part name="parameters" element="tns:GETPURCHASEORDEROutput"/>
                    </message>
                    <portType name="GETPURCHASEORDERPortType">
                      <operation name="GETPURCHASEORDER">
                        <input message="tns:GETPURCHASEORDERInputMessage"/>
                        <output message="tns:GETPURCHASEORDEROutputMessage"/>
                      </operation>
                    </portType>
                    <binding name="GETPURCHASEORDERBinding" type="tns:GETPURCHASEORDERPortType">
                      <soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>
                      <operation name="GETPURCHASEORDER">
                        <soap:operation soapAction="GETPURCHASEORDER"/>
                        <input>
                          <soap:body parts="parameters" use="literal"/>
                        </input>
                        <output>
                          <soap:body parts="parameters" use="literal"/>
                        </output>
                      </operation>
                    </binding>
                    <service name="GETPURCHASEORDERService">
                      <documentation>Oracle Web Service</documentation>
                      <port name="GETPURCHASEORDERPort" binding="tns:GETPURCHASEORDERBinding">
                        <soap:address location="http://localhost:80/orawsv/WSDLTEST/TESTPROC/GETPURCHASEORDER"/>
                      </port>
                    </service>
                  </definitions>
                  
                  
                  Elapsed: 00:00:00.11
                  SQL> BEGIN
                    2    :URL := 'http://&USERNAME:&PASSWORD@localhost:' || dbms_xdb.getHttpPort() || '/orawsv/&USERNAME/TESTPROC/EXPANDWSDL';
                    3  end;
                    4  /
                  old   2:   :URL := 'http://&USERNAME:&PASSWORD@localhost:' || dbms_xdb.getHttpPort() || '/orawsv/&USERNAME/TESTPROC/EXPANDWSDL';
                  new   2:   :URL := 'http://WSDLTEST:WSDLTEST@localhost:' || dbms_xdb.getHttpPort() || '/orawsv/WSDLTEST/TESTPROC/EXPANDWSDL';
                  
                  PL/SQL procedure successfully completed.
                  
                  Elapsed: 00:00:00.00
                  SQL> print url
                  http://WSDLTEST:WSDLTEST@localhost:80/orawsv/WSDLTEST/TESTPROC/EXPANDWSDL
                  
                  SQL> --
                  SQL> set serveroutput on
                  SQL> --
                  SQL> VAR WSDL_TEXT CLOB
                  SQL> --
                  SQL> DECLARE
                    2    V_SOAP_REQUEST      XMLTYPE := XMLTYPE(
                    3  '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
                    4                      xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
                    5                      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                    6                      xmlns:xsd="http://www.w3.org/2001/XMLSchema">
                    7          <SOAP-ENV:Body>
                    8                  <m:CXMLTYPE-EXPANDWSDLInput xmlns:m="http://xmlns.oracle.com/orawsv/WSDLTEST/TESTPROC"/>
                    9          </SOAP-ENV:Body>
                   10  </SOAP-ENV:Envelope>');
                   11    V_SOAP_REQUEST_TEXT CLOB := V_SOAP_REQUEST.getClobVal();
                   12    V_REQUEST           UTL_HTTP.REQ;
                   13    V_RESPONSE          UTL_HTTP.RESP;
                   14    V_BUFFER            VARCHAR2(1024);
                   15    V_RESPONSE_TEXT     CLOB;
                   16    V_RESPONSE_XML      XMLTYPE;
                   17    V_WSDL              XMLTYPE;
                   18  BEGIN
                   19          DBMS_LOB.CREATETEMPORARY(V_RESPONSE_TEXT, TRUE);
                   20
                   21    begin
                   22      V_REQUEST := UTL_HTTP.BEGIN_REQUEST(URL => :URL, METHOD => 'POST');
                   23      UTL_HTTP.SET_HEADER(V_REQUEST, 'User-Agent', 'Mozilla/4.0');
                   24      V_REQUEST.METHOD := 'POST';
                   25      UTL_HTTP.SET_HEADER (R => V_REQUEST, NAME => 'Content-Length', VALUE => DBMS_LOB.GETLENGTH(V_SOAP_REQUEST_TEXT));
                   26      UTL_HTTP.WRITE_TEXT (R => V_REQUEST, DATA => V_SOAP_REQUEST_TEXT);
                   27      V_RESPONSE := UTL_HTTP.GET_RESPONSE(V_REQUEST);
                   28       LOOP
                   29        UTL_HTTP.READ_LINE(V_RESPONSE, V_BUFFER, TRUE);
                   30        DBMS_LOB.WRITEAPPEND(V_RESPONSE_TEXT,LENGTH(V_BUFFER),V_BUFFER);
                   31       END LOOP;
                   32       UTL_HTTP.END_RESPONSE(V_RESPONSE);
                   33    EXCEPTION
                   34      WHEN UTL_HTTP.END_OF_BODY THEN
                   35        UTL_HTTP.END_RESPONSE(V_RESPONSE);
                   36    END;
                   37
                   38    V_RESPONSE_XML := XMLTYPE(V_RESPONSE_TEXT);
                   39
                   40    select XMLQUERY
                   41           (
                   42             'declare namespace SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"; (::)
                   43              declare namespace SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"; (::)
                   44              declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance"; (::)
                   45              declare namespace xsd="http://www.w3.org/2001/XMLSchema"; (::)
                   46              declare namespace m="http://xmlns.oracle.com/orawsv/WSDLTEST/TESTPROC"; (::)
                   47              $resp/SOAP-ENV:Envelope/SOAP-ENV:Body/m:EXPANDWSDLOutput/m:RETURN/*'
                   48              passing V_RESPONSE_XML as "resp" returning content
                   49           )
                   50      into V_WSDL
                   51      from DUAL;
                   52
                   53    select V_WSDL.getClobVal()
                   54      into :WSDL_TEXT
                   55      from dual;
                   56
                   57          DBMS_LOB.FREETEMPORARY(V_RESPONSE_TEXT);
                   58
                   59  END;
                   60  /
                  
                  PL/SQL procedure successfully completed.
                  
                  Elapsed: 00:00:00.10
                  SQL> --
                  SQL> set pages 0 lines 160 long 10000
                  SQL> column WSDL format A160
                  SQL> --
                  SQL> select xmlserialize(document XMLTYPE(:WSDL_TEXT) as CLOB indent size=2) WSDL
                    2    from dual
                    3  /
                  <definitions name="GETPURCHASEORDER" targetNamespace="http://xmlns.oracle.com/orawsv/WSDLTEST/TESTPROC/GETPURCHASEORDER" xmlns="http://schemas.xmlsoap.org/wsdl/
                  
                  " xmlns:tns="http://xmlns.oracle.com/orawsv/WSDLTEST/TESTPROC/GETPURCHASEORDER" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.
                  
                  org/wsdl/soap/">
                    <types>
                      <xsd:schema targetNamespace="http://xmlns.oracle.com/orawsv/WSDLTEST/TESTPROC/GETPURCHASEORDER" elementFormDefault="qualified">
                        <xsd:import xmlns:xsd="http://www.w3.org/2001/XMLSchema" namespace="http://xmlns.example.com/xsd/purchaseOrder" schemaLocation="/sys/schemas/WSDLTEST/xsd/
                  
                  purchaseOrder.xsd"/>
                        <xsd:element name="CXMLTYPE-GETPURCHASEORDERInput">
                          <xsd:complexType>
                            <xsd:sequence>
                              <xsd:element name="P_REFERENCE-VARCHAR2-IN" type="xsd:string"/>
                            </xsd:sequence>
                          </xsd:complexType>
                        </xsd:element>
                        <xsd:element name="GETPURCHASEORDEROutput">
                          <xsd:complexType>
                            <xsd:sequence>
                              <xsd:element name="RETURN">
                                <xsd:complexType xmlns:xsd="http://www.w3.org/2001/XMLSchema">
                                  <xsd:sequence>
                                    <xsd:element xmlns:po="http://xmlns.example.com/xsd/purchaseOrder" ref="po:PurchaseOrder"/>
                                  </xsd:sequence>
                                </xsd:complexType>
                              </xsd:element>
                            </xsd:sequence>
                          </xsd:complexType>
                        </xsd:element>
                      </xsd:schema>
                    </types>
                    <message name="GETPURCHASEORDERInputMessage">
                      <part name="parameters" element="tns:CXMLTYPE-GETPURCHASEORDERInput"/>
                    </message>
                    <message name="GETPURCHASEORDEROutputMessage">
                      <part name="parameters" element="tns:GETPURCHASEORDEROutput"/>
                    </message>
                    <portType name="GETPURCHASEORDERPortType">
                      <operation name="GETPURCHASEORDER">
                        <input message="tns:GETPURCHASEORDERInputMessage"/>
                        <output message="tns:GETPURCHASEORDEROutputMessage"/>
                      </operation>
                    </portType>
                    <binding name="GETPURCHASEORDERBinding" type="tns:GETPURCHASEORDERPortType">
                      <soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>
                      <operation name="GETPURCHASEORDER">
                        <soap:operation soapAction="GETPURCHASEORDER"/>
                        <input>
                          <soap:body parts="parameters" use="literal"/>
                        </input>
                        <output>
                          <soap:body parts="parameters" use="literal"/>
                        </output>
                      </operation>
                    </binding>
                    <service name="GETPURCHASEORDERService">
                      <documentation>Oracle Web Service</documentation>
                      <port name="GETPURCHASEORDERPort" binding="tns:GETPURCHASEORDERBinding">
                        <soap:address location="http://localhost:80/orawsv/WSDLTEST/TESTPROC/GETPURCHASEORDER"/>
                      </port>
                    </service>
                  </definitions>
                  
                  
                  Elapsed: 00:00:00.03
                  SQL> exit
                  Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                  With the Partitioning, OLAP, Data Mining and Real Application Testing options
                  
                  C:\xdb\examples\DBNWS>