JDBC query VARRAY on DB level

 create or replace  type rec_def as object (
      pvtrsid int
     );

//不能声明成record,只有局部的类型(sp 中)才可以声明record

 

  create or replace type my_table_type is table of rec_def; 

 

  declare
      rec rec_def;
      arr my_table_type;
     begin
      rec := rec_def(1);
       arr := my_table_type();
       arr.extend();
      arr(1) := rec;
     end;
=======jdbc

public List<AssetPriceRecon> varryQuery() throws Exception {
  Object obj  = jdbcTemplate.execute(new CallableStatementCreator() {
   
   @Override
   public CallableStatement createCallableStatement(Connection con)
     throws SQLException {
    CallableStatement cs = con.prepareCall("{call pkg_trustee.varrayQuery(?)}");
    cs.registerOutParameter(1,
      oracle.jdbc.OracleTypes.ARRAY, "MY_TABLE_TYPE");
    return cs;
   }
  }, new CallableStatementCallback() {
   
   @Override
   public Object doInCallableStatement(CallableStatement cs)
     throws SQLException, DataAccessException {
    // TODO Auto-generated method stub
    cs.execute();
    Array array = cs.getArray(1);
    
    return array;
   }
  });
  if (obj == null)
   return null;
  ARRAY rs = (ARRAY)obj;
  Datum [] data = rs.getOracleArray();
  ResultSet r = rs.getResultSet();
  while (r.next()) {
   System.out.println(r.getInt(1));
  }
  for (Datum da : data) {
   STRUCT st = (STRUCT)da;
   Map m = st.getMap();
   Object [] o = st.getAttributes();
   System.out.println(st.getMap());
  }
  
  return null;
 }====================

 

Hi I have a PL/SQL procedure which takes in a nested table inout parameter -
create or replace PROCEDURE XL_SP_ASSIGN_EVENTLIST (eventIDList IN OUT eventIDs, usrName USR.USR_LOGIN%TYPE, assignUsrKey USR.USR_KEY%TYPE, assignGrpKey UGP.UGP_KEY%TYPE ) AS
TYPE eventIDs IS TABLE OF RECON_EVENTS.RE_KEY%TYPE;
v_timestamp TIMESTAMP;
invoker_usr_key USR.USR_KEY%TYPE;
assign_usr_key USR.USR_KEY%TYPE;
rea_re_key_count number := 0;
max_rea_key RECON_EVENT_ASSIGNMENT.REA_KEY%TYPE;

BEGIN

I am trying to invoke this using StoredProcedurecall with the following snippet -

StoredProcedureCall spc = new StoredProcedureCall();
spc.setProcedureName(procedureName);
spc.addUnamedInOutputArgument("eventIdList", "eventIdList", Types.ARRAY, "eventIDs", ArrayList.class);
String argLoggedInUsrName = "usrName";
String argAssignedUsrKey = "assignUsrkey";
String argAssignedGrpKey = "assignGrpkey";
spc.addNamedArgument(argLoggedInUsrName);
spc.addNamedArgument(argAssignedUsrKey);
spc.addNamedArgument(argAssignedGrpKey);
ValueReadQuery query = new ValueReadQuery();
query.setCall(spc);
query.addArgument("eventIdList");
query.addArgument(argLoggedInUsrName);
query.addArgument(argAssignedUsrKey);
query.addArgument(argAssignedGrpKey);

Vector args = new Vector();
args.add(eventIdList);
args.add(loggedInUsrName);
args.add(assignedUsrKey);
args.add(assignedGrpKey);
eventIdList = (ArrayList<Long>) session.executeQuery(query, args);

I am getting this error code 17074 error. Here's the detailed error:
[TopLink Warning]: 2007.12.24 02:28:40.930--ServerSession(2725417)--Exception [TOPLINK-4002] (Oracle TopLink - 11g Release 1 (11.1.1.0.0) (Build 071011)): oracle.toplink.exceptions.DatabaseException
[junit] Internal Exception: java.sql.SQLException: invalid name pattern: OIMADMIN.eventIDs
[junit] Error Code: 17074
[junit] Call: BEGIN XL_SP_ASSIGN_EVENTLIST(?, usrName=>?, assignUsrkey=>?, assignGrpkey=>?); END;
[junit] bind => [null => eventIdList, FOO1, 117, 117]
[junit] Query: ValueReadQuery()

What am I doing wrong here?

You might also try checking your "eventIDs" type. Oracle defaults everything to uppercase while in java everything case sensitive, so it could be that "eventIDs" should really be specified as "EVENTIDS" in the addUnamedInOutputArgument argument list.