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"
(New page: == title ==) |
|||
Line 1: | Line 1: | ||
− | == | + | == 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 14: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.