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"
< EclipseLink | Examples | DBWS
(→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) |
||
| Line 32: | Line 32: | ||
T3 BOOLEAN | T3 BOOLEAN | ||
); | ); | ||
| − | + | PROCEDURE P1(OLDREC IN ARECORD, FOO IN VARCHAR2, AREC OUT ARECORD); | |
END SOMEPACKAGE; | END SOMEPACKAGE; | ||
</source> | </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 | + | 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 (or any PL/SQL record/collection type nested within these) 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 types are required: |
<source lang="plsql" enclose="div"> | <source lang="plsql" enclose="div"> | ||
| + | CREATE OR REPLACE TYPE SOMEPACKAGE_TBL1 AS TABLE OF VARCHAR2(111) | ||
| + | |||
| + | CREATE OR REPLACE TYPE SOMEPACKAGE_TBL2 AS TABLE OF NUMBER | ||
| + | |||
CREATE OR REPLACE TYPE SOMEPACKAGE_ARECORD AS OBJECT ( | CREATE OR REPLACE TYPE SOMEPACKAGE_ARECORD AS OBJECT ( | ||
| − | T1 | + | T1 SOMEPACKAGE_TBL1, |
| − | T2 | + | T2 SOMEPACKAGE_TBL2, |
T3 BOOLEAN | T3 BOOLEAN | ||
) | ) | ||
| Line 51: | Line 55: | ||
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"> | <dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"> | ||
<properties> | <properties> | ||
| − | <property name="projectName"> | + | <property name="projectName">testPLSQLProcedure</property> |
... database properties | ... database properties | ||
</properties> | </properties> | ||
<plsql-procedure | <plsql-procedure | ||
| − | name=" | + | name="plsqlprocedure" |
catalogPattern="SOMEPACKAGE" | catalogPattern="SOMEPACKAGE" | ||
| − | procedurePattern=" | + | procedurePattern="P1" |
/> | /> | ||
</dbws-builder> | </dbws-builder> | ||
</source> | </source> | ||
<source lang="text"> | <source lang="text"> | ||
| − | prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls | + | prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testPLSQLProcedure.war |
</source> | </source> | ||
| Line 74: | Line 78: | ||
<source lang="xml" enclose="div"> | <source lang="xml" enclose="div"> | ||
<?xml version="1.0" encoding="UTF-8"?> | <?xml version="1.0" encoding="UTF-8"?> | ||
| − | <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn: | + | <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:plsqlprocedure" xmlns="urn:plsqlprocedure" elementFormDefault="qualified"> |
<xsd:complexType name="SOMEPACKAGE_TBL1"> | <xsd:complexType name="SOMEPACKAGE_TBL1"> | ||
<xsd:sequence> | <xsd:sequence> | ||
Latest revision as of 14:10, 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:
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;
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:
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
);
PROCEDURE P1(OLDREC IN ARECORD, FOO IN VARCHAR2, AREC OUT ARECORD);
END SOMEPACKAGE;
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
);
PROCEDURE P1(OLDREC IN ARECORD, FOO IN VARCHAR2, AREC OUT 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 (or any PL/SQL record/collection type nested within these) 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 types are required:
CREATE OR REPLACE TYPE SOMEPACKAGE_TBL1 AS TABLE OF VARCHAR2(111)
CREATE OR REPLACE TYPE SOMEPACKAGE_TBL2 AS TABLE OF NUMBER
CREATE OR REPLACE TYPE SOMEPACKAGE_ARECORD AS OBJECT (
T1 SOMEPACKAGE_TBL1,
T2 SOMEPACKAGE_TBL2,
T3 BOOLEAN
)
CREATE OR REPLACE TYPE SOMEPACKAGE_TBL2 AS TABLE OF NUMBER
CREATE OR REPLACE TYPE SOMEPACKAGE_ARECORD AS OBJECT (
T1 SOMEPACKAGE_TBL1,
T2 SOMEPACKAGE_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.
<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<properties>
<property name="projectName">testPLSQLProcedure</property>
... database properties
</properties>
<plsql-procedure
name="plsqlprocedure"
catalogPattern="SOMEPACKAGE"
procedurePattern="P1"
/>
</dbws-builder>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<properties>
<property name="projectName">testPLSQLProcedure</property>
... database properties
</properties>
<plsql-procedure
name="plsqlprocedure"
catalogPattern="SOMEPACKAGE"
procedurePattern="P1"
/>
</dbws-builder>
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testPLSQLProcedure.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:
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:plsqlprocedure" xmlns="urn:plsqlprocedure" 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>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:plsqlprocedure" xmlns="urn:plsqlprocedure" 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>