Here's the procedure I commonly use to implement a odi loop in package.!
create or replace function sys.set_initial_data (P_TOKEN varchar2, P_VALUE varchar2, P_ROW_ID number, p_session number) return varchar2
is
PRAGMA AUTONOMOUS_TRANSACTION;
res varchar2(50);
cursor get_datap is
select * from process_data_odi
where 1=1--status = (select case P_VALUE when 'ROW' THEN 'R' else 'P' end from dual)
and status = 'P';
--and id_host = nvl(null,id_host);
vget_datap get_datap%rowtype;
cursor get_datap_id is
select * from process_data_odi
where 1=1--status = (select case P_VALUE when 'ROW' THEN 'R' else 'P' end from dual)
and id_host = nvl(P_ROW_ID,id_host);
--and status = 'P'
vget_datap_id get_datap_id%rowtype;
begin
if p_token = 'INIT' then
delete from process_data_odi;
insert into process_data_odi
select p_session, id, 'P', null,IP, puerto from table_info_host;
commit;
return 'OK';
elsif p_token = 'GET' then
open get_datap_id;
fetch get_datap_id into vget_datap_id;
close get_datap_id;
IF P_VALUE = 'PORT' then
res := vget_datap_id.PUERTO;
elsif P_VALUE = 'HOST' then
res := vget_datap_id.HOST;
elsif P_VALUE = 'ROW' then
open get_datap;
fetch get_datap into vget_datap;
close get_datap;
res := vget_datap.ID_host;
update process_data_odi set status = 'R'
where id_host = vget_datap.ID_host;
commit;
end if;
elsif p_token = 'SET' then
update process_data_odi set status = 'T'
where id_host = P_ROW_ID;
commit;
return 'OK';
end if;
return nvl(RES,'ERR');
end;
SELECT * FROM process_data_odi;
select set_initial_data('GET','PORT',2,1111) from dual;
select set_initial_data('GET','HOST',2,1111) from dual;
select set_initial_data('GET','ROW',null,1111) from dual;
select set_initial_data('SET',null,1,1111) from dual;
select set_initial_data('INIT',NULL,null,1111) from dual;
commit;
