Difference between revisions of "EclipseLink/UserGuide/DBWS/Creating from a Stored Function with complex PL/SQL arguments"

Revision as of 08:08, 3 November 2011

EclipseLink
Website
Download
Community
Mailing List • Forums • IRC • mattermost
Issues
Open • Help Wanted • Bug Day
Contribute
Browse Source

Creating from a Stored Function with complex PL/SQL arguments

Starting with EclipseLink 2.3, you can create a DBWS web service from a stored function that uses complex PL/SQL types as either an IN, OUT,IN OUT or return argument.

Example

In this example, the following stored function is used:

FUNCTION F1(OLDREC IN ARECORD, FOO IN VARCHAR2) RETURN ARECORD IS
  arec ARECORD; -- temp var
  BEGIN
    arec.T1 := ... some processing based upon OLDREC
    arec.T2 := ... AND FOO
    arec.T3 := ...
    RETURN arec;
  END F1;

Type ARECORD is defined in the PL/SQL -ackage 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.

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

Notice thatt returnType is set to SOMEPACKAGE_ARECORD. This value indicates a complex type in the generated EclipseLink DBWS schema (as shown below). In this case, it is constructed based on the contents of the SOMEPACKAGE package.

Execute the DBWSBuilder, as shown here:

prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testPLSQLFunction.war

where

• dbws-builder.xml is the DBWS builder configuration file (as shown previously).
• output_directory is the output directory for the generated files.
• -packageAs is the platform on which the web service will be deployed.

The generated eclipselink-dbws-schema.xsd file follows: