最初由 WL_ZCJ 发布
[B]create or replace procedure p_97_auto_cron as
ret_flag number;
ret_errmsg varchar2(200);
as_area_id number;
as_staff_id number;
as_begin_time varchar2(20);
as_end_time varchar2(20);
begin
as_staff_id := -1;
for rec in (select 1 area_id from dual
union
select 2 area_id from dual
union
select 3 area_id from dual
union
select 5 area_id from dual
union
select 6 area_id from dual
union
select 8 area_id from dual
) loop
begin
as_area_id := rec.area_id;
as_begin_time := to_char(trunc(sysdate,'DD')-1, 'yyyymmddhh24miss');
as_end_time := to_char(trunc(sysdate,'DD'), 'yyyymmddhh24miss');
p_97_auto_get_ask(as_begin_time, as_end_time, as_area_id, ret_flag, ret_errmsg);
dbms_session.close_database_link('hxtojf');
exception
when others then
ret_errmsg := sqlerrm;
insert into system_log(operate_time,staff_id,event,id)
values(sysdate,as_staff_id,'('||as_area_id||')'||ret_errmsg, 4200+as_area_id);
commit;
end;
end loop;
end;
在执行该存储过程是,循环执行一次后就报一个ORA-02041的问题,经分析是子过程p_97_auto_get_ask中用到的数据库链没有释放,通过在子过程后加dbms_session.close_database_link('hxtojf'); 解决了此问题.
不知道哪位大侠那有比较全的ORACLE函数文档 [/B]