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.
Difference between revisions of "EclipseLink/Examples/DBWS/EndToEndPLSQLRecord"
(→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) |
||
Line 59: | Line 59: | ||
<property name="wsdlLocationURI">http://localhost:7001/simpleplsqlsp/simpleplsqlsp?wsdl</property> | <property name="wsdlLocationURI">http://localhost:7001/simpleplsqlsp/simpleplsqlsp?wsdl</property> | ||
</properties> | </properties> | ||
− | <plsql-procedure name=" | + | <plsql-procedure name="TestRecWithPercentTypeField" catalogPattern="EMP_RECORD_PACKAGE" procedurePattern="get_emp_record"/> |
</dbws-builder> | </dbws-builder> | ||
</source> | </source> | ||
Line 65: | Line 65: | ||
Based on the provided Builder XML, the DDL for SCOTT.SIMPLEPACKAGE.INOUTARGSPLSQLSP would be extracted as follows: | Based on the provided Builder XML, the DDL for SCOTT.SIMPLEPACKAGE.INOUTARGSPLSQLSP would be extracted as follows: | ||
<source lang="text"> | <source lang="text"> | ||
− | SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE', AO.OBJECT_NAME) AS RESULT FROM ALL_OBJECTS AO WHERE (REGEXP_LIKE(OWNER,?) | + | 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 ?) | + | AND AO.STATUS = 'VALID' AND AO.OBJECT_TYPE = ? AND AO.OBJECT_NAME LIKE ?) |
</source> | </source> | ||
<source lang="text"> | <source lang="text"> | ||
− | CREATE OR REPLACE PACKAGE " | + | CREATE OR REPLACE PACKAGE "DMCCANN"."EMP_RECORD_PACKAGE" AS |
− | + | type EmpRec is record (emp_id NUMBER(4), emp_name VARCHAR(25)); | |
− | END | + | function get_emp_record (pId in number) return EmpRec; |
+ | END EMP_RECORD_PACKAGE; | ||
</source> | </source> | ||
Revision as of 10:15, 24 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 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.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 "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<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.