EclipseLink/Examples/DBWS/DBWSComplexArgStoredFunction

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 simple Stored Function containing complex PL/SQL arguments.

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;

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;

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 schema, which is constructed based on (in this case) the contents of the package SOMEPACKAGE. The generated schema would contain complex types for each type in the package, i.e. SOMEPACKAGE_TBL1 SOMEPACKAGE_TBL2 SOMEPACKAGE_ARECORD.

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

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