PL/SQL Tables in Eclipselink
We have pl/sql type like : "create or replace TYPE GUID_ARRAY AS TABLE OF RAW(16)"
And we use this type to pass as arguments to some functions and is also returned in some cases. Like:
FUNCTION get_guids(p_guid VARCHAR2) RETURN MGMT_TARGET_GUID_ARRAY;
PROCEDURE get_count(p_guid_array in GUID_ARRAY,p_count out NUMBER );
I have defined a VARRAY (PLSQL_GUID_ARRAY), as it looks to me that TABLE type is not supported.
I am trying to invoke the procedure using the following code snippet, but i have not been successful so far:
PLSQLCollection collection = new PLSQLCollection();
collection.setTypeName("GUID_ARRAY");
collection.setCompatibleType("PLSQL_GUID_ARRAY");
collection.setNestedType(JDBCTypes.VARCHAR_TYPE);
PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
call.setProcedureName("get_count");
call.addNamedArgument("p_guid_array", collection);
call.addNamedOutputArgument("p_count", JDBCTypes.NUMERIC_TYPE);
DataReadQuery query = new DataReadQuery ();
query.addArgument("p_guid_array", java.sql.Array.class);
query.setCall(call);
List params = new ArrayList();
params.add(new String[]{"ABC9876543221ABC"}); //-----------------------------------A
List mylist= (List)session.executeQuery(query,params);
DatabaseRecord record = (DatabaseRecord) mylist.get(0);
It looks to me that there is some issue with the step marked 'A' - how do i pass the values?
Also, i am not exactly sure how PLSQLRecord can be used.
(I did have a look at some samples in here http://dev.eclipse.org/svnroot/rt/org.eclipse.persistence/trunk/foundation/eclipselink.core.test/src/org/eclipse/persistence/testing/tests/plsql/
but havent got any progress).