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:33, 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

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

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

Copyright © Eclipse Foundation, Inc. All Rights Reserved.