View source for Cristiano's mail from 17 July

<pre>
 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;


</pre>