View source for EclipseLink/Examples/DBWS/EndToEndPLSQLRecord

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:
<source lang="text">
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;
</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:

<source lang="text">
CREATE OR REPLACE TYPE EMP_RECORD_PACKAGE_EMPREC AS OBJECT (
    emp_id NUMBER(4),
    emp_name VARCHAR(25)
)
</source>

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):
<source lang="xml">
<?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>
</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>

=== Retrieving DDL and executing the DDLParser ===
The DDL for SCOTT.EMP_RECORD_PACKAGE would be extracted as follows:
<source lang="text">
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 ?)
</source>

Which results in:
<source lang="text">
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;
</source>

Once the DDL is obtained, the DDLParser can be used to generate the meta-model objects based on the DDL string:
<source lang="java">
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
</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>

=== Example SOAP request message ===
A valid SOAP request message would look like:
<source lang="xml">
<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>
</source>
<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.