Jump to: navigation, search

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

(DBWS Service based on a PL/SQL Stored Function returning a PL/SQL Record)
(Service XML)
 
(39 intermediate revisions by the same user not shown)
Line 1: Line 1:
== DBWS Service based on a PL/SQL Stored Function returning a PL/SQL Record ==
 
 
This end-to-end example is intended to show the artifacts generated by DBWS, along with the relevant steps taken during generation, when DBWSBuilder is used to create a web service based on a PL/SQL Stored Function that returns a PL/SQL Record.
 
This end-to-end example is intended to show the artifacts generated by DBWS, along with the relevant steps taken during generation, when DBWSBuilder is used to create a web service based on a PL/SQL Stored Function that returns a PL/SQL Record.
  
 +
=== PL/SQL DDL and 'Shadow' JDBC Type DDL ===
 
The DDL used to create the PL/SQL Package containing the Record and Stored Function:
 
The DDL used to create the PL/SQL Package containing the Record and Stored Function:
 
<source lang="text">
 
<source lang="text">
Line 13: Line 13:
 
</source>
 
</source>
  
Oracle's JDBC drivers do not support PL/SQL types ([http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#34_05 Oracle FAQ]), so it is required that a 'shadow' top-level (i.e. outside of the PL/SQL Package) JDBC type exists.  In this case, the DDL for the PL/SQL Record's 'shadow' JDBC type would be:
+
Oracle's JDBC drivers do not support PL/SQL types ([http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#34_05 Oracle FAQ]), so it is required that a 'shadow' top-level (i.e. outside of the PL/SQL Package) JDBC type exists.  In this case, the DDL for the PL/SQL Record's shadow JDBC type would be:
  
 
<source lang="text">
 
<source lang="text">
Line 22: Line 22:
 
</source>
 
</source>
  
Note that the JDBC type is named <code>PL/SQL package name + "_" + PL/SQL record name</code>.
+
The expected naming convention for the shadow JDBC type is <code>PL/SQL package name + "_" + PL/SQL record name</code>. This is required as anonymous PL/SQL is executed on the database schema at runtime which is responsible for converting between the shadow JDBC type and the PL/SQL type;  the JDBC type name isn't provided, so this assumption about the shadow type name is necessary.  In this use case the PL/SQL record <code>EMP_RECORD_PACKAGE.EMPREC</code> would have an associated Object type named <code>EMP_RECORD_PACKAGE_EMPREC</code>. 
  
 +
The only PL/SQL type is the return argument, so a single PL/SQL function will be required to convert the PL/SQL Record to the shadow JDBC type before returning.  Note that input PL/SQL types (IN or INOUT) would require conversion functions as well.  The following block of anonymous PL/SQL code will be generated by PLSQLStoredProcedureCall for use at runtime:
 +
<source lang="text">
 +
DECLARE
 +
  pIdTARGET NUMERIC := :1;
 +
  RESULTTARGET EMP_RECORD_PACKAGE.EMPREC;
 +
  FUNCTION EL_PL2SQL_0(aPlsqlItem EMP_RECORD_PACKAGE.EMPREC)
 +
  RETURN EMP_RECORD_PACKAGE_EMPREC IS
 +
    aSqlItem EMP_RECORD_PACKAGE_EMPREC;
 +
  BEGIN
 +
    aSqlItem := EMP_RECORD_PACKAGE_EMPREC(NULL, NULL);
 +
    aSqlItem.emp_id := aPlsqlItem.emp_id;
 +
    aSqlItem.emp_name := aPlsqlItem.emp_name;
 +
    RETURN aSqlItem;
 +
  END EL_PL2SQL_0;
 +
BEGIN
 +
  RESULTTARGET := EMP_RECORD_PACKAGE.get_emp_record(pId=>pIdTARGET);
 +
  :2 := EL_PL2SQL_0(RESULTTARGET);
 +
END;
 +
</source>
 +
 +
DBWSBuilder generates create and drop DDL for any required shadow types.  Here is an example of how the builder's shadow DDL can be used:
 +
<source lang="java">
 +
String[] args = ...
 +
DBWSBuilder builder = new DBWSBuilder();
 +
builder.start(args);
 +
// execute shadow type ddl to generate JDBC equivalents of PL/SQL types
 +
List<String> ddls = new ArrayList<String>();
 +
for (String ddl : builder.getTypeDDL()) {
 +
    try {
 +
        PreparedStatement pStmt = builder.getConnection().prepareStatement(ddl);
 +
        pStmt.execute();
 +
    } catch (SQLException e) {
 +
        // handle exception...
 +
    }
 +
}
 +
</source>
 +
Note that the drop DDL can be accessed via <code>builder.getTypeDropDDL()</code>.
 +
 +
=== Configuring DBWSBuilder ===
 
Following is the XML passed to the Builder (assume the target platform is WebLogic, packaged as a .war):
 
Following is the XML passed to the Builder (assume the target platform is WebLogic, packaged as a .war):
 
<source lang="xml">
 
<source lang="xml">
Line 29: Line 68:
 
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 
   <properties>
 
   <properties>
     <property name="projectName">simpleplsqlsp</property>
+
     <property name="projectName">PLSQLRecord</property>
 
     <property name="logLevel">off</property>
 
     <property name="logLevel">off</property>
 
     <property name="username">SCOTT</property>
 
     <property name="username">SCOTT</property>
Line 37: Line 76:
 
     <property name="platformClassname">org.eclipse.persistence.platform.database.oracle.Oracle11Platform</property>
 
     <property name="platformClassname">org.eclipse.persistence.platform.database.oracle.Oracle11Platform</property>
 
     <property name="dataSource">jdbc/EclipseLinkDS</property>
 
     <property name="dataSource">jdbc/EclipseLinkDS</property>
     <property name="wsdlLocationURI">http://localhost:7001/simpleplsqlsp/simpleplsqlsp?wsdl</property>
+
     <property name="wsdlLocationURI">http://localhost:7001/PLSQLRecord/PLSQLRecord?wsdl</property>
 
   </properties>
 
   </properties>
   <plsql-procedure name="InOutArgsTest" catalogPattern="SIMPLEPACKAGE" procedurePattern="INOUTARGSPLSQLSP"/>  
+
   <plsql-procedure name="TestRecWithPercentTypeField" catalogPattern="EMP_RECORD_PACKAGE" procedurePattern="get_emp_record"/>
 
</dbws-builder>
 
</dbws-builder>
 +
</source>
 +
Alternatively, the builder can be configured via API as follows:
 +
<source lang="java">
 +
builder = new DBWSBuilder();
 +
         
 +
builder.setProjectName("PLSQLRecord");
 +
builder.setLogLevel("off");
 +
builder.setUsername("SCOTT");
 +
builder.setPassword("TIGER");
 +
builder.setUrl("jdbc:oracle:thin:@localhost:1521:TOPLINK");
 +
builder.setDriver("oracle.jdbc.OracleDriver");
 +
builder.setPlatformClassname("org.eclipse.persistence.platform.database.oracle.Oracle11Platform");
 +
builder.setDataSource("jdbc/EclipseLinkDS");
 +
builder.setWsdlLocationURI("http://localhost:7001/PLSQLRecord/PLSQLRecord?wsdl");
 +
 +
       
 +
PLSQLProcedureOperationModel procOpModel = new PLSQLProcedureOperationModel();
 +
procOpModel.setName("TestRecWithPercentTypeField");
 +
procOpModel.setCatalogPattern("EMP_RECORD_PACKAGE");
 +
procOpModel.setProcedurePattern("get_emp_record");
 +
builder.addOperation(procOpModel);
 +
 +
builder.start();
 
</source>
 
</source>
  
Based on the provided Builder XML, the DDL for SCOTT.SIMPLEPACKAGE.INOUTARGSPLSQLSP would be extracted as follows:
+
=== Retrieving DDL and executing the DDLParser ===
 +
The DDL for SCOTT.EMP_RECORD_PACKAGE would be extracted as follows:
 
<source lang="text">
 
<source lang="text">
SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE', AO.OBJECT_NAME) AS RESULT FROM ALL_OBJECTS AO WHERE (REGEXP_LIKE(OWNER,?)  
+
SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE', AO.OBJECT_NAME) AS RESULT FROM ALL_OBJECTS AO WHERE (REGEXP_LIKE(OWNER,?)
 
AND AO.STATUS = 'VALID' AND AO.OBJECT_TYPE = ? AND AO.OBJECT_NAME LIKE ?)
 
AND AO.STATUS = 'VALID' AND AO.OBJECT_TYPE = ? AND AO.OBJECT_NAME LIKE ?)
 
</source>
 
</source>
  
 +
Which results in:
 
<source lang="text">
 
<source lang="text">
CREATE OR REPLACE PACKAGE "SCOTT"."SIMPLEPACKAGE" AS
+
CREATE OR REPLACE PACKAGE "SCOTT"."EMP_RECORD_PACKAGE" AS
  PROCEDURE INOUTARGSPLSQLSP(T IN VARCHAR, U OUT VARCHAR, V OUT NUMERIC);
+
    type EmpRec is record (emp_id NUMBER(4), emp_name VARCHAR(25));
END SIMPLEPACKAGE;
+
    function get_emp_record (pId in number) return EmpRec;
 +
END EMP_RECORD_PACKAGE;
 
</source>
 
</source>
  
Line 62: Line 127:
 
DDLParser parser = new DDLParser(new StringReader(ddlString));
 
DDLParser parser = new DDLParser(new StringReader(ddlString));
 
PLSQLPackageType packageType = parser.parsePLSQLPackage();
 
PLSQLPackageType packageType = parser.parsePLSQLPackage();
List<ProcedureType> procedures = packageType.getProcedures();
+
List<FunctionType> functions = packageType.getProcedures();
// iterate through list and locate the proc by name
+
// iterate through list and locate the function by name
 +
List<PLSQLType> types = packageType.getTypes();
 +
// iterate through list and locate the record by name
 +
</source>
 +
 
 +
=== JAXB Metadata ===
 +
<code>XmlBindingsGenerator</code> will generate the following JAXB Metadata (one <code><xml-bindings></code> per package); note the <code>xml-type</code> and <code>xml-root-element</code> names refer to the shadow JDBC type name:
 +
<source lang="xml">
 +
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
 +
<xml-bindings-list xmlns="http://www.eclipse.org/eclipselink/xsds/persistence/oxm">
 +
    <xml-bindings package-name="emp_record_package">
 +
        <xml-schema element-form-default="QUALIFIED" namespace="urn:PLSQLRecord">
 +
            <xml-ns namespace-uri="http://www.w3.org/2001/XMLSchema-instance" prefix="xsi"/>
 +
        </xml-schema>
 +
        <java-types>
 +
            <java-type name="emprec" xml-accessor-type="FIELD">
 +
                <xml-type name="EMP_RECORD_PACKAGE_EMPREC" namespace="urn:PLSQLRecord"/>
 +
                <xml-root-element name="EMP_RECORD_PACKAGE_EMPREC" namespace="urn:PLSQLRecord"/>
 +
                <java-attributes>
 +
                    <xml-element required="true" type="java.math.BigDecimal" xml-path="emp_id/text()" java-attribute="emp_id">
 +
                        <xml-schema-type name="decimal"/>
 +
                    </xml-element>
 +
                    <xml-element required="true" type="java.lang.String" xml-path="emp_name/text()" java-attribute="emp_name">
 +
                        <xml-schema-type name="string"/>
 +
                    </xml-element>
 +
                </java-attributes>
 +
            </java-type>
 +
        </java-types>
 +
    </xml-bindings>
 +
</xml-bindings-list>
 +
</source>
 +
 
 +
=== JPA Metadata ===
 +
<code>XmlEntityMappingsGenerator</code> will generate <code><named-plsql-stored-function-query></code>, <code><plsql-record></code>, and <code><embeddable></code> JPA metadata elements as follows;  note the <code>compatible-type</code> attribute on the <code><plsql-record></code> element which refers to the shadow JDBC type:
 +
<source lang="xml">
 +
<?xml version="1.0" encoding="UTF-8"?>
 +
<orm:entity-mappings
 +
      xsi:schemaLocation="http://www.eclipse.org/eclipselink/xsds/persistence/orm org/eclipse/persistence/jpa/eclipselink_orm_2_5.xsd"
 +
      xmlns:orm="http://www.eclipse.org/eclipselink/xsds/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 +
  <orm:named-plsql-stored-function-query name="TestRecWithPercentTypeField" function-name="EMP_RECORD_PACKAGE.get_emp_record">
 +
      <orm:parameter direction="IN" name="pId" database-type="NUMERIC_TYPE"/>
 +
      <orm:return-parameter name="RESULT" database-type="EMP_RECORD_PACKAGE.EMPREC"/>
 +
  </orm:named-plsql-stored-function-query>
 +
  <orm:plsql-record name="EMP_RECORD_PACKAGE.EMPREC" compatible-type="EMP_RECORD_PACKAGE_EMPREC" java-type="emp_record_package.emprec">
 +
      <orm:field name="emp_id" database-type="NUMERIC_TYPE"/>
 +
      <orm:field name="emp_name" database-type="VARCHAR_TYPE"/>
 +
  </orm:plsql-record>
 +
  <orm:embeddable class="emp_record_package.emprec" access="VIRTUAL">
 +
      <orm:struct name="EMP_RECORD_PACKAGE_EMPREC">  -- required for field ordering
 +
        <orm:field>emp_id</orm:field>
 +
        <orm:field>emp_name</orm:field>
 +
      </orm:struct>
 +
      <orm:attributes>
 +
        <orm:basic name="emp_id" attribute-type="java.math.BigInteger">
 +
            <orm:column name="emp_id"/>
 +
        </orm:basic>
 +
        <orm:basic name="emp_name" attribute-type="java.lang.String">
 +
            <orm:column name="emp_name"/>
 +
        </orm:basic>
 +
      </orm:attributes>
 +
  </orm:embeddable>
 +
</orm:entity-mappings>
 +
</source>
 +
 
 +
=== XML Schema ===
 +
The generated XML schema's elements and types are referenced by the DBWS service file, the JAXB metadata file, and the WSDL; note that the element and type names refer to the shadow JDBC type name, and not the PL/SQL record name:
 +
<source lang="xml">
 +
<?xml version="1.0" encoding="UTF-8"?>
 +
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:PLSQLRecord"
 +
        xmlns="urn:PLSQLRecord" elementFormDefault="qualified">
 +
  <xsd:complexType name="EMP_RECORD_PACKAGE_EMPREC">
 +
      <xsd:sequence>
 +
        <xsd:element name="emp_id" type="xsd:decimal" nillable="true"/>
 +
        <xsd:element name="emp_name" type="xsd:string" nillable="true"/>
 +
      </xsd:sequence>
 +
  </xsd:complexType>
 +
  <xsd:element name="EMP_RECORD_PACKAGE_EMPREC" type="EMP_RECORD_PACKAGE_EMPREC"/>
 +
</xsd:schema>
 +
</source>
 +
 
 +
=== Service XML ===
 +
The DBWS runtime code (a.k.a. X-R) makes use of 'operations' - <code>QueryOperation</code>, <code>InsertOperation</code>, <code>UpdateOperation</code> and <code>DeleteOperation</code>.  These operations are executable representations of <code>SELECT</code>, <code>INSERT</code>, <code>UPDATE</code> and <code>DELETE</code> database operations.  Each is responsible for looking up it's database query on the session, validating and executing it.  A service XML file is unmarshalled at runtime in order to build these operations.  In this case, the service XML would be:
 +
<source lang="xml">
 +
<?xml version="1.0" encoding="UTF-8"?>
 +
<dbws xmlns:ns1="urn:PLSQLRecord" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 +
  <name>PLSQLRecord</name>
 +
  <sessions-file>eclipselink-dbws-sessions.xml</sessions-file>
 +
  <query>
 +
      <name>TestRecWithPercentTypeField</name>
 +
      <parameter>
 +
        <name>pId</name>
 +
        <type>xsd:decimal</type>
 +
      </parameter>
 +
      <result>
 +
        <type>ns1:EMP_RECORD_PACKAGE_EMPREC</type>
 +
      </result>
 +
      <named-query>
 +
        <name>TestRecWithPercentTypeField</name>
 +
      </named-query>
 +
  </query>
 +
</dbws>
 +
</source>
 +
Each operation will have an associated <code><operation></code> entry in the generated WSDL - the association is based on the query name.
 +
 
 +
=== WSDL ===
 +
The following WSDL will be generated:
 +
<source lang="xml">
 +
<?xml version="1.0" encoding="UTF-8"?>
 +
<wsdl:definitions name="PLSQLRecordService" targetNamespace="urn:PLSQLRecordService" xmlns:ns1="urn:PLSQLRecord"
 +
      xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:tns="urn:PLSQLRecordService"
 +
      xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/">
 +
  <wsdl:types>
 +
    <xsd:schema xmlns:tns="urn:PLSQLRecordService" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
 +
            targetNamespace="urn:PLSQLRecordService" elementFormDefault="qualified">
 +
      <xsd:import schemaLocation="eclipselink-dbws-schema.xsd" namespace="urn:PLSQLRecord"/>
 +
      <xsd:complexType name="TestRecWithPercentTypeFieldRequestType">
 +
        <xsd:sequence>
 +
          <xsd:element name="pId" type="xsd:decimal"/>
 +
        </xsd:sequence>
 +
      </xsd:complexType>
 +
      <xsd:complexType name="TestRecWithPercentTypeFieldResponseType">
 +
        <xsd:sequence>
 +
          <xsd:element name="result">
 +
            <xsd:complexType>
 +
              <xsd:sequence>
 +
                <xsd:element ref="ns1:EMP_RECORD_PACKAGE_EMPREC" minOccurs="0"/>
 +
              </xsd:sequence>
 +
            </xsd:complexType>
 +
          </xsd:element>
 +
        </xsd:sequence>
 +
      </xsd:complexType>
 +
      <xsd:element name="TestRecWithPercentTypeFieldResponse" type="tns:TestRecWithPercentTypeFieldResponseType"/>
 +
      <xsd:element name="TestRecWithPercentTypeField" type="tns:TestRecWithPercentTypeFieldRequestType"/>
 +
    </xsd:schema>
 +
  </wsdl:types>
 +
  <wsdl:message name="TestRecWithPercentTypeFieldResponse">
 +
    <wsdl:part name="TestRecWithPercentTypeFieldResponse" element="tns:TestRecWithPercentTypeFieldResponse"/>
 +
  </wsdl:message>
 +
  <wsdl:message name="TestRecWithPercentTypeFieldRequest">
 +
    <wsdl:part name="TestRecWithPercentTypeFieldRequest" element="tns:TestRecWithPercentTypeField"/>
 +
  </wsdl:message>
 +
  <wsdl:portType name="PLSQLRecordService_Interface">
 +
    <wsdl:operation name="TestRecWithPercentTypeField">
 +
      <wsdl:input message="tns:TestRecWithPercentTypeFieldRequest"/>
 +
      <wsdl:output message="tns:TestRecWithPercentTypeFieldResponse"/>
 +
    </wsdl:operation>
 +
  </wsdl:portType>
 +
  <wsdl:binding name="PLSQLRecordService_SOAP_HTTP" type="tns:PLSQLRecordService_Interface">
 +
    <soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>
 +
    <wsdl:operation name="TestRecWithPercentTypeField">
 +
      <soap:operation soapAction="urn:PLSQLRecordService:TestRecWithPercentTypeField"/>
 +
      <wsdl:input><soap:body use="literal"/></wsdl:input>
 +
      <wsdl:output><soap:body use="literal"/></wsdl:output>
 +
    </wsdl:operation>
 +
  </wsdl:binding>
 +
  <wsdl:service name="PLSQLRecordService">
 +
    <wsdl:port name="PLSQLRecordServicePort" binding="tns:PLSQLRecordService_SOAP_HTTP">
 +
      <soap:address location="http://localhost:7001/PLSQLRecord/PLSQLRecord?wsdl"/>
 +
    </wsdl:port>
 +
  </wsdl:service>
 +
</wsdl:definitions>
 +
</source>
 +
 
 +
=== .war file packaging ===
 +
Assuming that the target packaging is <code>.war</code>, the generated artifacts would be archived like so:
 +
<source lang="text">
 +
\--- root of war file
 +
      |
 +
      \---web-inf
 +
          |  web.xml
 +
          |
 +
          +---classes
 +
          |  +---META-INF
 +
          |  |    eclipselink-dbws.xml            -- service file
 +
          |  |    eclipselink-dbws-sessions.xml -- name can be overridden by <sessions-file> entry in eclipselink-dbws.xml
 +
          |  |    eclipselink-dbws-or.xml
 +
          |  |    eclipselink-dbws-ox.xml
 +
          |  |
 +
          |  +---_dbws
 +
          |  |    DBWSProvider.class        -- ASM-generated javax.xml.ws.Provider
 +
          |  |    DBWSProvider.java        -- (source provided as a convenience for IDE integration)
 +
          |  |    ProviderListener.class    -- ASM-generated javax.servlet.ServletContextListener
 +
          |  |    ProviderListener.java      -- (source provided as a convenience for IDE integration)
 +
          |  |
 +
          \---wsdl
 +
                swaref.xsd                        -- optional to handle attachments
 +
                eclipselink-dbws.wsdl
 +
                eclipselink-dbws-schema.xsd
 
</source>
 
</source>
  
<code>XmlEntityMappingsGenerator</code> will generate the following <code><named-plsql-stored-procedure-query></code> metadata as follows:
+
=== Example SOAP request message ===
 +
A valid SOAP request message would look like:
 
<source lang="xml">
 
<source lang="xml">
<orm:named-plsql-stored-procedure-query name="InOutArgsTest" procedure-name="SIMPLEPACKAGE.INOUTARGSPLSQLSP">
+
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
  <orm:parameter direction="IN" name="T" database-type="VARCHAR_TYPE"/>
+
  <env:Header/>
  <orm:parameter direction="OUT" name="U" database-type="VARCHAR_TYPE"/>
+
  <env:Body>
  <orm:parameter direction="OUT" name="V" database-type="NUMERIC_TYPE"/>
+
    <TestRecWithPercentTypeField xmlns="urn:PLSQLRecordService">
</orm:named-plsql-stored-procedure-query>
+
      <pId>66</pId>
 +
    </TestRecWithPercentTypeField>
 +
  </env:Body>
 +
</env:Envelope>
 
</source>
 
</source>
Note: the <code>name</code> attribute is configured through the builder on the associated operation model.
+
<code>DBWSProvider</code> receives the SOAP request message and passes it to the DBWS runtime (<code>ProviderHelper</code>). The body of the SOAP message is unmarshalled to an <code>Invocation</code> instance, whose responsibility is to store query names and their parameters.  The X-R operation with name matching the invocation name is retrieved, any parameters set, then the underlying EclipseLink query is executed.  The result of the query is converted into a SOAPMessage, which is returned to the caller.

Latest revision as of 12:14, 24 June 2013

This end-to-end example is intended to show the artifacts generated by DBWS, along with the relevant steps taken during generation, when DBWSBuilder is used to create a web service based on a PL/SQL Stored Function that returns a PL/SQL Record.

PL/SQL DDL and 'Shadow' JDBC Type DDL

The DDL used to create the PL/SQL Package containing the Record and Stored Function:

CREATE OR REPLACE PACKAGE EMP_RECORD_PACKAGE AS
    TYPE EmpRec IS RECORD (
        emp_id NUMBER(4),
        emp_name VARCHAR(25)
    );
    FUNCTION get_emp_record (pId IN NUMBER) RETURN EmpRec;
END EMP_RECORD_PACKAGE;

Oracle's JDBC drivers do not support PL/SQL types (Oracle FAQ), so it is required that a 'shadow' top-level (i.e. outside of the PL/SQL Package) JDBC type exists. In this case, the DDL for the PL/SQL Record's shadow JDBC type would be:

CREATE OR REPLACE TYPE EMP_RECORD_PACKAGE_EMPREC AS OBJECT (
    emp_id NUMBER(4),
    emp_name VARCHAR(25)
)

The expected naming convention for the shadow JDBC type is PL/SQL package name + "_" + PL/SQL record name. This is required as anonymous PL/SQL is executed on the database schema at runtime which is responsible for converting between the shadow JDBC type and the PL/SQL type; the JDBC type name isn't provided, so this assumption about the shadow type name is necessary. In this use case the PL/SQL record EMP_RECORD_PACKAGE.EMPREC would have an associated Object type named EMP_RECORD_PACKAGE_EMPREC.

The only PL/SQL type is the return argument, so a single PL/SQL function will be required to convert the PL/SQL Record to the shadow JDBC type before returning. Note that input PL/SQL types (IN or INOUT) would require conversion functions as well. The following block of anonymous PL/SQL code will be generated by PLSQLStoredProcedureCall for use at runtime:

DECLARE
  pIdTARGET NUMERIC := :1;
  RESULTTARGET EMP_RECORD_PACKAGE.EMPREC;
  FUNCTION EL_PL2SQL_0(aPlsqlItem EMP_RECORD_PACKAGE.EMPREC)
  RETURN EMP_RECORD_PACKAGE_EMPREC IS
    aSqlItem EMP_RECORD_PACKAGE_EMPREC;
  BEGIN
    aSqlItem := EMP_RECORD_PACKAGE_EMPREC(NULL, NULL);
    aSqlItem.emp_id := aPlsqlItem.emp_id;
    aSqlItem.emp_name := aPlsqlItem.emp_name;
    RETURN aSqlItem;
  END EL_PL2SQL_0;
BEGIN
  RESULTTARGET := EMP_RECORD_PACKAGE.get_emp_record(pId=>pIdTARGET);
  :2 := EL_PL2SQL_0(RESULTTARGET);
END;

DBWSBuilder generates create and drop DDL for any required shadow types. Here is an example of how the builder's shadow DDL can be used:

String[] args = ...
DBWSBuilder builder = new DBWSBuilder();
builder.start(args);
// execute shadow type ddl to generate JDBC equivalents of PL/SQL types
List<String> ddls = new ArrayList<String>();
for (String ddl : builder.getTypeDDL()) {
    try {
        PreparedStatement pStmt = builder.getConnection().prepareStatement(ddl);
        pStmt.execute();
    } catch (SQLException e) {
        // handle exception...
    }
}

Note that the drop DDL can be accessed via builder.getTypeDropDDL().

Configuring DBWSBuilder

Following is the XML passed to the Builder (assume the target platform is WebLogic, packaged as a .war):

<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <properties>
    <property name="projectName">PLSQLRecord</property>
    <property name="logLevel">off</property>
    <property name="username">SCOTT</property>
    <property name="password">TIGER</property>
    <property name="url">jdbc:oracle:thin:@localhost:1521:TOPLINK</property>
    <property name="driver">oracle.jdbc.OracleDriver</property>
    <property name="platformClassname">org.eclipse.persistence.platform.database.oracle.Oracle11Platform</property>
    <property name="dataSource">jdbc/EclipseLinkDS</property>
    <property name="wsdlLocationURI">http://localhost:7001/PLSQLRecord/PLSQLRecord?wsdl</property>
  </properties>
  <plsql-procedure name="TestRecWithPercentTypeField" catalogPattern="EMP_RECORD_PACKAGE" procedurePattern="get_emp_record"/>
</dbws-builder>

Alternatively, the builder can be configured via API as follows:

builder = new DBWSBuilder();
 
builder.setProjectName("PLSQLRecord");
builder.setLogLevel("off");
builder.setUsername("SCOTT");
builder.setPassword("TIGER");
builder.setUrl("jdbc:oracle:thin:@localhost:1521:TOPLINK");
builder.setDriver("oracle.jdbc.OracleDriver");
builder.setPlatformClassname("org.eclipse.persistence.platform.database.oracle.Oracle11Platform");
builder.setDataSource("jdbc/EclipseLinkDS");
builder.setWsdlLocationURI("http://localhost:7001/PLSQLRecord/PLSQLRecord?wsdl");
 
 
PLSQLProcedureOperationModel procOpModel = new PLSQLProcedureOperationModel();
procOpModel.setName("TestRecWithPercentTypeField");
procOpModel.setCatalogPattern("EMP_RECORD_PACKAGE");
procOpModel.setProcedurePattern("get_emp_record");
builder.addOperation(procOpModel);
 
builder.start();

Retrieving DDL and executing the DDLParser

The DDL for SCOTT.EMP_RECORD_PACKAGE would be extracted as follows:

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE', AO.OBJECT_NAME) AS RESULT FROM ALL_OBJECTS AO WHERE (REGEXP_LIKE(OWNER,?)
AND AO.STATUS = 'VALID' AND AO.OBJECT_TYPE = ? AND AO.OBJECT_NAME LIKE ?)

Which results in:

CREATE OR REPLACE PACKAGE "SCOTT"."EMP_RECORD_PACKAGE" AS
    type EmpRec is record (emp_id NUMBER(4), emp_name VARCHAR(25));
    function get_emp_record (pId in number) return EmpRec;
END EMP_RECORD_PACKAGE;

Once the DDL is obtained, the DDLParser can be used to generate the meta-model objects based on the DDL string:

String ddlString;
// set ddlString ...
 
DDLParser parser = new DDLParser(new StringReader(ddlString));
PLSQLPackageType packageType = parser.parsePLSQLPackage();
List<FunctionType> functions = packageType.getProcedures();
// iterate through list and locate the function by name
List<PLSQLType> types = packageType.getTypes();
// iterate through list and locate the record by name

JAXB Metadata

XmlBindingsGenerator will generate the following JAXB Metadata (one <xml-bindings> per package); note the xml-type and xml-root-element names refer to the shadow JDBC type name:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xml-bindings-list xmlns="http://www.eclipse.org/eclipselink/xsds/persistence/oxm">
    <xml-bindings package-name="emp_record_package">
        <xml-schema element-form-default="QUALIFIED" namespace="urn:PLSQLRecord">
            <xml-ns namespace-uri="http://www.w3.org/2001/XMLSchema-instance" prefix="xsi"/>
        </xml-schema>
        <java-types>
            <java-type name="emprec" xml-accessor-type="FIELD">
                <xml-type name="EMP_RECORD_PACKAGE_EMPREC" namespace="urn:PLSQLRecord"/>
                <xml-root-element name="EMP_RECORD_PACKAGE_EMPREC" namespace="urn:PLSQLRecord"/>
                <java-attributes>
                    <xml-element required="true" type="java.math.BigDecimal" xml-path="emp_id/text()" java-attribute="emp_id">
                        <xml-schema-type name="decimal"/>
                    </xml-element>
                    <xml-element required="true" type="java.lang.String" xml-path="emp_name/text()" java-attribute="emp_name">
                        <xml-schema-type name="string"/>
                    </xml-element>
                </java-attributes>
            </java-type>
        </java-types>
    </xml-bindings>
</xml-bindings-list>

JPA Metadata

XmlEntityMappingsGenerator will generate <named-plsql-stored-function-query>, <plsql-record>, and <embeddable> JPA metadata elements as follows; note the compatible-type attribute on the <plsql-record> element which refers to the shadow JDBC type:

<?xml version="1.0" encoding="UTF-8"?>
<orm:entity-mappings 
      xsi:schemaLocation="http://www.eclipse.org/eclipselink/xsds/persistence/orm org/eclipse/persistence/jpa/eclipselink_orm_2_5.xsd" 
      xmlns:orm="http://www.eclipse.org/eclipselink/xsds/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <orm:named-plsql-stored-function-query name="TestRecWithPercentTypeField" function-name="EMP_RECORD_PACKAGE.get_emp_record">
      <orm:parameter direction="IN" name="pId" database-type="NUMERIC_TYPE"/>
      <orm:return-parameter name="RESULT" database-type="EMP_RECORD_PACKAGE.EMPREC"/>
   </orm:named-plsql-stored-function-query>
   <orm:plsql-record name="EMP_RECORD_PACKAGE.EMPREC" compatible-type="EMP_RECORD_PACKAGE_EMPREC" java-type="emp_record_package.emprec">
      <orm:field name="emp_id" database-type="NUMERIC_TYPE"/>
      <orm:field name="emp_name" database-type="VARCHAR_TYPE"/>
   </orm:plsql-record>
   <orm:embeddable class="emp_record_package.emprec" access="VIRTUAL">
      <orm:struct name="EMP_RECORD_PACKAGE_EMPREC">  -- required for field ordering
         <orm:field>emp_id</orm:field>
         <orm:field>emp_name</orm:field>
      </orm:struct>
      <orm:attributes>
         <orm:basic name="emp_id" attribute-type="java.math.BigInteger">
            <orm:column name="emp_id"/>
         </orm:basic>
         <orm:basic name="emp_name" attribute-type="java.lang.String">
            <orm:column name="emp_name"/>
         </orm:basic>
      </orm:attributes>
   </orm:embeddable>
</orm:entity-mappings>

XML Schema

The generated XML schema's elements and types are referenced by the DBWS service file, the JAXB metadata file, and the WSDL; note that the element and type names refer to the shadow JDBC type name, and not the PL/SQL record name:

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:PLSQLRecord" 
        xmlns="urn:PLSQLRecord" elementFormDefault="qualified">
   <xsd:complexType name="EMP_RECORD_PACKAGE_EMPREC">
      <xsd:sequence>
         <xsd:element name="emp_id" type="xsd:decimal" nillable="true"/>
         <xsd:element name="emp_name" type="xsd:string" nillable="true"/>
      </xsd:sequence>
   </xsd:complexType>
   <xsd:element name="EMP_RECORD_PACKAGE_EMPREC" type="EMP_RECORD_PACKAGE_EMPREC"/>
</xsd:schema>

Service XML

The DBWS runtime code (a.k.a. X-R) makes use of 'operations' - QueryOperation, InsertOperation, UpdateOperation and DeleteOperation. These operations are executable representations of SELECT, INSERT, UPDATE and DELETE database operations. Each is responsible for looking up it's database query on the session, validating and executing it. A service XML file is unmarshalled at runtime in order to build these operations. In this case, the service XML would be:

<?xml version="1.0" encoding="UTF-8"?>
<dbws xmlns:ns1="urn:PLSQLRecord" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <name>PLSQLRecord</name>
   <sessions-file>eclipselink-dbws-sessions.xml</sessions-file>
   <query>
      <name>TestRecWithPercentTypeField</name>
      <parameter>
         <name>pId</name>
         <type>xsd:decimal</type>
      </parameter>
      <result>
         <type>ns1:EMP_RECORD_PACKAGE_EMPREC</type>
      </result>
      <named-query>
         <name>TestRecWithPercentTypeField</name>
      </named-query>
   </query>
</dbws>

Each operation will have an associated <operation> entry in the generated WSDL - the association is based on the query name.

WSDL

The following WSDL will be generated:

<?xml version="1.0" encoding="UTF-8"?>
<wsdl:definitions name="PLSQLRecordService" targetNamespace="urn:PLSQLRecordService" xmlns:ns1="urn:PLSQLRecord" 
      xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:tns="urn:PLSQLRecordService" 
      xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/">
  <wsdl:types>
    <xsd:schema xmlns:tns="urn:PLSQLRecordService" xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
            targetNamespace="urn:PLSQLRecordService" elementFormDefault="qualified">
      <xsd:import schemaLocation="eclipselink-dbws-schema.xsd" namespace="urn:PLSQLRecord"/>
      <xsd:complexType name="TestRecWithPercentTypeFieldRequestType">
        <xsd:sequence>
          <xsd:element name="pId" type="xsd:decimal"/>
        </xsd:sequence>
      </xsd:complexType>
      <xsd:complexType name="TestRecWithPercentTypeFieldResponseType">
         <xsd:sequence>
           <xsd:element name="result">
             <xsd:complexType>
               <xsd:sequence>
                 <xsd:element ref="ns1:EMP_RECORD_PACKAGE_EMPREC" minOccurs="0"/>
               </xsd:sequence>
             </xsd:complexType>
           </xsd:element>
         </xsd:sequence>
       </xsd:complexType>
       <xsd:element name="TestRecWithPercentTypeFieldResponse" type="tns:TestRecWithPercentTypeFieldResponseType"/>
       <xsd:element name="TestRecWithPercentTypeField" type="tns:TestRecWithPercentTypeFieldRequestType"/>
    </xsd:schema>
  </wsdl:types>
  <wsdl:message name="TestRecWithPercentTypeFieldResponse">
    <wsdl:part name="TestRecWithPercentTypeFieldResponse" element="tns:TestRecWithPercentTypeFieldResponse"/>
  </wsdl:message>
  <wsdl:message name="TestRecWithPercentTypeFieldRequest">
    <wsdl:part name="TestRecWithPercentTypeFieldRequest" element="tns:TestRecWithPercentTypeField"/>
  </wsdl:message>
  <wsdl:portType name="PLSQLRecordService_Interface">
    <wsdl:operation name="TestRecWithPercentTypeField">
      <wsdl:input message="tns:TestRecWithPercentTypeFieldRequest"/>
      <wsdl:output message="tns:TestRecWithPercentTypeFieldResponse"/>
    </wsdl:operation>
  </wsdl:portType>
  <wsdl:binding name="PLSQLRecordService_SOAP_HTTP" type="tns:PLSQLRecordService_Interface">
    <soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>
    <wsdl:operation name="TestRecWithPercentTypeField">
      <soap:operation soapAction="urn:PLSQLRecordService:TestRecWithPercentTypeField"/>
      <wsdl:input><soap:body use="literal"/></wsdl:input>
      <wsdl:output><soap:body use="literal"/></wsdl:output>
    </wsdl:operation>
  </wsdl:binding>
  <wsdl:service name="PLSQLRecordService">
    <wsdl:port name="PLSQLRecordServicePort" binding="tns:PLSQLRecordService_SOAP_HTTP">
      <soap:address location="http://localhost:7001/PLSQLRecord/PLSQLRecord?wsdl"/>
    </wsdl:port>
  </wsdl:service>
</wsdl:definitions>

.war file packaging

Assuming that the target packaging is .war, the generated artifacts would be archived like so:

 \--- root of war file
      |
      \---web-inf
          |   web.xml
          |
          +---classes
          |   +---META-INF
          |   |    eclipselink-dbws.xml            -- service file
          |   |    eclipselink-dbws-sessions.xml -- name can be overridden by <sessions-file> entry in eclipselink-dbws.xml
          |   |    eclipselink-dbws-or.xml
          |   |    eclipselink-dbws-ox.xml
          |   |
          |   +---_dbws
          |   |    DBWSProvider.class        -- ASM-generated javax.xml.ws.Provider
          |   |    DBWSProvider.java         -- (source provided as a convenience for IDE integration)
          |   |    ProviderListener.class     -- ASM-generated javax.servlet.ServletContextListener
          |   |    ProviderListener.java      -- (source provided as a convenience for IDE integration)
          |   |
          \---wsdl
                 swaref.xsd                        -- optional to handle attachments
                 eclipselink-dbws.wsdl
                 eclipselink-dbws-schema.xsd

Example SOAP request message

A valid SOAP request message would look like:

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
  <env:Header/>
  <env:Body>
    <TestRecWithPercentTypeField xmlns="urn:PLSQLRecordService">
      <pId>66</pId>
    </TestRecWithPercentTypeField>
  </env:Body>
</env:Envelope>

DBWSProvider receives the SOAP request message and passes it to the DBWS runtime (ProviderHelper). The body of the SOAP message is unmarshalled to an Invocation instance, whose responsibility is to store query names and their parameters. The X-R operation with name matching the invocation name is retrieved, any parameters set, then the underlying EclipseLink query is executed. The result of the query is converted into a SOAPMessage, which is returned to the caller.