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 14:36, 20 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 JAX-WS 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)
)

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="InOutArgsTest" catalogPattern="SIMPLEPACKAGE" procedurePattern="INOUTARGSPLSQLSP"/> 
</dbws-builder>

Based on the provided Builder XML, the DDL for SCOTT.SIMPLEPACKAGE.INOUTARGSPLSQLSP 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"."SIMPLEPACKAGE" AS
  PROCEDURE INOUTARGSPLSQLSP(T IN VARCHAR, U OUT VARCHAR, V OUT NUMERIC);
END SIMPLEPACKAGE;

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<ProcedureType> procedures = packageType.getProcedures();
// iterate through list and locate the proc by name

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

<orm:named-plsql-stored-procedure-query name="InOutArgsTest" procedure-name="SIMPLEPACKAGE.INOUTARGSPLSQLSP">
   <orm:parameter direction="IN" name="T" database-type="VARCHAR_TYPE"/>
   <orm:parameter direction="OUT" name="U" database-type="VARCHAR_TYPE"/>
   <orm:parameter direction="OUT" name="V" database-type="NUMERIC_TYPE"/>
</orm:named-plsql-stored-procedure-query>

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

Back to the top