存储过程里用了数据链路
单独测试没有问题
java程序调的话报cursor closed 不知道什么原因create or replace package body P_KCTP_KSSQ isprocedure getForKsyyShowInfo(lshPIn in varchar2,kskmPIn in varchar2,glbmPIn in varchar2,rcOut out t_Cursor,result out integer,bz out varchar2)is
sqlstr varchar2(500);
--kskm drv_flow.kskm@TO_TYB_DRV%Type;
--zt drv_preasign.zt@TO_TYB_DRV%Type;
kskm varchar2(10);
zt varchar2(10);
begin
--查看给定流水号能否报考该科目
declare
begin
set transaction read only;
select substr(t.kskm,decode(kskmPIn,'1',0,'2','3','3',6),2) into kskm from drv_flow@TO_TYB_DRV t where t.lsh=lshPIn and t.glbm=glbmPIn;
commit;
exception
when others then
raise_application_error (-20101, '没有符合此流水号的信息');
rollback;
end;if(kskm <>'01') then
raise_application_error (-20101, '不能报考该科目');
end if;--查询该流水号报考的科目是否已考试
declare
begin
set transaction read only;
select t.zt into zt from drv_preasign@TO_TYB_DRV t where t.lsh=lshPIn and t.glbm=glbmPIn;
commit;
exception
when others then
raise_application_error (-20101, '没有符合的数据');
rollback;
end;if(zt='1') then
raise_application_error (-20101, '该科目已考试合格');
end if;
--查询该流水号信息
--set transaction read only;
sqlstr :='select t.sfzmhm as zjhm,GETZJMC(t.sfzmmc) as zjmc,GETGJ(t.gj) as gj,t.xm as xm,decode(t.xb,''1'',''男'',''2'',''女'') as xb,t.csrq as csrq,t.djzsxxdz as djzsxxdz,t.djzsxzqh as djzsxzqh,t.lxzsxzqh as lxzsxzqh,t.lxzsxxdz as lxzsxxdz,t.lxdh as lxdh ,t.jxmc as jxmc ,a.kscx as kscx from drv_preasign@TO_TYB_DRV a , drv_temp_mid@TO_TYB_DRV t where a.lsh=t.lsh and t.lsh=:p0 and t.glbm=:p1';
open rcOut for sqlstr using lshPIn,glbmPIn;
result :=0;
bz:='执行成功';
--commit;
exception
when others then
--rollback;
result :=1;
bz:=sqlerrm;end;
单独测试没有问题
java程序调的话报cursor closed 不知道什么原因create or replace package body P_KCTP_KSSQ isprocedure getForKsyyShowInfo(lshPIn in varchar2,kskmPIn in varchar2,glbmPIn in varchar2,rcOut out t_Cursor,result out integer,bz out varchar2)is
sqlstr varchar2(500);
--kskm drv_flow.kskm@TO_TYB_DRV%Type;
--zt drv_preasign.zt@TO_TYB_DRV%Type;
kskm varchar2(10);
zt varchar2(10);
begin
--查看给定流水号能否报考该科目
declare
begin
set transaction read only;
select substr(t.kskm,decode(kskmPIn,'1',0,'2','3','3',6),2) into kskm from drv_flow@TO_TYB_DRV t where t.lsh=lshPIn and t.glbm=glbmPIn;
commit;
exception
when others then
raise_application_error (-20101, '没有符合此流水号的信息');
rollback;
end;if(kskm <>'01') then
raise_application_error (-20101, '不能报考该科目');
end if;--查询该流水号报考的科目是否已考试
declare
begin
set transaction read only;
select t.zt into zt from drv_preasign@TO_TYB_DRV t where t.lsh=lshPIn and t.glbm=glbmPIn;
commit;
exception
when others then
raise_application_error (-20101, '没有符合的数据');
rollback;
end;if(zt='1') then
raise_application_error (-20101, '该科目已考试合格');
end if;
--查询该流水号信息
--set transaction read only;
sqlstr :='select t.sfzmhm as zjhm,GETZJMC(t.sfzmmc) as zjmc,GETGJ(t.gj) as gj,t.xm as xm,decode(t.xb,''1'',''男'',''2'',''女'') as xb,t.csrq as csrq,t.djzsxxdz as djzsxxdz,t.djzsxzqh as djzsxzqh,t.lxzsxzqh as lxzsxzqh,t.lxzsxxdz as lxzsxxdz,t.lxdh as lxdh ,t.jxmc as jxmc ,a.kscx as kscx from drv_preasign@TO_TYB_DRV a , drv_temp_mid@TO_TYB_DRV t where a.lsh=t.lsh and t.lsh=:p0 and t.glbm=:p1';
open rcOut for sqlstr using lshPIn,glbmPIn;
result :=0;
bz:='执行成功';
--commit;
exception
when others then
--rollback;
result :=1;
bz:=sqlerrm;end;
解决方案 »
- JDBC 使用jpa的形式 取出来的数据 总是第一条 这个表没有主键 为什么牛人帮忙解答一下。
- 急:视图卡住,执行很慢
- oracle exp/imp 无法解析指定的连接标识符
- oracle数据库问题
- 这是怎么回事呀?
- isqlplus打不开,高手进来帮帮忙啊,100分相送
- 高分急问,关于dbms_output.put_line
- 请教各位大侠,一个Oracle触发器的问题
- 能否用语句查询,一个对象的名称?
- 判断数据是否存在时一般使用语句select 1 from .. where.. ,它比select count(*) from .. where ..的优点在哪里?
- 如何给一批表加触发器
- grouping sets为何出错
在最后的异常处理中利用sqlcode和sqlerrm将错误信息返回,根据它来判断错误根源