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.
Difference between revisions of "EclipseLink/Examples/DBWS/DBWSOverloadStoredProcedure"
< EclipseLink | Examples | DBWS
Line 15: | Line 15: | ||
The following PL/SQL stored procedures will be used for this example: <source lang="sql" enclose="div"> | The following PL/SQL stored procedures will be used for this example: <source lang="sql" enclose="div"> | ||
− | CREATE PROCEDURE | + | CREATE PROCEDURE P(SIMPLARRAY IN TBL1, FOO IN VARCHAR2) AS |
BEGIN | BEGIN | ||
// do something | // do something | ||
− | END | + | END P; |
− | CREATE PROCEDURE | + | CREATE PROCEDURE P(SIMPLARRAY IN TBL1, FOO IN VARCHAR2, BAR IN VARCHAR2) AS |
BEGIN | BEGIN | ||
// do something | // do something | ||
− | END | + | END P; |
</source> | </source> | ||
Line 29: | Line 29: | ||
CREATE OR REPLACE PACKAGE SOMEPACKAGE AS | CREATE OR REPLACE PACKAGE SOMEPACKAGE AS | ||
TYPE TBL1 IS TABLE OF VARCHAR2(111) INDEX BY BINARY_INTEGER; | TYPE TBL1 IS TABLE OF VARCHAR2(111) INDEX BY BINARY_INTEGER; | ||
− | PROCEDURE | + | PROCEDURE P(SIMPLARRAY IN TBL1, FOO IN VARCHAR2); |
− | PROCEDURE | + | PROCEDURE P(SIMPLARRAY IN TBL1, FOO IN VARCHAR2, BAR IN VARCHAR2); |
END SOMEPACKAGE; | END SOMEPACKAGE; | ||
</source> | </source> | ||
Line 45: | Line 45: | ||
name="overloadedProcedure" | name="overloadedProcedure" | ||
catalogPattern="SOMEPACKAGE" | catalogPattern="SOMEPACKAGE" | ||
− | procedurePattern=" | + | procedurePattern="P" |
/> | /> | ||
</dbws-builder> | </dbws-builder> | ||
Line 58: | Line 58: | ||
* <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 | + | 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"> |
− | < | + | <wsdl:definitions |
− | <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" | + | 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:schema> | + | <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> | ||
[[Category:EclipseLink/Example/DBWS]] | [[Category:EclipseLink/Example/DBWS]] |
Revision as of 15:13, 10 June 2011
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 a PL/SQL 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 PL/SQL stored procedures will be used for this example:CREATE PROCEDURE P(SIMPLARRAY IN TBL1, FOO IN VARCHAR2) AS
BEGIN
// do something
END P;
CREATE PROCEDURE P(SIMPLARRAY IN TBL1, FOO IN VARCHAR2, BAR IN VARCHAR2) AS
BEGIN
// do something
END P;
BEGIN
// do something
END P;
CREATE PROCEDURE P(SIMPLARRAY IN TBL1, FOO IN VARCHAR2, BAR IN VARCHAR2) AS
BEGIN
// do something
END P;
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;
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>
<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
<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>
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>