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

From Eclipsepedia

Jump to: navigation, search
(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 a PL/SQL Stored Procedure with complex PL/SQL arguments)
 
(One intermediate revision by one user not shown)
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  ==
  
TBD
+
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
 +
  );
 +
  PROCEDURE P1(OLDREC IN ARECORD, FOO IN VARCHAR2, AREC OUT 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 (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">
 +
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
 +
)
 +
</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">testPLSQLProcedure</property>
 +
    ... database properties
 +
  </properties>
 +
  <plsql-procedure
 +
    name="plsqlprocedure"
 +
    catalogPattern="SOMEPACKAGE"
 +
    procedurePattern="P1"
 +
  />
 +
</dbws-builder>
 +
</source>
 +
<source lang="text">
 +
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testPLSQLProcedure.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: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>
 +
</source>
 +
 
 +
[[Category:EclipseLink/Example/DBWS]]

Latest revision as of 14:10, 25 July 2012


[edit] 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;

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;

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
)

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>
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>