Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
EclipseLink/Examples/DBWS/EndToEndPLSQLRecord
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 "DMCCANN"."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:part> </wsdl:message> <wsdl:message name="TestRecWithPercentTypeFieldRequest"> <wsdl:part name="TestRecWithPercentTypeFieldRequest" element="tns:TestRecWithPercentTypeField"> </wsdl:part> </wsdl:message> <wsdl:portType name="PLSQLRecordService_Interface"> <wsdl:operation name="TestRecWithPercentTypeField"> <wsdl:input message="tns:TestRecWithPercentTypeFieldRequest"> </wsdl:input> <wsdl:output message="tns:TestRecWithPercentTypeFieldResponse"> </wsdl:output> </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>