Skip to main content

Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.

Jump to: navigation, search

Difference between revisions of "EclipseLink/Examples/DBWS/DBWSOverloadStoredProcedure"

 
(7 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
<css>
 
<css>
−
   .source-sql {padding:4px;}
+
  .source-plsql {padding:4px;border:1px solid black;}
 +
   .source-sql {padding:4px;border:1px solid black;}
 
   .source-java5 {padding:4px;border:1px solid black;}
 
   .source-java5 {padding:4px;border:1px solid black;}
 
   .source-xml {padding:4px;border:1px solid black;}
 
   .source-xml {padding:4px;border:1px solid black;}
Line 7: Line 8:
 
__NOTOC__  
 
__NOTOC__  
  
−
== EclipseLink DBWS Service based on an overloaded Stored Procedure ==
+
<font color="red"><i>{NB - this capability is available starting in version 2.3 of DBWS}</i></font>
−
The use-case for this example is the creation of a Web service that exposes a Stored Procedure (or multiple procedures).
+
−
From the metadata for a Stored Procedure, it is not possible to determine the structure of the returned data. Therefore,
+
−
the Simple XML Format schema is used. The EclipseLink DBWS runtime produces an XML document that is simple and 'human-readable'.
+
−
Any combination of IN, OUT and IN OUT arguments are supported; in addition, procedures in packages that are overloaded -
+
−
same name, different parameters – are supported.
+
  
−
The following stored procedures will be used for this example: <source lang="sql" enclose="div">
+
== EclipseLink DBWS Service based on an overloaded PL/SQL Stored Procedure ==
−
CREATE OR REPLACE PROCEDURE P1(SIMPLARRAY IN TBL1, FOO IN VARCHAR2) AS
+
The use-case for this example is the creation of a Web service that exposes multiple PL/SQL Stored Procedures. Normally, the user would specify in the DBWS builder file which procedures will make up the Web Service. However, in this scenario, the user can specify a single procedure name and leverage Oracle's support for ''overloading'' (where a single procedure can have different parameters specified):
 +
<source lang="plsql" enclose="div">
 +
CREATE PROCEDURE P(SIMPLARRAY IN TBL1, FOO IN VARCHAR2) AS
 
BEGIN
 
BEGIN
−
   // do something
+
   -- 2 arguments SIMPLARRAY and FOO
−
END P1;
+
END P;
−
 
+
CREATE PROCEDURE P(SIMPLARRAY IN TBL1, FOO IN VARCHAR2, BAR IN VARCHAR2) AS
−
CREATE OR REPLACE PROCEDURE P1(SIMPLARRAY IN TBL1, FOO IN VARCHAR2, BAR IN VARCHAR2) AS
+
 
BEGIN
 
BEGIN
−
   // do something
+
   -- (same name 'P') 3 arguments SIMPLARRAY, FOO and BAR
−
END P1;
+
END P;
 
</source>
 
</source>
 +
Any combination of IN, OUT and IN OUT arguments are supported
 +
 +
Type <code>TBL1</code> is defined in PL/SQL Package <code>SOMEPACKAGE</code> as follows:
 +
<source lang="plsql" enclose="div">
 +
CREATE OR REPLACE PACKAGE SOMEPACKAGE AS
 +
  TYPE TBL1 IS TABLE OF VARCHAR2(111) INDEX BY BINARY_INTEGER;
 +
  PROCEDURE P(SIMPLARRAY IN TBL1, FOO IN VARCHAR2);
 +
  PROCEDURE P(SIMPLARRAY IN TBL1, FOO IN VARCHAR2, BAR IN VARCHAR2);
 +
END SOMEPACKAGE;
 +
</source>
  
 
The <tt>DBWSBuilder</tt> utility requires a DBWS configuration file as input.
 
The <tt>DBWSBuilder</tt> utility requires a DBWS configuration file as input.
Line 34: Line 41:
 
     ... database properties
 
     ... database properties
 
   </properties>
 
   </properties>
−
   <procedure
+
   <plsql-procedure
−
     name="overloadedProcedureTest"
+
     name="overloadedProcedure"
−
     procedurePattern="P1"
+
     catalogPattern="SOMEPACKAGE"
−
     isSimpleXMLFormat="true"
+
     procedurePattern="P"
 
   />
 
   />
 
</dbws-builder>
 
</dbws-builder>
Line 50: Line 57:
 
* <tt>-packageAs</tt> specifies the platform on which the web service will be deployed
 
* <tt>-packageAs</tt> specifies the platform on which the web service will be deployed
  
−
The generated <tt><b>eclipselink-dbws-schema.xsd</b></tt> file is the schema for the Simple XML format:
+
When generating queries and the WSDL when overloaded procedures are present, an index is used to uniquely identify each procedure.  The index starts at 1, and incremented for each overloaded procedure.  The generated <tt><b>eclipselink-dbws.wsdl</b></tt> for this example follows.  :
 
<source lang="xml" enclose="div">
 
<source lang="xml" enclose="div">
−
<?xml version="1.0" encoding="UTF-8"?>
+
<wsdl:definitions
−
<xsd:schema
+
    name="plsqloverloadService"
−
  xmlns:xsd="http://www.w3.org/2001/XMLSchema"
+
    targetNamespace="urn:plsqloverloadService"
−
  >
+
    xmlns:ns1="urn:plsqloverload"
−
  <xsd:complexType name="simple-xml-format">
+
    xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/"
−
    <xsd:sequence>
+
    xmlns:tns="urn:plsqloverloadService"
−
      <xsd:any minOccurs="0"/>
+
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
−
    </xsd:sequence>
+
    xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/"
−
  </xsd:complexType>
+
    >
−
</xsd:schema>
+
    <wsdl:types>
 +
        <xsd:schema elementFormDefault="qualified" targetNamespace="urn:plsqloverloadService" xmlns:tns="urn:plsqloverloadService"
 +
            xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 +
            <xsd:import namespace="urn:plsqloverload" schemaLocation="eclipselink-dbws-schema.xsd"/>
 +
            <xsd:complexType name="p1ResponseType">
 +
                <xsd:sequence>
 +
                    <xsd:element name="result">
 +
                        <xsd:complexType><xsd:sequence><xsd:any/></xsd:sequence></xsd:complexType>
 +
                    </xsd:element>
 +
                </xsd:sequence>
 +
            </xsd:complexType>
 +
            <xsd:complexType name="p1RequestType">
 +
                <xsd:sequence>
 +
                    <xsd:element name="SIMPLARRAY" type="ns1:SOMEPACKAGE_TBL1"/>
 +
                    <xsd:element name="FOO" type="xsd:string"/>
 +
                </xsd:sequence>
 +
            </xsd:complexType>
 +
            <xsd:complexType name="p2ResponseType">
 +
                <xsd:sequence>
 +
                    <xsd:element name="result">
 +
                        <xsd:complexType><xsd:sequence><xsd:any/></xsd:sequence></xsd:complexType>
 +
                    </xsd:element>
 +
                </xsd:sequence>
 +
            </xsd:complexType>
 +
            <xsd:complexType name="p2RequestType">
 +
                <xsd:sequence>
 +
                    <xsd:element name="SIMPLARRAY" type="ns1:SOMEPACKAGE_TBL1"/>
 +
                    <xsd:element name="FOO" type="xsd:string"/>
 +
                    <xsd:element name="BAR" type="xsd:string"/>
 +
                </xsd:sequence>
 +
            </xsd:complexType>
 +
            <xsd:element name="p2" type="tns:p2RequestType"/>
 +
            <xsd:element name="p1" type="tns:p1RequestType"/>
 +
            <xsd:element name="p1Response" type="tns:p1ResponseType"/>
 +
            <xsd:element name="p2Response" type="tns:p2ResponseType"/>
 +
        </xsd:schema>
 +
    </wsdl:types>
 +
    <wsdl:message name="p2Request"><wsdl:part name="p2Request" element="tns:p2"/></wsdl:message>
 +
    <wsdl:message name="p2Response"><wsdl:part name="p2Response" element="tns:p2Response"/></wsdl:message>
 +
    <wsdl:message name="p1Request"><wsdl:part name="p1Request" element="tns:p1"/></wsdl:message>
 +
    <wsdl:message name="p1Response"><wsdl:part name="p1Response" element="tns:p1Response"/></wsdl:message>
 +
    <wsdl:portType name="plsqloverloadService_Interface">
 +
        <wsdl:operation name="p2">
 +
            <wsdl:input message="tns:p2Request"/>
 +
            <wsdl:output message="tns:p2Response"/>
 +
        </wsdl:operation>
 +
        <wsdl:operation name="p1">
 +
            <wsdl:input message="tns:p1Request"/>
 +
            <wsdl:output message="tns:p1Response"/>
 +
        </wsdl:operation>
 +
    </wsdl:portType>
 +
    <wsdl:binding name="plsqloverloadService_SOAP_HTTP" type="tns:plsqloverloadService_Interface">
 +
        <soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>
 +
        <wsdl:operation name="p2">
 +
            <soap:operation soapAction="urn:plsqloverloadService:p2"/>
 +
            <wsdl:input><soap:body use="literal"/></wsdl:input>
 +
            <wsdl:output><soap:body use="literal"/></wsdl:output>
 +
        </wsdl:operation>
 +
        <wsdl:operation name="p1">
 +
            <soap:operation soapAction="urn:plsqloverloadService:p1"/>
 +
            <wsdl:input><soap:body use="literal"/></wsdl:input>
 +
            <wsdl:output><soap:body use="literal"/></wsdl:output>
 +
        </wsdl:operation>
 +
    </wsdl:binding>
 +
    <wsdl:service name="plsqloverloadService">
 +
        <wsdl:port name="plsqloverloadServicePort" binding="tns:plsqloverloadService_SOAP_HTTP">
 +
            <soap:address location="REPLACE_WITH_ENDPOINT_ADDRESS"/>
 +
        </wsdl:port>
 +
    </wsdl:service>
 +
</wsdl:definitions>
 
</source>
 
</source>
−
The element tags <tt><b>simple-xml-format</b></tt> and <tt><b>simple-xml</b></tt> can be customized by setting the appropriate properties
 
−
on an <tt>sql</tt> operation.
 
 
[[Category:EclipseLink/Example/DBWS]]
 
[[Category:EclipseLink/Example/DBWS]]

Latest revision as of 13:48, 13 June 2011


{NB - this capability is available starting in version 2.3 of DBWS}

EclipseLink DBWS Service based on an overloaded PL/SQL Stored Procedure

The use-case for this example is the creation of a Web service that exposes multiple PL/SQL Stored Procedures. Normally, the user would specify in the DBWS builder file which procedures will make up the Web Service. However, in this scenario, the user can specify a single procedure name and leverage Oracle's support for overloading (where a single procedure can have different parameters specified):

CREATE PROCEDURE P(SIMPLARRAY IN TBL1, FOO IN VARCHAR2) AS
BEGIN
  -- 2 arguments SIMPLARRAY and FOO
END P;
CREATE PROCEDURE P(SIMPLARRAY IN TBL1, FOO IN VARCHAR2, BAR IN VARCHAR2) AS
BEGIN
  -- (same name 'P') 3 arguments SIMPLARRAY, FOO and BAR
END P;

Any combination of IN, OUT and IN OUT arguments are supported

Type TBL1 is defined in PL/SQL Package SOMEPACKAGE as follows:

CREATE OR REPLACE PACKAGE SOMEPACKAGE AS
  TYPE TBL1 IS TABLE OF VARCHAR2(111) INDEX BY BINARY_INTEGER;
  PROCEDURE P(SIMPLARRAY IN TBL1, FOO IN VARCHAR2);
  PROCEDURE P(SIMPLARRAY IN TBL1, FOO IN VARCHAR2, BAR IN VARCHAR2);
END SOMEPACKAGE;

The DBWSBuilder utility requires a DBWS configuration file as input.

<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <properties>
    <property name="projectName">testOverloadedProcedure</property>
    ... database properties
  </properties>
  <plsql-procedure
   name="overloadedProcedure"
   catalogPattern="SOMEPACKAGE"
   procedurePattern="P"
 />
</dbws-builder>
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testEcho.war

where

  • dbws-builder.xml is the DBWS builder XML file above
  • output_directory is the output directory for the generated files
  • -packageAs specifies the platform on which the web service will be deployed

When generating queries and the WSDL when overloaded procedures are present, an index is used to uniquely identify each procedure. The index starts at 1, and incremented for each overloaded procedure. The generated eclipselink-dbws.wsdl for this example follows.  :

<wsdl:definitions
    name="plsqloverloadService"
    targetNamespace="urn:plsqloverloadService"
    xmlns:ns1="urn:plsqloverload"
    xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/"
    xmlns:tns="urn:plsqloverloadService"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/"
   >
    <wsdl:types>
        <xsd:schema elementFormDefault="qualified" targetNamespace="urn:plsqloverloadService" xmlns:tns="urn:plsqloverloadService"
            xmlns:xsd="http://www.w3.org/2001/XMLSchema">
            <xsd:import namespace="urn:plsqloverload" schemaLocation="eclipselink-dbws-schema.xsd"/>
            <xsd:complexType name="p1ResponseType">
                <xsd:sequence>
                    <xsd:element name="result">
                        <xsd:complexType><xsd:sequence><xsd:any/></xsd:sequence></xsd:complexType>
                    </xsd:element>
                </xsd:sequence>
            </xsd:complexType>
            <xsd:complexType name="p1RequestType">
                <xsd:sequence>
                    <xsd:element name="SIMPLARRAY" type="ns1:SOMEPACKAGE_TBL1"/>
                    <xsd:element name="FOO" type="xsd:string"/>
                </xsd:sequence>
            </xsd:complexType>
            <xsd:complexType name="p2ResponseType">
                <xsd:sequence>
                    <xsd:element name="result">
                        <xsd:complexType><xsd:sequence><xsd:any/></xsd:sequence></xsd:complexType>
                    </xsd:element>
                </xsd:sequence>
            </xsd:complexType>
            <xsd:complexType name="p2RequestType">
                <xsd:sequence>
                    <xsd:element name="SIMPLARRAY" type="ns1:SOMEPACKAGE_TBL1"/>
                    <xsd:element name="FOO" type="xsd:string"/>
                    <xsd:element name="BAR" type="xsd:string"/>
                </xsd:sequence>
            </xsd:complexType>
            <xsd:element name="p2" type="tns:p2RequestType"/>
            <xsd:element name="p1" type="tns:p1RequestType"/>
            <xsd:element name="p1Response" type="tns:p1ResponseType"/>
            <xsd:element name="p2Response" type="tns:p2ResponseType"/>
        </xsd:schema>
    </wsdl:types>
    <wsdl:message name="p2Request"><wsdl:part name="p2Request" element="tns:p2"/></wsdl:message>
    <wsdl:message name="p2Response"><wsdl:part name="p2Response" element="tns:p2Response"/></wsdl:message>
    <wsdl:message name="p1Request"><wsdl:part name="p1Request" element="tns:p1"/></wsdl:message>
    <wsdl:message name="p1Response"><wsdl:part name="p1Response" element="tns:p1Response"/></wsdl:message>
    <wsdl:portType name="plsqloverloadService_Interface">
        <wsdl:operation name="p2">
            <wsdl:input message="tns:p2Request"/>
            <wsdl:output message="tns:p2Response"/>
        </wsdl:operation>
        <wsdl:operation name="p1">
            <wsdl:input message="tns:p1Request"/>
            <wsdl:output message="tns:p1Response"/>
        </wsdl:operation>
    </wsdl:portType>
    <wsdl:binding name="plsqloverloadService_SOAP_HTTP" type="tns:plsqloverloadService_Interface">
        <soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>
        <wsdl:operation name="p2">
            <soap:operation soapAction="urn:plsqloverloadService:p2"/>
            <wsdl:input><soap:body use="literal"/></wsdl:input>
            <wsdl:output><soap:body use="literal"/></wsdl:output>
        </wsdl:operation>
        <wsdl:operation name="p1">
            <soap:operation soapAction="urn:plsqloverloadService:p1"/>
            <wsdl:input><soap:body use="literal"/></wsdl:input>
            <wsdl:output><soap:body use="literal"/></wsdl:output>
        </wsdl:operation>
    </wsdl:binding>
    <wsdl:service name="plsqloverloadService">
        <wsdl:port name="plsqloverloadServicePort" binding="tns:plsqloverloadService_SOAP_HTTP">
            <soap:address location="REPLACE_WITH_ENDPOINT_ADDRESS"/>
        </wsdl:port>
    </wsdl:service>
</wsdl:definitions>
Retrieved from "https://wiki.eclipse.org/index.php?title=EclipseLink/Examples/DBWS/DBWSOverloadStoredProcedure&oldid=255354"

This page was last modified 13:48, 13 June 2011 by Mike Norman. Based on work by David McCann.

Copyright © Eclipse Foundation, Inc. All Rights Reserved.