JDBC query VARRAY on DB level

原创 2012年03月21日 20:01:00

 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.

 

相关文章推荐

Demonstration of DB Query Analyzer 6.03 Installation and Running on Microsoft Windows 8

This essay gives a demonstration of DB Query Analyzer Installation and Running on Microsoft Windows ...

DB Query Analyzer 6.03 on Microsoft Windows 8

  • 2015年01月09日 08:15
  • 1.18MB
  • 下载

Install and run DB Query Analyzer 6.04 on Microsoft Windows 10

Install and run DB Query Analyzer 6.04 on Microsoft Windows 10   DB Query Analyzer is presented by M...

[ElasticSearch]Java API之TermQuery

1. 词条查询(Term Query)  词条查询是ElasticSearch的一个简单查询。它仅匹配在给定字段中含有该词条的文档,而且是确切的、未经分析的词条。term 查询 会查找我们设定的...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:JDBC query VARRAY on DB level
举报原因:
原因补充:

(最多只允许输入30个字)