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/DBWSComplexArgStoredProcedure"
(New page: <css> .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-xml {padding...) |
(→EclipseLink DBWS Service based on Stored Procedure with complex PL/SQL arguments) |
||
Line 8: | Line 8: | ||
__NOTOC__ | __NOTOC__ | ||
− | == EclipseLink DBWS Service based on Stored Procedure with complex PL/SQL arguments == | + | == EclipseLink DBWS Service based on a PL/SQL Stored Procedure with complex PL/SQL arguments == |
− | + | The use-case for this example is the creation of a Web service that exposes a PL/SQL Stored Procedure that uses complex PL/SQL types as an <tt>IN</tt>, <tt>OUT</tt> or <tt>IN OUT</tt> argument. | |
+ | |||
+ | The following stored procedure will be used for this example: | ||
+ | <source lang="plsql" enclose="div"> | ||
+ | PROCEDURE P1(OLDREC IN ARECORD, FOO IN VARCHAR2, AREC OUT ARECORD) IS | ||
+ | BEGIN | ||
+ | AREC.T1 := ... some processing based upon OLDREC | ||
+ | AREC.T2 := ... and FOO | ||
+ | AREC.T3 := ... | ||
+ | END P1; | ||
+ | </source> | ||
+ | |||
+ | Type <code>ARECORD</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; | ||
+ | TYPE TBL2 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; | ||
+ | TYPE ARECORD IS RECORD ( | ||
+ | T1 TBL1, | ||
+ | T2 TBL2, | ||
+ | T3 BOOLEAN | ||
+ | ); | ||
+ | FUNCTION F1(OLDREC IN ARECORD, FOO IN VARCHAR2) RETURN ARECORD; | ||
+ | END SOMEPACKAGE; | ||
+ | </source> | ||
+ | |||
+ | Because PL/SQL record and collection types cannot be transported over JDBC, an anonymous block of PL/SQL code will be generated that contains functions that convert to/from JDBC and PL/SQL types. In order for this to work, each PL/SQL record or collection type that will be an <tt>IN</tt>, <tt>IN OUT</tt>, <tt>OUT</tt> or <tt>RETURN</tt> argument will need to have an equivalent JDBC type - the name of the type is expected to be in the form <code><package name>_<type name></code>. In this example, the following JDBC type is required: | ||
+ | |||
+ | <source lang="plsql" enclose="div"> | ||
+ | CREATE OR REPLACE TYPE SOMEPACKAGE_ARECORD AS OBJECT ( | ||
+ | T1 TBL1, | ||
+ | T2 TBL2, | ||
+ | T3 BOOLEAN | ||
+ | ) | ||
+ | </source> | ||
+ | |||
+ | The <tt>DBWSBuilder</tt> utility requires a DBWS configuration file as input. Note that the return type will be <code>SOMEPACKAGE_ARECORD</code>; this is a complex type in the generated EclipseLink DBWS schema (below), which in this case is constructed based on the contents of the package <code>SOMEPACKAGE</code>. | ||
+ | <source lang="xml" enclose="div"> | ||
+ | <?xml version="1.0" encoding="UTF-8"?> | ||
+ | <dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"> | ||
+ | <properties> | ||
+ | <property name="projectName">testPLSQLFunction</property> | ||
+ | ... database properties | ||
+ | </properties> | ||
+ | <plsql-procedure | ||
+ | name="plsqlfunction" | ||
+ | catalogPattern="SOMEPACKAGE" | ||
+ | procedurePattern="F1" | ||
+ | /> | ||
+ | </dbws-builder> | ||
+ | </source> | ||
+ | <source lang="text"> | ||
+ | prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testPLSQLFunction.war | ||
+ | </source> | ||
+ | |||
+ | where | ||
+ | |||
+ | *<tt>dbws-builder.xml</tt> is the DBWS builder XML file above | ||
+ | *<tt>output_directory</tt> is the output directory for the generated files | ||
+ | *<tt>-packageAs</tt> specifies the platform on which the web service will be deployed | ||
+ | |||
+ | The generated <tt>'''eclipselink-dbws-schema.xsd'''</tt> file follows: | ||
+ | <source lang="xml" enclose="div"> | ||
+ | <?xml version="1.0" encoding="UTF-8"?> | ||
+ | <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:plsqlfunction" xmlns="urn:plsqlfunction" elementFormDefault="qualified"> | ||
+ | <xsd:complexType name="SOMEPACKAGE_TBL1"> | ||
+ | <xsd:sequence> | ||
+ | <xsd:element name="item" type="xsd:string" maxOccurs="unbounded" nillable="true"/> | ||
+ | </xsd:sequence> | ||
+ | </xsd:complexType> | ||
+ | <xsd:complexType name="SOMEPACKAGE_TBL2"> | ||
+ | <xsd:sequence> | ||
+ | <xsd:element name="item" type="xsd:decimal" maxOccurs="unbounded" nillable="true"/> | ||
+ | </xsd:sequence> | ||
+ | </xsd:complexType> | ||
+ | <xsd:complexType name="SOMEPACKAGE_ARECORD"> | ||
+ | <xsd:sequence> | ||
+ | <xsd:element name="t1"> | ||
+ | <xsd:complexType> | ||
+ | <xsd:sequence> | ||
+ | <xsd:element name="item" type="xsd:string" maxOccurs="unbounded" nillable="true"/> | ||
+ | </xsd:sequence> | ||
+ | </xsd:complexType> | ||
+ | </xsd:element> | ||
+ | <xsd:element name="t2"> | ||
+ | <xsd:complexType> | ||
+ | <xsd:sequence> | ||
+ | <xsd:element name="item" type="xsd:decimal" maxOccurs="unbounded" nillable="true"/> | ||
+ | </xsd:sequence> | ||
+ | </xsd:complexType> | ||
+ | </xsd:element> | ||
+ | <xsd:element name="t3" type="xsd:boolean" nillable="true"/> | ||
+ | </xsd:sequence> | ||
+ | </xsd:complexType> | ||
+ | <xsd:complexType name="simple-xml-format"> | ||
+ | <xsd:sequence> | ||
+ | <xsd:any minOccurs="0"/> | ||
+ | </xsd:sequence> | ||
+ | </xsd:complexType> | ||
+ | <xsd:element name="SOMEPACKAGE_TBL1" type="SOMEPACKAGE_TBL1"/> | ||
+ | <xsd:element name="SOMEPACKAGE_TBL2" type="SOMEPACKAGE_TBL2"/> | ||
+ | <xsd:element name="SOMEPACKAGE_ARECORD" type="SOMEPACKAGE_ARECORD"/> | ||
+ | </xsd:schema> | ||
+ | </source> | ||
+ | |||
+ | [[Category:EclipseLink/Example/DBWS]] |
Revision as of 14:03, 25 July 2012
EclipseLink DBWS Service based on a PL/SQL Stored Procedure with complex PL/SQL arguments
The use-case for this example is the creation of a Web service that exposes a PL/SQL Stored Procedure that uses complex PL/SQL types as an IN, OUT or IN OUT argument.
The following stored procedure will be used for this example:
BEGIN
AREC.T1 := ... some processing based upon OLDREC
AREC.T2 := ... AND FOO
AREC.T3 := ...
END P1;
Type ARECORD
is defined in PL/SQL Package SOMEPACKAGE
as follows:
TYPE TBL1 IS TABLE OF VARCHAR2(111) INDEX BY BINARY_INTEGER;
TYPE TBL2 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE ARECORD IS RECORD (
T1 TBL1,
T2 TBL2,
T3 BOOLEAN
);
FUNCTION F1(OLDREC IN ARECORD, FOO IN VARCHAR2) RETURN ARECORD;
END SOMEPACKAGE;
Because PL/SQL record and collection types cannot be transported over JDBC, an anonymous block of PL/SQL code will be generated that contains functions that convert to/from JDBC and PL/SQL types. In order for this to work, each PL/SQL record or collection type that will be an IN, IN OUT, OUT or RETURN argument will need to have an equivalent JDBC type - the name of the type is expected to be in the form <package name>_<type name>
. In this example, the following JDBC type is required:
T1 TBL1,
T2 TBL2,
T3 BOOLEAN
)
The DBWSBuilder utility requires a DBWS configuration file as input. Note that the return type will be SOMEPACKAGE_ARECORD
; this is a complex type in the generated EclipseLink DBWS schema (below), which in this case is constructed based on the contents of the package SOMEPACKAGE
.
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<properties>
<property name="projectName">testPLSQLFunction</property>
... database properties
</properties>
<plsql-procedure
name="plsqlfunction"
catalogPattern="SOMEPACKAGE"
procedurePattern="F1"
/>
</dbws-builder>
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testPLSQLFunction.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
The generated eclipselink-dbws-schema.xsd file follows:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:plsqlfunction" xmlns="urn:plsqlfunction" elementFormDefault="qualified">
<xsd:complexType name="SOMEPACKAGE_TBL1">
<xsd:sequence>
<xsd:element name="item" type="xsd:string" maxOccurs="unbounded" nillable="true"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="SOMEPACKAGE_TBL2">
<xsd:sequence>
<xsd:element name="item" type="xsd:decimal" maxOccurs="unbounded" nillable="true"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="SOMEPACKAGE_ARECORD">
<xsd:sequence>
<xsd:element name="t1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="item" type="xsd:string" maxOccurs="unbounded" nillable="true"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="t2">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="item" type="xsd:decimal" maxOccurs="unbounded" nillable="true"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="t3" type="xsd:boolean" nillable="true"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="simple-xml-format">
<xsd:sequence>
<xsd:any minOccurs="0"/>
</xsd:sequence>
</xsd:complexType>
<xsd:element name="SOMEPACKAGE_TBL1" type="SOMEPACKAGE_TBL1"/>
<xsd:element name="SOMEPACKAGE_TBL2" type="SOMEPACKAGE_TBL2"/>
<xsd:element name="SOMEPACKAGE_ARECORD" type="SOMEPACKAGE_ARECORD"/>
</xsd:schema>