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.

 

spark mysql 行级别控制

spark 的save modespark 的saveMode在org.apache.spark.sql.SaveMode下,是一个枚举类,支持 Append(在mysql中为append) Over...
  • cjuexuan
  • cjuexuan
  • 2016年08月27日 10:28
  • 1727

SqlServer 并发事务(一):事务隔离级别

--查了当前数据库是事务隔离级别 DBCC USEROPTIONS 【测试一:丢失更新】 --事务1 begin tran select * from dbo.Test(nolock) wher...
  • kk185800961
  • kk185800961
  • 2014年11月26日 13:35
  • 10989

LevelDB原理探究与代码分析(上)

1.概述 LevelDB(http://code.google.com/p/leveldb/)是google开源的Key/Value存储系统,它的committer阵容相当强大,基本上是bigta...
  • u012432778
  • u012432778
  • 2015年06月14日 20:34
  • 1209

DB Query Analyzer 6.03, the most excellent UDA tools on any Microsoft Windows OS

  • 2014年12月16日 08:17
  • 771KB
  • 下载

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

  • 2015年09月01日 07:59
  • 349KB
  • 下载

A DB2 SQL Query - how to get the RACF groups on specified tables

  • 2009年09月01日 21:17
  • 865B
  • 下载

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 ...
  • magenfeng
  • magenfeng
  • 2015年01月09日 08:04
  • 5042

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...
  • magenfeng
  • magenfeng
  • 2015年09月01日 07:54
  • 5098

Rankcosine- Learning to Search Web Pages with Query-Level Loss Functions.pdf

  • 2008年03月11日 10:36
  • 426KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:JDBC query VARRAY on DB level
举报原因:
原因补充:

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