求救:java调用oracle 存储过程(返回数组的过程),求高高手
这是oracle:
CREATE OR REPLACE PACKAGE termtype_pkg AS
TYPE term_record IS RECORD(
monthEnergy number,
monthBili varchar2(8),
dayEnergy number,
dayBili varchar2(8),
logCount number
);
TYPE newArray IS VARRAY(10) OF term_record ;
end termtype_pkg;
create or replace procedure termtypePro
(
corpid in number,
cur_out out termtype_pkg.newArray
)
is
nowDate date;
nowYmd varchar(20);
nowYm varchar(20);
termtypeId number;
energy number;
daytotalEnergy number;
monthtotalEnergy number;
dayKTTotal number;
dayKTBili number;
dayZMTotal number;
dayZMBili number;
dayBGTotal number;
dayBGBili number;
dayQTTotal number;
dayQTBili number;
monthKTTotal number;
monthKTBili number;
monthZMTotal number;
monthZMBili number;
monthBGTotal number;
monthBGBili number;
monthQTTotal number;
monthQTBili number;
ktOut termtype_pkg.term_record;
bgOut termtype_pkg.term_record;
zmOut termtype_pkg.term_record;
qtOut termtype_pkg.term_record;
begin
select sysdate into nowDate from dual;
nowYmd := to_char(nowDate,'yyyy-mm-dd');
nowYm := to_char(nowDate,'yyyy-mm');
dayKTTotal :=0;
dayZMTotal :=0;
dayBGTotal :=0;
dayQTTotal :=0;
dayKTBili :=0;
dayZMBili :=0;
dayBGBili :=0;
dayQTBili :=0;
monthKTTotal :=0;
monthZMTotal :=0;
monthBGTotal :=0;
monthQTTotal :=0;
monthKTBili :=0;
monthZMBili :=0;
monthBGBili :=0;
monthQTBili :=0;
declare cursor daycus is select distinct s.termtypeid,s.en from(
select factdate.termid,ttype.termtypeid, max(factdate.datavalue)-min(factdate.datavalue) as en
from pfm_factdata_tbl factdate
left join stm_terminfo_tbl terminfo on factdate.termid = terminfo.termid
left join cfg_termtypeinfo_tbl ttype on ttype.termtypeid=terminfo.termtype
where terminfo.corpid = corpid and to_date(to_char(factdate.gathertimes,'yyyy-mm-dd'),'yyyy-mm-dd')=to_date(nowYmd,'yyyy-mm-dd') group by factdate.termid,ttype.termtypeid
) s;
--c_row daycus%rowtype;
begin
open daycus;
loop
fetch daycus into termtypeId,energy;
EXIT WHEN daycus%NOTFOUND;
energy := nvl(energy,0);
if termtypeId = 1 then
dayKTTotal := energy+dayKTTotal;
elsif termtypeId = 3 or termtypeId = 4 or termtypeId = 5 or termtypeId = 13 or termtypeId = 14 then
dayBGTotal := dayBGTotal+energy;
elsif termtypeId = 2 then
dayZMTotal := dayZMTotal+energy;
else
dayQTTotal := dayQTTotal+energy;
end if;
end loop;
close daycus;
end;
daytotalEnergy := dayKTTotal+dayBGTotal+dayZMTotal+dayQTTotal;
if daytotalEnergy is null or daytotalEnergy = 0 then
daytotalEnergy :=1;
end if;
dayKTBili :=round(dayKTTotal/daytotalEnergy,4)*100;
dayBGBili :=round(dayBGTotal/daytotalEnergy,4)*100;
dayZMBili :=round(dayZMTotal/daytotalEnergy,4)*100;
dayQTBili :=round(dayQTTotal/daytotalEnergy,4)*100;
declare cursor monthcus is select distinct s.termtypeid,s.ener from(
select factdate.termid,ttype.termtypeid, max(factdate.datavalue)-min(factdate.datavalue) as ener
from pfm_factdata_tbl factdate
left join stm_terminfo_tbl terminfo on factdate.termid = terminfo.termid
left join cfg_termtypeinfo_tbl ttype on ttype.termtypeid=terminfo.termtype
where terminfo.corpid = corpid and to_date(to_char(factdate.gathertimes,'yyyy-mm'),'yyyy-mm')=to_date(nowYm,'yyyy-mm')
group by factdate.termid,ttype.termtypeid
) s ;
begin
open monthcus;
loop
fetch monthcus into termtypeId , energy;
EXIT WHEN monthcus%NOTFOUND;
if termtypeId = 1 then
monthKTTotal := energy+monthKTTotal;
elsif termtypeId = 3 or termtypeId = 4 or termtypeId = 5 or termtypeId = 13 or termtypeId = 14 then
monthBGTotal := monthBGTotal+energy;
elsif termtypeId = 2 then
monthZMTotal := monthZMTotal+energy;
else
monthQTTotal := monthQTTotal+energy;
end if;
end loop;
close monthcus;
end;
monthtotalEnergy := monthKTTotal+monthBGTotal+monthZMTotal+monthQTTotal;
--monthtotalEnergy := monthKTTotal+monthBGTotal+monthZMTotal+monthQTTotal;
if monthtotalEnergy is null or monthtotalEnergy = 0 then
monthtotalEnergy :=1;
end if;
monthKTBili:=round(monthKTTotal/monthtotalEnergy,4)*100;
monthBGBili:=round(monthBGTotal/monthtotalEnergy,4)*100;
monthZMBili:=round(monthZMTotal/monthtotalEnergy,4)*100;
monthQTBili:=round(monthQTTotal/monthtotalEnergy,4)*100;
cur_out :=termtype_pkg.newArray();
cur_out.extend(4);
--ktOut :=termtype_pkg.newArray;
ktOut.monthEnergy :=monthKTTotal;
ktOut.monthBili :=monthKTBili||'%';
ktOut.dayEnergy :=dayKTTotal;
ktOut.dayBili :=dayKTBili||'%';
ktOut.logCount :=0;
cur_out(1) :=ktOut;
-- bgOut :=termtype_pkg.newArray;
bgOut.monthEnergy :=monthBGTotal;
bgOut.monthBili :=monthBGBili||'%';
bgOut.dayEnergy :=dayBGTotal;
bgOut.dayBili :=dayBGBili||'%';
bgOut.logCount :=0;
cur_out(2) :=bgOut;
--zmOut :=termtype_pkg.newArray;
zmOut.monthEnergy :=monthZMTotal;
zmOut.monthBili :=monthZMBili||'%';
zmOut.dayEnergy :=dayZMTotal;
zmOut.dayBili :=dayZMBili||'%';
zmOut.logCount :=0;
cur_out(3) :=zmOut;
--qtOut :=termtype_pkg.newArray;
qtOut.monthEnergy :=monthQTTotal;
qtOut.monthBili :=monthQTBili||'%';
qtOut.dayEnergy :=dayQTTotal;
qtOut.dayBili :=dayQTBili||'%';
qtOut.logCount :=0;
cur_out(4) :=qtOut;
end termtypePro;
这是java调用
new HibernateCallback(){
public Object doInHibernate(Session session)
throws HibernateException, SQLException{
Connection conn = session.connection();
OracleCallableStatement stmt =(OracleCallableStatement)conn.prepareCall ( "call system.termtypePro(?,?)" );
// ArrayDescriptor ad = ArrayDescriptor.createDescriptor("lgn", conn);
// ARRAY arr= new ARRAY(ad, conn, new Object[10]);
//cstmt.registerOutParameter(4,oracle.jdbc.OracleTypes.ARRAY,lgn2);
stmt.setInt(1, user.getCorpid());
stmt.registerOutParameter( 2, OracleTypes.ARRAY);
stmt.execute();
// 得到 ARRAY 对象
ARRAY simpleArray = stmt.getARRAY(1);
//转换为java数组
String[ ] values = (String[])simpleArray.getArray(); //输出数组内容
for( int i = 0; i < values.length; i++ ) {
System.out.println( "row " + i + " = '" + values[i] +"'" );
}
return null;
}
}
问题是:ERROR - Hibernate-related JDBC operation; uncategorized SQLException for SQL []; SQL state [63000]; error code [3115]; ORA-03115: 不支持的网络数据类型或表示法
debug 出错行在 stmt.execute();这一行
补充:Java , Java EE