2 Replies Latest reply on Dec 7, 2012 1:52 AM by 978277

    PL/SQL native web services starting from an XSD ?

    978277
    978277 Dec 6, 2012 3:27 AM
      I'm trying to setup an Oracle Native web service in PL/SQL under Oracle 11.2.0.2.0 running on Red Hat 5 Linux 64-bit.

      We already have native PL/SQL web services running successfully (the database servlet works fine) from a previous project we did, however that project used the following approach:

      1. Start off without an XSD
      2. Hand-code the SQL scripts to create all the Oracle types needed to pass in the XML via web services
      3. Now write the PL/SQL package to use the TYPE for what will be the top-level XML element
      4. Call the native webservices servlet to find out what the WSDL is.

      What I'm trying to do is write a PL/SQL web service that accepts a reasonably-complex XML document as input, and passes back a different XML document as a response. Our new "standard approach" is to use native web services for this.

      So unlike the earlier approach above, I'm trying to start with an XSD instead, as follows:

      1. Write the XSD using Altova XMLSpy.
      2. Edit the XSD to add Oracle XDB extensions such as xdb:SQLType and xdb:SQLName, eg: to control the naming of the types (if I don't do this, RegisterSchema() [below] will generate its own random unique names)
      2. Load it into the Oracle DB using DBMS_XMLSCHEMA.registerSchema (), with gentypes=>TRUE
      3. Create the new PL/SQL package to use the TYPE for the top-level element.
      4. Check the service is OK by retrieving the WSDL back from the database using Internet Explorer

      You'd think this would work reasonably intuitively (I've been coding PL/SQL for over 15 years), but instead it's been very difficult to actually get working. The Oracle TYPEs generated by RegisterSchema() seem to make Oracle webservices very unhappy: even though the XSD loads OK and the TYPEs get created, and the package compiles fine, the WSDL that gets returned by Oracle for this new package is actually gibberish (it's not even syntactically correct) and causes Internet Explorer and SoapUI to complain loudly.

      I've done the usual Googling, but cannot seem to find any articles describing anyone creating a native PL/SQL web service from an XSD that has actually been loaded into the database. Most examples seem to be someone trying to expose an existing stored package (one that has very basic in/out parameters) as a web service. We can do this already no problems.


      Any help would be hugely appreciated..I'm on the verge of giving up and hand-coding all the oracle types manually to get it working.

      Regards
        • 1. Re: PL/SQL native web services starting from an XSD ?
          odie_63
          odie_63 Dec 6, 2012 5:42 AM (in response to 978277)
          Hi,

          Seems that you're following the correct approach.
          Please post a simple test case showing the issue.

          What error(s) are you getting?
          • 2. Re: PL/SQL native web services starting from an XSD ?
            978277
            978277 Dec 7, 2012 1:52 AM (in response to odie_63)
            Hi thanks for your fast reply.

            I've put together a test case below:

            Declare
            lv_schema_name VARCHAR2(300):= 'testcase.xsd';

            lv_schema_doc VARCHAR(32767):='<?xml version="1.0" encoding="UTF-8"?>
            <schema xmlns="http://www.w3.org/2001/XMLSchema"
            xmlns:xdb="http://xmlns.oracle.com/xdb"
            targetNamespace="testcase.xsd"
            elementFormDefault="qualified" version="1.0"
            xdb:mapStringToNCHAR="false" xdb:storeVarrayAsTable="true">
            <element name="root_element" xdb:SQLName="ROOT_ELEMENT" xdb:SQLType="TESTCASE_ROOT_ELEMENT_T">
            <complexType xdb:maintainDOM="false">
            <sequence>
            <element name="Loyalty_Status" xdb:SQLName="LOYALTY_STATUS">
            <simpleType>
            <restriction base="string">
            <enumeration value="immediate"/>
            <enumeration value="wait"/>
                                     </restriction>
                                </simpleType>
                           </element>
                           </sequence>
                           </complexType>
                      </element>
            </schema>
            ';

            exXSDDoesNotExist EXCEPTION;
            PRAGMA EXCEPTION_INIT (exXSDDoesNotExist, -31000);

            begin
            BEGIN
            DBMS_XMLSCHEMA.deleteSchema(
            schemaURL => lv_schema_name
            , delete_option => DBMS_XMLSCHEMA.DELETE_CASCADE
            );
            EXCEPTION
            WHEN exXSDDoesNotExist THEN
            NULL;
            END;

            DBMS_XMLSCHEMA.registerSchema(
            schemaurl=> lv_schema_name,
            schemaDoc => lv_schema_doc,
            local => True,
            genTypes => true,
            genbean => FALSE,
            gentables => FALSE
            );

            end;
            /



            create or replace package DBPK_WS_TEST is

            PROCEDURE test1 (pi_input in testcase_root_element_t);

            end DBPK_WS_TEST;
            /

            create or replace package body DBPK_WS_TEST is

            PROCEDURE test1 (pi_input in testcase_root_element_t)
            IS
            BEGIN
            NULL;
            END test1;

            end DBPK_WS_TEST;
            /


            Now if I pull out the WSDL using URL http://myserver:myport/orawsv/myschema/DBPK_WS_TEST/TEST1?wsdl

            I get this:

            <definitions name="TEST1"
            targetNamespace="http://xmlns.oracle.com/orawsv/ADMIN/DBPK_WS_TEST/TEST1"
            xmlns="http://schemas.xmlsoap.org/wsdl/"
            xmlns:tns="http://xmlns.oracle.com/orawsv/ADMIN/DBPK_WS_TEST/TEST1"
            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/ADMIN/DBPK_WS_TEST/TEST1"
            elementFormDefault="qualified">
            <xsd:element name="TEST1Input">
            <xsd:complexType>
            <xsd:sequence>
            <xsd:element name="PI_INPUT-TESTCASE_ROOT_ELEMENT_T-CIN" type="tns:TESTCASE_ROOT_ELEMENT_TType"/>
            </xsd:sequence>
            </xsd:complexType>
            </xsd:element>


            <xsd:element name="TEST1Output">
            <xsd:complexType>
            <xsd:sequence>
            </xsd:sequence>
            </xsd:complexType>
            </xsd:element>
            <xsd:complexType name="TESTCASE_ROOT_ELEMENT_TType">
            <xsd:sequence>
            <xsd:element name="TESTCASE_ROOT_ELEMENT_T">
            <xsd:complexType>
            <xsd:sequence>
            <xsd:element name="LOYALTY_STATUS" type="XDB.tns:XDB$ENUM_T_IntType"/>
            </xsd:sequence>
            </xsd:complexType>
            </xsd:element>
            </xsd:sequence>
            </xsd:complexType>
            <xsd:complexType name="XDB.XDB$ENUM_T_IntType">
            <xsd:sequence>
            <xsd:element name="VALUE" type="xsd:hexBinary"/>
            </xsd:sequence>
            </xsd:complexType>
            </xsd:schema>
            </types>

            <message name="TEST1InputMessage">
            <part name="parameters" element="tns:TEST1Input"/>
            </message>

            <message name="TEST1OutputMessage">
            <part name="parameters" element="tns:TEST1Output"/>
            </message>

            <portType name="TEST1PortType">
            <operation name="TEST1">
            <input message="tns:TEST1InputMessage"/>
            <output message="tns:TEST1OutputMessage"/>
            </operation>
            </portType>

            <binding name="TEST1Binding"
            type="tns:TEST1PortType">
            <soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>
            <operation name="TEST1">
            <soap:operation
            soapAction="TEST1"/>
            <input>
            <soap:body parts="parameters" use="literal"/>
            </input>
            <output>
            <soap:body parts="parameters" use="literal"/>
            </output>
            </operation>
            </binding>

            <service name="TEST1Service">
            <documentation>Oracle Web Service</documentation>
            <port name="TEST1Port" binding="tns:TEST1Binding">
            <soap:address
            location="http://sxlppdb09:8310/orawsv/ADMIN/DBPK_WS_TEST/TEST1"/>
            </port>
            </service>

            </definitions>


            The problem is with XDB.XDB$ENUM_T_IntType

            If I try and load this WSDL in something such as SOAPUI, I get the following:

            Source: http://myserver:myport/orawsv/myschema/DBPK_WS_TEST/TEST1?wsdl Error: The value 'XDB.XDB$ENUM_T_IntType' is an invalid name.


            Regards