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

EclipseLink/Examples/DBWS/EndToEndPLSQLRecord

< EclipseLink‎ | Examples‎ | DBWS
Revision as of 10:52, 24 June 2013 by David.mccann.oracle.com (Talk | contribs) (DBWS Service based on a PL/SQL Stored Function returning a PL/SQL Record)

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.

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 case, 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. The following block of anonymous PL/SQL code will be generated:

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;

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">simpleplsqlsp</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/simpleplsqlsp/simpleplsqlsp?wsdl</property>
  </properties>
  <plsql-procedure name="TestRecWithPercentTypeField" catalogPattern="EMP_RECORD_PACKAGE" procedurePattern="get_emp_record"/>
</dbws-builder>

Based on the provided Builder XML, 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 ?)
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


XmlBindingsGenerator will generate the following:

<?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-null-policy is-set-performed-for-absent-node="true" xsi-nil-represents-null="true"
                                  empty-node-represents-null="false" null-representation-for-xml="XSI_NIL"/>
                        <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-null-policy is-set-performed-for-absent-node="true" xsi-nil-represents-null="true"
                                  empty-node-represents-null="false" null-representation-for-xml="XSI_NIL"/>
                        <xml-schema-type name="string"/>
                    </xml-element>
                </java-attributes>
            </java-type>
        </java-types>
    </xml-bindings>
</xml-bindings-list>

XmlEntityMappingsGenerator will generate the following <named-plsql-stored-procedure-query> metadata as follows:

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

Note: the name attribute is configured through the builder on the associated operation model.

The XML schema generated would be:

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

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 associated request/response entries in the generated WSDL - the association is based on the operation name.

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="REPLACE_WITH_ENDPOINT_ADDRESS"/>
    </wsdl:port>
  </wsdl:service>
</wsdl:definitions>

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

Back to the top