Another mail from Cristiano

Dear All,
 
Luca found another syntax problem in the pp_download_predictions I sent you last week.
Here is the (hopefully final!) corrected procedure text, provided by him.
Best regards
 
Cristiano
 
create or replace procedure pp_download_predictions(i_id_eventbrick integer,
i_dblink
varchar2) as
type typcur is ref cursor;
pc typcur;
v_procid integer;
v_rootid integer;
begin
execute immediate 'select distinct id_processoperation from tb_cs_candidates@'
||
i_dblink || ' zb where (zb.id_eventbrick, zb.id) in
   (select id_eventbrick, id_candidate from tb_cs_candidate_validation@' ||
i_dblink || ' zc where id_eventbrick = ' || i_id_eventbrick || ' and valid =
''Y'')' into v_rootid;
v_procid := v_rootid;
while v_procid is not NULL loop
  v_rootid := v_procid;
  execute immediate 'select id_parent_operation from tb_proc_operations@' ||
i_dblink || ' where id = ' || v_rootid into v_procid;
end loop;
dbms_output.put_line('ROOT: ' || v_rootid);

execute immediate 'insert into tb_eventbricks (select * from tb_eventbricks@'
||
i_dblink || ' where id = ' || i_id_eventbrick || ')';
execute immediate 'insert into tb_plates (select * from tb_plates@' || i_dblink
||
' where id_eventbrick = ' || i_id_eventbrick || ')';
execute immediate 'insert into tb_templatemarksets (select * from
tb_templatemarksets@' || i_dblink || ' where id_eventbrick = ' ||
i_id_eventbrick || ')';

open pc for 'select id from tb_proc_operations@' || i_dblink || ' connect by
id_parent_operation = prior id start with id = ' || v_rootid;
loop
  fetch pc into v_procid;
  exit when pc%notfound;
  dbms_output.put_line('PROCOP: ' || v_procid);
  execute immediate 'insert into tb_proc_operations (select * from
tb_proc_operations@' || i_dblink || ' where id = ' || v_procid || ')';
  execute immediate 'insert into tb_proc_operations (select * from
tb_proc_operations@' || i_dblink || ' p where id in (select id_processoperation
from tb_predicted_events@' || i_dblink || ' where id in (select id_event from
tb_cs_candidates@' || i_dblink || ' ca where id_eventbrick = ' ||
i_id_eventbrick
|| ' and id_processoperation = ' || v_procid || ' and exists (select * from
tb_cs_candidate_validation@' || i_dblink || ' cb where ca.id_eventbrick =
cb.id_eventbrick and ca.id = cb.id_candidate and cb.VALID = ''Y''))) and not
exists (select * from tb_proc_operations q where p.id = q.id)  )';
  execute immediate 'insert into tb_predicted_events (select * from
tb_predicted_events@' || i_dblink || ' where id in (select id_event from
tb_cs_candidates@' || i_dblink || ' ca where id_eventbrick = ' ||
i_id_eventbrick
|| ' and id_processoperation = ' || v_procid || ' and exists (select * from
tb_cs_candidate_validation@' || i_dblink || ' cb where ca.id_eventbrick =
cb.id_eventbrick and ca.id = cb.id_candidate and cb.VALID = ''Y'')))';
  execute immediate 'insert into tb_predicted_tracks (select * from
tb_predicted_tracks@' || i_dblink || ' where id_event in (select id_event from
tb_cs_candidates@' || i_dblink || ' ca where id_eventbrick = ' ||
i_id_eventbrick
|| ' and id_processoperation = ' || v_procid || ' and exists (select * from
tb_cs_candidate_validation@' || i_dblink || ' cb where ca.id_eventbrick =
cb.id_eventbrick and ca.id = cb.id_candidate and cb.VALID = ''Y'')))';
  execute immediate 'insert into tb_cs_candidates (select * from
tb_cs_candidates@'
|| i_dblink || ' ca where id_eventbrick = ' || i_id_eventbrick || 'and
id_processoperation = ' || v_procid || ' and exists (select * from
tb_cs_candidate_validation@' || i_dblink || ' cb where ca.id_eventbrick =
cb.id_eventbrick and ca.id = cb.id_candidate and cb.VALID = ''Y''))';
end loop;
close pc;

open pc for 'select id from tb_proc_operations connect by id_parent_operation =
prior id start with id = ' || v_rootid;
loop
  fetch pc into v_procid;
  exit when pc%notfound;
  execute immediate 'insert into tb_zones (select * from tb_zones@' || i_dblink
||
' p where id_eventbrick = ' || i_id_eventbrick || ' and id_processoperation = '
||
v_procid || ' and exists (select * from tb_cs_candidate_tracks@' || i_dblink ||
'
q where p.id_eventbrick = q.id_eventbrick and p.id = q.id_zone))';
  execute immediate 'insert into tb_views (select * from tb_views@' || i_dblink
||
' p where (id_eventbrick, id_zone) in (select id_eventbrick, id from tb_zones
where id_eventbrick = ' || i_id_eventbrick || ' and id_processoperation = ' ||
v_procid || '))';
end loop;
close pc;

open pc for 'select id from tb_proc_operations connect by id_parent_operation =
prior id start with id = ' || v_rootid;
loop
  fetch pc into v_procid;
  exit when pc%notfound;
  execute immediate 'insert into tb_mipmicrotracks (select * from
tb_mipmicrotracks@' || i_dblink || ' where (id_eventbrick, id_zone, side, id)
in
(select id_eventbrick, id_zone, side, id_microtrack from
tb_cs_candidate_tracks@'
|| i_dblink || ' where (id_eventbrick, id_candidate) in (select id_eventbrick,
id
from tb_cs_candidates where id_eventbrick = ' || i_id_eventbrick || ' and
id_processoperation = ' || v_procid || ')))';
end loop;
close pc;

open pc for 'select id from tb_proc_operations connect by id_parent_operation =
prior id start with id = ' || v_rootid;
loop
  fetch pc into v_procid;
  exit when pc%notfound;
   execute immediate 'insert into tb_cs_candidate_tracks (select * from
tb_cs_candidate_tracks@' || i_dblink || ' where (id_eventbrick, id_candidate)
in
(select id_eventbrick, id from tb_cs_candidates where id_eventbrick = ' ||
i_id_eventbrick || ' and id_processoperation = ' || v_procid || '))';
   execute immediate 'insert into tb_cs_candidate_checks (select * from
tb_cs_candidate_checks@' || i_dblink || ' where (id_eventbrick, id_candidate)
in
(select id_eventbrick, id from tb_cs_candidates where id_eventbrick = ' ||
i_id_eventbrick || ' and id_processoperation = ' || v_procid || '))';
end loop;
close pc;

end;