Cristiano's mail from 17 July
Jump to navigation
Jump to search
Reply Reply Reply to all Reply to all Forward Forward Move Copy Delete Previous Item Next Item Close Help
From: LUTTER Guillaume [guillaume.lutter@unine.ch] Sent: Thu 7/17/2008 10:40 PM
To: Igor Kreslo
Cc:
Subject: TR : Errata corrige for pp_download_predictions
Attachments:
View As Web Page
________________________________________
De : Cristiano Bozza [kryss@sa.infn.it]
Date d'envoi : jeudi 17 juillet 2008 17:52
À : LUTTER Guillaume
Objet : Re: Errata corrige for pp_download_predictions
Yes, as I wrote to Igor, the account BERN on OPITA was rebuilt, so the
password was reset.
I already sent him instructions to set the BERN password to what it was
before.
If you can't manage to do, I can reset the password once again, but then you
will have to rebuild the DBLink...
Cheers
Cristiano
----- Original Message -----
From: "LUTTER Guillaume" <guillaume.lutter@unine.ch>
To: "Cristiano Bozza" <kryss@sa.infn.it>
Sent: Thursday, July 17, 2008 5:33 PM
Subject: RE : Errata corrige for pp_download_predictions
Now it compiles
but I have now :
SQL> call pp_download_predictions(3034730,'OPITA01');
call pp_download_predictions(3034730,'OPITA01')
*
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from OPITA01
ORA-06512: at "OPERAPUB.PP_DOWNLOAD_PREDICTIONS", line 8
is it comming from the link to opita ?
________________________________________
De : Cristiano Bozza [kryss@sa.infn.it]
Date d'envoi : jeudi 17 juillet 2008 17:26
À : LUTTER Guillaume; opera-scanonline@cern.ch
Objet : Errata corrige for pp_download_predictions
Dear OPERA members,
in the version of pp_download_predictions I sent around a few days ago there
were some remnants of testing, so it would in any case try to connect to a
DBLink named 'OPITA'.
Please execute the script below to fix this.
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;