Jump to: navigation, search

Difference between revisions of "EclipseLink/Examples/DBWS/EndToEndPLSQLRecord"

(New page: == title ==)
 
Line 1: Line 1:
== title ==
+
== 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 follows:
 +
<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>
 +
 
 +
 
 +
 
 +
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">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>
 +
</source>
 +
 
 +
Based on the provided Builder XML, the DDL for SCOTT.SIMPLEPACKAGE.INOUTARGSPLSQLSP 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>
 +
 
 +
<source lang="text">
 +
CREATE OR REPLACE PACKAGE "SCOTT"."SIMPLEPACKAGE" AS
 +
  PROCEDURE INOUTARGSPLSQLSP(T IN VARCHAR, U OUT VARCHAR, V OUT NUMERIC);
 +
END SIMPLEPACKAGE;
 +
</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<ProcedureType> procedures = packageType.getProcedures();
 +
// iterate through list and locate the proc by name
 +
</source>
 +
 
 +
<code>XmlEntityMappingsGenerator</code> will generate the following <code><named-plsql-stored-procedure-query></code> metadata as follows:
 +
<source lang="xml">
 +
<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>
 +
</source>
 +
Note:  the <code>name</code> attribute is configured through the builder on the associated operation model.

Revision as of 13:27, 20 June 2013

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

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;


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.