Skip to main content

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.

Jump to: navigation, search

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

m (Example)
m (Replacing page with ''''Warning This page is obsolete. Please see ''[http://www.eclipse.org/eclipselink/documentation/ Developing Persistence Architectures Using Eclip...')
 
(3 intermediate revisions by the same user not shown)
Line 1: Line 1:
{{EclipseLink_UserGuide
+
'''[[Image:Elug_draft_icon.png|Warning]] This page is obsolete. Please see ''[http://www.eclipse.org/eclipselink/documentation/ Developing Persistence Architectures Using EclipseLink Database Web Services]'' for current information.'''
|info=y
+
|toc=n
+
|eclipselink=y
+
|eclipselinktype=DBWS
+
}}
+
 
+
==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 <tt>IN</tt>, <tt>OUT</tt>,<tt>IN OUT</tt> or return argument.
+
 
+
 
+
===Example===
+
In this example, the following stored function is used:
+
 
+
<source lang="plsql">
+
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;
+
</source>
+
 
+
Type <code>ARECORD</code> is defined in the PL/SQL -ackage <code>SOMEPACKAGE</code> as follows:
+
<source lang="plsql">
+
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;
+
</source>
+
 
+
The <tt>DBWSBuilder</tt> utility requires a DBWS configuration file as input.  
+
 
+
<source lang="xml">
+
<?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>
+
</source>
+
 
+
Notice thatt <code>returnType</code> is set to <code>SOMEPACKAGE_ARECORD</code>. 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 <code>SOMEPACKAGE</code> package.
+
 
+
Execute the DBWSBuilder, as shown here:
+
<source lang="text">
+
prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls testPLSQLFunction.war
+
</source>
+
 
+
where
+
*<tt>dbws-builder.xml</tt> is the DBWS builder configuration file (as shown previously).
+
*<tt>output_directory</tt> is the output directory for the generated files.
+
*<tt>-packageAs</tt> is 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: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>
+
</source>
+
 
+
[[Category:EclipseLink/Example/DBWS]]
+

Latest revision as of 13:19, 30 January 2013

Warning This page is obsolete. Please see Developing Persistence Architectures Using EclipseLink Database Web Services for current information.

Copyright © Eclipse Foundation, Inc. All Rights Reserved.