Here is how the procedure/types should be handled in EclipseLink.
First, we need some model classes for the PL/SQL record types - very
simple:
{for demo purposes, I put everything in the
package 'test' but that can be changed easily ...}
package test;
import java.math.BigDecimal;
public class NestedRecord {
public String value01;
public BigDecimal value02;
public NestedRecord() {
}
}
...
public class Record {
public String column01;
public BigDecimal column02;
public NestedRecord nest01;
public Record() {
}
}
A simple test class shows mappings for each type as well as how to
build a query to execute the stored procedure.
Some of the key points:
- complex types that are defined in a PL/SQL package (records,
collections or any combination thereof)
require 'shadow' types that are defined in the 'global' JDBC
namespace outside of any PL/SQL package.
Thus for the record
"TEST_REC", we need the shadow type
"NEST_REC_PACKAGE_TEST_REC".
The
shadow type must have the same 'shape' - same number of fields, in
the same order, with the same names.
(Note the simple transform from the PL/SQL world to the JDBC world:
package name + "_" + record name).
- in the case of the stored procedure
"PROCEDURE_RECORD_CALL_NEST",
the record
record "TEST_REC" is used
twice, once for the
IN parameter
"INPUT" and
second for the
OUT parameter
"OUTPUT". However,
EclipseLink
needs separate instances of the
PLSQLrecord object
{other notes: all Oracle database artifacts -
type names, package names, etc. should always be
represented in
EclipseLink metadata in UPPERCASE}
...
//javase imports
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
//EclipseLink imports
import org.eclipse.persistence.logging.SessionLog;
import org.eclipse.persistence.mappings.DirectToFieldMapping;
import org.eclipse.persistence.mappings.structures.ObjectRelationalDataTypeDescriptor;
import org.eclipse.persistence.mappings.structures.StructureMapping;
import org.eclipse.persistence.platform.database.jdbc.JDBCTypes;
import org.eclipse.persistence.platform.database.oracle.Oracle11Platform;
import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLStoredProcedureCall;
import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLrecord;
import org.eclipse.persistence.queries.ValueReadQuery;
import org.eclipse.persistence.sessions.DatabaseLogin;
import org.eclipse.persistence.sessions.DatabaseSession;
import org.eclipse.persistence.sessions.DatasourceLogin;
import org.eclipse.persistence.sessions.Project;
public class NestedRecordTest {
static String username;
static String password;
static String url;
static final String DATABASE_USERNAME_KEY = "db.user";
static final String DATABASE_PASSWORD_KEY = "db.pwd";
static final String DATABASE_URL_KEY = "db.url";
static final String DEFAULT_DATABASE_USERNAME = "scott";
static final String DEFAULT_DATABASE_PASSWORD = "tiger";
static final String DEFAULT_DATABASE_DRIVER = "oracle.jdbc.OracleDriver";
static final String DEFAULT_DATABASE_URL = "jdbc:oracle:thin:@localhost:1521:ORCL";
static final String PACKAGE_NAME = "NEST_REC_PACKAGE";
static final String PROCEDURE_NAME = "PROCEDURE_RECORD_CALL_NEST";
static final String NEST_RECORD_TYPE_NAME = "TEST_NEST_REC";
static final String RECORD_TYPE_NAME = "TEST_REC";
static final String CREATE_PACKAGE =
"CREATE OR REPLACE PACKAGE " + PACKAGE_NAME + " AS\n" +
"TYPE " + NEST_RECORD_TYPE_NAME + " IS RECORD (\n" +
"VALUE01 VARCHAR2(10),\n" +
"VALUE02 NUMBER\n" +
");\n" +
"TYPE " + RECORD_TYPE_NAME + " IS RECORD (\n" +
"COLUMN01 VARCHAR2(10),\n" +
"COLUMN02 NUMBER,\n" +
"NEST01 " + NEST_RECORD_TYPE_NAME + " \n" +
");\n" +
"PROCEDURE " + PROCEDURE_NAME + "(\n" +
"INPUT IN " + RECORD_TYPE_NAME + ",\n" +
"OUTPUT OUT " + RECORD_TYPE_NAME + "\n" +
");\n" +
"END " + PACKAGE_NAME + ";";
static final String CREATE_BODY =
"CREATE OR REPLACE PACKAGE BODY " + PACKAGE_NAME + " AS\n" +
"PROCEDURE " + PROCEDURE_NAME + "(\n" +
"INPUT IN " + RECORD_TYPE_NAME + ",\n" +
"OUTPUT OUT " + RECORD_TYPE_NAME + ") AS\n" +
"BEGIN\n" +
"NULL;\n" +
"END " + PROCEDURE_NAME + ";\n" +
"END " + PACKAGE_NAME + ";";
static final String JDBC_SHADOW_TYPE1 =
"NEST_REC_PACKAGE_TEST_NEST_REC";
static final String JDBC_SHADOW_TYPE2 =
"NEST_REC_PACKAGE_TEST_REC";
static final String CREATE_JDBC_SHADOW_TYPE1 =
"CREATE OR REPLACE TYPE " + JDBC_SHADOW_TYPE1 + " AS OBJECT (\n" +
"VALUE01 VARCHAR2(10),\n" +
"VALUE02 NUMBER\n" +
")\n";
static final String CREATE_JDBC_SHADOW_TYPE2 =
"CREATE OR REPLACE TYPE " + JDBC_SHADOW_TYPE2 + " AS OBJECT (\n" +
"COLUMN01 VARCHAR2(10),\n" +
"COLUMN02 NUMBER,\n" +
"NEST01 " + JDBC_SHADOW_TYPE1 + " \n" +
")\n";
static final String DROP_PACKAGE =
"DROP PACKAGE NEST_REC_PACKAGE";
static final String DROP_JDBC_SHADOW_TYPE1 =
"DROP TYPE " + JDBC_SHADOW_TYPE1;
static final String DROP_JDBC_SHADOW_TYPE2 =
"DROP TYPE " + JDBC_SHADOW_TYPE2;
static DatabaseSession ds = null;
public static void main(String...args) {
username = System.getProperty(DATABASE_USERNAME_KEY, DEFAULT_DATABASE_USERNAME);
password = System.getProperty(DATABASE_PASSWORD_KEY, DEFAULT_DATABASE_PASSWORD);
url = "" style="color:rgb(127, 0, 85);font-weight:bold">System.getProperty(DATABASE_URL_KEY, DEFAULT_DATABASE_URL);
DatasourceLogin login = new DatabaseLogin();
login.setUserName(username);
login.setPassword(password);
((DatabaseLogin)login).setConnectionString(url);
((DatabaseLogin)login).setDriverClassName(DEFAULT_DATABASE_DRIVER);
login.setDatasourcePlatform(new Oracle11Platform());
((DatabaseLogin)login).bindAllParameters();
Project p = new Project(login);
ObjectRelationalDataTypeDescriptor recordDescriptor = new ObjectRelationalDataTypeDescriptor();
recordDescriptor.descriptorIsAggregate();
recordDescriptor.setJavaClass(test.Record.class);
recordDescriptor.setAlias("Record");
recordDescriptor.setStructureName(JDBC_SHADOW_TYPE2);
DirectToFieldMapping column01Mapping = new DirectToFieldMapping();
column01Mapping.setAttributeName("column01");
column01Mapping.setFieldName("COLUMN01");
recordDescriptor.addMapping(column01Mapping);
DirectToFieldMapping column02Mapping = new DirectToFieldMapping();
column02Mapping.setAttributeName("column02");
column02Mapping.setFieldName("COLUMN02");
recordDescriptor.addMapping(column02Mapping);
StructureMapping nest01Mapping = new StructureMapping();
nest01Mapping.setAttributeName("nest01");
nest01Mapping.setFieldName("NEST01");
nest01Mapping.setReferenceClass(test.NestedRecord.class);
recordDescriptor.addMapping(nest01Mapping);
p.addDescriptor(recordDescriptor);
ObjectRelationalDataTypeDescriptor nestedRecordDesc = new ObjectRelationalDataTypeDescriptor();
nestedRecordDesc.descriptorIsAggregate();
nestedRecordDesc.setJavaClass(test.NestedRecord.class);
nestedRecordDesc.setAlias("NestedRecord");
nestedRecordDesc.setStructureName(JDBC_SHADOW_TYPE1);
DirectToFieldMapping value01Mapping = new DirectToFieldMapping();
value01Mapping.setAttributeName("value01");
value01Mapping.setFieldName("VALUE01");
nestedRecordDesc.addMapping(value01Mapping);
DirectToFieldMapping value02Mapping = new DirectToFieldMapping();
value02Mapping.setAttributeName("value02");
value02Mapping.setFieldName("VALUE02");
nestedRecordDesc.addMapping(value02Mapping);
p.addDescriptor(nestedRecordDesc);
ds = p.createDatabaseSession();
ds.setLogLevel(SessionLog.FINE);
ds.login();
try {
ds.executeNonSelectingSQL(CREATE_PACKAGE);
ds.executeNonSelectingSQL(CREATE_BODY);
ds.executeNonSelectingSQL(CREATE_JDBC_SHADOW_TYPE1);
ds.executeNonSelectingSQL(CREATE_JDBC_SHADOW_TYPE2);
}
catch (Exception e) {
// ignore
}
testNestedRecordExecution();
try {
ds.executeNonSelectingSQL(DROP_PACKAGE);
ds.executeNonSelectingSQL(DROP_JDBC_SHADOW_TYPE2);
ds.executeNonSelectingSQL(DROP_JDBC_SHADOW_TYPE1);
}
catch (Exception e) {
// ignore
}
ds.logout();
}
public static void testNestedRecordExecution() {
PLSQLrecord recordNest = new PLSQLrecord();
recordNest.setTypeName(PACKAGE_NAME + "." + NEST_RECORD_TYPE_NAME);
recordNest.setCompatibleType(JDBC_SHADOW_TYPE1);
recordNest.setJavaType(NestedRecord.class);
recordNest.addField("VALUE01", JDBCTypes.VARCHAR_TYPE);
recordNest.addField("VALUE02", JDBCTypes.NUMERIC_TYPE);
// when a PLSQLrecord (or PLSQLcollection) databaseTypes is re-used across
// the arguments, need separate instances - and that includes nested databaseTypes
PLSQLrecord inRecord = new PLSQLrecord();
inRecord.setTypeName(PACKAGE_NAME + "." + RECORD_TYPE_NAME);
inRecord.setCompatibleType(JDBC_SHADOW_TYPE2);
inRecord.setJavaType(Record.class);
inRecord.addField("COLUMN01", JDBCTypes.VARCHAR_TYPE, 10);
inRecord.addField("COLUMN02", JDBCTypes.NUMERIC_TYPE);
inRecord.addField("NEST01", recordNest);
PLSQLrecord outRecord = new PLSQLrecord();
outRecord.setTypeName(PACKAGE_NAME + "." + RECORD_TYPE_NAME);
outRecord.setCompatibleType(JDBC_SHADOW_TYPE2);
outRecord.setJavaType(Record.class);
outRecord.addField("COLUMN01", JDBCTypes.VARCHAR_TYPE, 10);
outRecord.addField("COLUMN02", JDBCTypes.NUMERIC_TYPE);
outRecord.addField("NEST01", recordNest.clone());
PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
call.setProcedureName(PACKAGE_NAME + "." + PROCEDURE_NAME);
call.addNamedArgument("INPUT", inRecord);
call.addNamedOutputArgument("OUTPUT", outRecord);
ValueReadQuery query = new ValueReadQuery();
query.addArgument("INPUT", Record.class);
query.setCall(call);
query.bindAllParameters();
NestedRecord nRec = new NestedRecord();
nRec.value01 = "nestedtest";
nRec.value02 = new BigDecimal("123.456");
Record rec = new Record();
rec.column01 = "test";
rec.column02 = new BigDecimal("789.012");
rec.nest01 = nRec;
List<Object> args = new ArrayList<Object>();
args.add(rec);
ds.executeQuery(query, args);
}
}
Hope this helps,
---
oratta
wrote:
We
try
to call a procedure as follow,
PROCEDURE procedure_record_call_nest(
input IN test_rec,
output OUT test_rec)
|
The procedure has two arguments of 'test_rec' as follow
TYPE test_nest_rec IS RECORD (
value01 varchar2(10),
value02 number);
TYPE test_rec IS RECORD (
column01 varchar2(10),
column02 number,
nest01 test_nest_rec);
And, we try to call the procedure with follow code
PLSQLrecord recordNest = new PLSQLrecord();
recordNest.setTypeName("test_pkg.test_nest_rec");
recordNest.setJavaType(TestNestRecDTO.class);
recordNest.addField("value01", JDBCTypes.VARCHAR_TYPE);
recordNest.addField("value02", JDBCTypes.NUMERIC_TYPE);
PLSQLrecord record = new PLSQLrecord();
record.setTypeName("test_pkg.test_rec");
record.setJavaType(TestRecDTO.class);
record.addField("column01", JDBCTypes.VARCHAR_TYPE);
record.addField("column02", JDBCTypes.NUMERIC_TYPE);
record.addField("nest01", recordNest);
PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
call.setProcedureName("test_pkg.procedure_record_call_nest");
call.addNamedArgument("input", record);
call.addNamedOutputArgument("output", record);
DataReadQuery query = new DataReadQuery();
query.addArgument("column01");
query.addArgument("column02");
query.addArgument("nest01");
query.setCall(call);
query.setResultType(DataReadQuery.ARRAY);
List attributes = new ArrayList();
attributes.add("tESt");
attributes.add(1);
List nestbutes = new ArrayList();
nestbutes.add("TesT");
nestbutes.add(1);
attributes.add(nestbutes);
List returnObject = (List)JpaHelper.getEntityManager(em).getActiveSession().executeQuery(query, attributes);
However we faced some error:
Caused by: oracle.oc4j.rmi.OracleRemoteException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 1.2.0.v20091016-r5565): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: 列の型が無効です。
Error Code: 17004
Call:
DECLARE
input_TARGET xxpoc_test_pkg.test003_rec;
output_TARGET xxpoc_test_pkg.test003_rec;
BEGIN
input_TARGET.column01 := :1;
input_TARGET.column02 := :2;
input_TARGET.nest01 := :3;
xxpoc_test_pkg.procedure_record_call_nest(input=>input_TARGET, output=>output_TARGET);
:4 := output_TARGET.column01;
:5 := output_TARGET.column02;
:6 := output_TARGET.nest01;
END;
bind => [:1 => tESt, :2 => 1, , , column01 => :4, column02 => :5, , ]
How can I do this?
Sorry for my poor english.
best regards
