[eclipselink-users] Manual generation of Anonymous PL/SQL block

[oratta <oratta@xxxxxxxxx>]

Hi, 

We use Eclipselink1.2.0. We have some problem for calling PL/SQL stored
programs using PLSQLStoredProcedureCall and StoredFunctionCall.

1. We can't call a procedure which has a long named parameter as follow
PROCEDURE test_argument(
 abcdefghijklmnopqrstuvwxyz IN NUMBER
);

2. We have to use NUMERIC type instead of NUMBER type.

3. We cannot use BOOLEAN type in complex type. Because it cannot create a
compatible type.

4. We cannot use Oracle type(Non-JDBC type) at StoredFunctionCall

So, we try to use manual generation of anonymous PL/SQL block. Bud, in our
test program, we cannot assign some argument value to a PL/SQL block...How
can We call it?

[PL/SQL procedure]
PROCEDURE procedure_record_call_two(
			in1 IN test001_rec,
			in2 IN test001_rec,
			out1 OUT test001_rec,
			out2 OUT test001_rec
   				) AS
BEGIN
	out1.column01 := UPPER(in1.column01);
	out1.column02 := in1.column02 * 10;
	out2.column01 := LOWER(in2.column01);
	out2.column02 := in2.column02 * 100;
END procedure_record_call_two;

[Java call]
        ObjectRelationalDataTypeDescriptor nestedRecordDesc = new
ObjectRelationalDataTypeDescriptor();
        nestedRecordDesc.descriptorIsAggregate();
        nestedRecordDesc.setJavaClass(PLSQLHelperTestRecDTO1.class);
        nestedRecordDesc.setAlias("Record");
        nestedRecordDesc.setStructureName("XXZ_TEST001_REC");
        DirectToFieldMapping value01Mapping = new DirectToFieldMapping();
        value01Mapping.setAttributeName("column01");
        value01Mapping.setFieldName("COLUMN01");
        nestedRecordDesc.addMapping(value01Mapping);
        DirectToFieldMapping value02Mapping = new DirectToFieldMapping();
        value02Mapping.setAttributeName("column02");
        value02Mapping.setFieldName("COLUMN02");
        nestedRecordDesc.addMapping(value02Mapping);
       
JpaHelper.getEntityManager(em).getServerSession().addDescriptor(nestedRecordDesc);
    
        DataReadQuery query = new DataReadQuery();
        SQLCall sqlCall = new SQLCall();
        sqlCall.setQueryString(
       "DECLARE\n" + 
       "  in1_TARGET XXZ_TEST_PKG.TEST001_REC;\n" + 
       "  in1_COMPAT XXZ_TEST001_REC := #1;\n" + 
       "  in2_TARGET XXZ_TEST_PKG.TEST001_REC;\n" + 
       "  in2_COMPAT XXZ_TEST001_REC := #2;\n" + 
       "  out1_TARGET XXZ_TEST_PKG.TEST001_REC;\n" + 
       "  out2_TARGET XXZ_TEST_PKG.TEST001_REC;\n" + 
       "  FUNCTION EL_SQL2PL_1(aSqlItem XXZ_TEST001_REC) \n" + 
       "  RETURN XXZ_TEST_PKG.TEST001_REC IS\n" + 
       "    aPlsqlItem XXZ_TEST_PKG.TEST001_REC;\n" + 
       "  BEGIN\n" + 
       "    aPlsqlItem.COLUMN01 := aSqlItem.COLUMN01;\n" + 
       "    aPlsqlItem.COLUMN02 := aSqlItem.COLUMN02;\n" + 
       "    RETURN aPlsqlItem;\n" + 
       "  END EL_SQL2PL_1;\n" + 
       "  FUNCTION EL_PL2SQL_0(aPlsqlItem XXZ_TEST_PKG.TEST001_REC)\n" + 
       "  RETURN XXZ_TEST001_REC IS\n" + 
       "    aSqlItem XXZ_TEST001_REC;\n" + 
       "  BEGIN\n" + 
       "    aSqlItem := XXZ_TEST001_REC(NULL, NULL);\n" + 
       "    aSqlItem.COLUMN01 := aPlsqlItem.COLUMN01;\n" + 
       "    aSqlItem.COLUMN02 := aPlsqlItem.COLUMN02;\n" + 
       "    RETURN aSqlItem;\n" + 
       "  END EL_PL2SQL_0;\n" + 
       "BEGIN\n" + 
       "  in1_TARGET := EL_SQL2PL_1(in1_COMPAT);\n" + 
       "  in2_TARGET := EL_SQL2PL_1(in2_COMPAT);\n" + 
       "  XXZ_TEST_PKG.PROCEDURE_RECORD_CALL_TWO(in1=>in1_TARGET,
in2=>in2_TARGET, out1=>out1_TARGET, out2=>out2_TARGET);\n" + 
       "  ###3 := EL_PL2SQL_0(out1_TARGET);\n" + 
       "  ###4 := EL_PL2SQL_0(out2_TARGET);\n" + 
       "END;"
        );
        PLSQLHelperTestRecDTO1 inDto1 = new PLSQLHelperTestRecDTO1("test1",
10L);
        PLSQLHelperTestRecDTO1 inDto2 = new PLSQLHelperTestRecDTO1("Test2",
20L);
        sqlCall.setQuery(query);
        sqlCall.setCustomSQLArgumentType("1",PLSQLHelperTestRecDTO1.class);
        sqlCall.setCustomSQLArgumentType("2",PLSQLHelperTestRecDTO1.class);
        sqlCall.setCustomSQLArgumentType("3",PLSQLHelperTestRecDTO1.class);
        sqlCall.setCustomSQLArgumentType("4",PLSQLHelperTestRecDTO1.class);
        query.setCall(sqlCall);
        query.addArgument("1");
        query.addArgument("2");
        NonSynchronizedVector foo = new NonSynchronizedVector();
        foo.add(inDto1);
        foo.add(inDto2);
        return
JpaHelper.getEntityManager(em).getActiveSession().executeQuery(query,foo);

[Error Message]
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
  in1_TARGET XXZ_TEST_PKG.TEST001_REC;
  in1_COMPAT XXZ_TEST001_REC := ?;
  in2_TARGET XXZ_TEST_PKG.TEST001_REC;
  in2_COMPAT XXZ_TEST001_REC := ?;
  out1_TARGET XXZ_TEST_PKG.TEST001_REC;
  out2_TARGET XXZ_TEST_PKG.TEST001_REC;
  FUNCTION EL_SQL2PL_1(aSqlItem XXZ_TEST001_REC) 
  RETURN XXZ_TEST_PKG.TEST001_REC IS
    aPlsqlItem XXZ_TEST_PKG.TEST001_REC;
  BEGIN
    aPlsqlItem.COLUMN01 := aSqlItem.COLUMN01;
    aPlsqlItem.COLUMN02 := aSqlItem.COLUMN02;
    RETURN aPlsqlItem;
  END EL_SQL2PL_1;
  FUNCTION EL_PL2SQL_0(aPlsqlItem XXZ_TEST_PKG.TEST001_REC)
  RETURN XXZ_TEST001_REC IS
    aSqlItem XXZ_TEST001_REC;
  BEGIN
    aSqlItem := XXZ_TEST001_REC(NULL, NULL);
    aSqlItem.COLUMN01 := aPlsqlItem.COLUMN01;
    aSqlItem.COLUMN02 := aPlsqlItem.COLUMN02;
    RETURN aSqlItem;
  END EL_PL2SQL_0;
BEGIN
  in1_TARGET := EL_SQL2PL_1(in1_COMPAT);
  in2_TARGET := EL_SQL2PL_1(in2_COMPAT);
  XXZ_TEST_PKG.PROCEDURE_RECORD_CALL_TWO(in1=>in1_TARGET, in2=>in2_TARGET,
out1=>out1_TARGET, out2=>out2_TARGET);
  ? := EL_PL2SQL_0(out1_TARGET);
  ? := EL_PL2SQL_0(out2_TARGET);
END;
	bind => [test.jp.co.y01.z0.dao.util.test.PLSQLHelperTestRecDTO1@123446d,
test.jp.co.y01.z0.dao.util.test.PLSQLHelperTestRecDTO1@6156d8, => 3, => 4]
-- 
View this message in context: http://old.nabble.com/Manual-generation-of-Anonymous-PL-SQL-block-tp28372467p28372467.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.