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 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.