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/DBWSComplexArgStoredFunction"
< EclipseLink | Examples | DBWS
(→EclipseLink DBWS Service based on Stored Function with complex PL/SQL arguments) |
(→EclipseLink DBWS Service based on Stored Function with complex PL/SQL arguments) |
||
| Line 12: | Line 12: | ||
The use-case for this example is the creation of a Web service that exposes a Stored Function that uses complex PL/SQL types as either an <tt>IN</tt>, <tt>OUT</tt> or <tt>IN OUT</tt> argument. | The use-case for this example is the creation of a Web service that exposes a Stored Function that uses complex PL/SQL types as either an <tt>IN</tt>, <tt>OUT</tt> or <tt>IN OUT</tt> argument. | ||
| − | The following stored function will be used for this example: <source lang=" | + | The following stored function will be used for this example: |
| + | <source lang="plsql" enclose="div"> | ||
FUNCTION F1(OLDREC IN ARECORD, FOO IN VARCHAR2) RETURN ARECORD IS arec ARECORD; | FUNCTION F1(OLDREC IN ARECORD, FOO IN VARCHAR2) RETURN ARECORD IS arec ARECORD; | ||
BEGIN | BEGIN | ||
| Line 22: | Line 23: | ||
</source> | </source> | ||
| − | Type <code>ARECORD</code> is defined in PL/SQL Package <code>SOMEPACKAGE</code> as follows: <source lang=" | + | 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 | 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; | ||
| Line 35: | Line 37: | ||
</source> | </source> | ||
| − | The <tt>DBWSBuilder</tt> utility requires a DBWS configuration file as input. Note that <code>returnType</code> is set to <code>SOMEPACKAGE_ARECORD</code>; this value indicates 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"> | + | The <tt>DBWSBuilder</tt> utility requires a DBWS configuration file as input. Note that <code>returnType</code> is set to <code>SOMEPACKAGE_ARECORD</code>; this value indicates 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"?> | <?xml version="1.0" encoding="UTF-8"?> | ||
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"> | <dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"> | ||
| Line 49: | Line 52: | ||
/> | /> | ||
</dbws-builder> | </dbws-builder> | ||
| − | </source> <source lang="text"> | + | </source> |
| + | <source lang="text"> | ||
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testPLSQLFunction.war | prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testPLSQLFunction.war | ||
</source> | </source> | ||
| Line 59: | Line 63: | ||
*<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>'''eclipselink-dbws-schema.xsd'''</tt> file follows: <source lang="xml" enclose="div"> | + | The generated <tt>'''eclipselink-dbws-schema.xsd'''</tt> file follows: |
| + | <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:plsqlfunction" xmlns="urn:plsqlfunction" elementFormDefault="qualified"> | <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:plsqlfunction" xmlns="urn:plsqlfunction" elementFormDefault="qualified"> | ||
Revision as of 13:30, 13 June 2011
{NB - this capability is available starting in version 2.3 of DBWS}
EclipseLink DBWS Service based on Stored Function with complex PL/SQL arguments
The use-case for this example is the creation of a Web service that exposes a Stored Function that uses complex PL/SQL types as either an IN, OUT or IN OUT argument.
The following stored function will be used for this example:
FUNCTION F1(OLDREC IN ARECORD, FOO IN VARCHAR2) RETURN ARECORD IS arec ARECORD;
BEGIN
arec.T1 := ...
arec.T2 := ...
arec.T3 := ...
RETURN arec;
END F1;
BEGIN
arec.T1 := ...
arec.T2 := ...
arec.T3 := ...
RETURN arec;
END F1;
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
);
FUNCTION F1(OLDREC IN ARECORD, FOO IN VARCHAR2) RETURN 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
);
FUNCTION F1(OLDREC IN ARECORD, FOO IN VARCHAR2) RETURN ARECORD;
END SOMEPACKAGE;
The DBWSBuilder utility requires a DBWS configuration file as input. Note that returnType is set to SOMEPACKAGE_ARECORD; this value indicates 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">testPLSQLFunction</property>
... database properties
</properties>
<plsql-procedure
name="plsqlfunction"
catalogPattern="SOMEPACKAGE"
procedurePattern="F1"
returnType="SOMEPACKAGE_ARECORD"
/>
</dbws-builder>
<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"
returnType="SOMEPACKAGE_ARECORD"
/>
</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:
<?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>
<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>