Create an sql script that compares two dbase and updates dbase and insert date of update in a column in oracle 11g
-
Miguel Quintanilla wrote:
I can c++, java and some sql but i nned help with oracle.
I've never worked in C++ nor Java, but the Oracle-database speaks SQL.
Miguel Quintanilla wrote:
and need all the help i can get.
What are you trying to achieve (on a higher level)? Create some kind of synchronization?
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
I have done this on TOAD for oracle: -- run every 5 MINUTES BEGIN dbms_scheduler.create_schedule( schedule_name => 'INTERVAL_EVERY_5_MINUTES', start_date => trunc(sysdate)+18/24, repeat_interval => 'freq=MINUTELY;interval=5', comments => 'Runtime: Every day all 5 minutes'); END; / begin -- Call a procedure of a database package dbms_scheduler.create_program (program_name=> 'PROG_COLLECT_SESS_DATA', program_type=> '???????', HERE IS MY PROBLEM IS IT POSIBLE TO MAKE A DBLINK TO CONNECTO TO THE OTHER DB AND RETRIEVE INFORMATION FROM A TABLE AND PAST IT ON TO SIMILAR TABLE ON THIS DB. AND PUT A FIELD DNM_SINC TO 'YES' WHEN IN DEFAULT IS NO? program_action=> '??????????????', enabled=>true, comments=>'Procedure to collect session information' ); end; begin -- Connect both dbms_scheduler parts by creating the final job dbms_scheduler.create_job (job_name => 'JOB_COLLECT_SESS_DATA', program_name=> 'PROG_COLLECT_SESS_DATA', schedule_name=>'INTERVAL_EVERY_5_MINUTES', enabled=>true, auto_drop=>false, comments=>'Job to collect data about session values every 5 minutes'); end;
-
We won't actually do the writing for you but will help when you have a problem (not the entire process). Try breaking it down into smaller bits. Write a query that compares the 2 tables and identifies the records that need to be updated. Test it Now change the query to do the actual update. Create a stored procedure using the update query. Look into database job scheduling to call the stored procedure you have created (I know SQL Server but not Oracle). If this is beyond you then you need to get some education in working with a database, specifically Oracle which is not a trivial exercise.
Never underestimate the power of human stupidity RAH
Hi many thanks for all the help you give me. here is my problem i want to use me of this: this i have on toad: -- run every 5 MINUTES BEGIN dbms_scheduler.create_schedule( schedule_name => 'INTERVAL_EVERY_5_MINUTES', start_date => trunc(sysdate)+18/24, repeat_interval => 'freq=MINUTELY;interval=5', comments => 'Runtime: Every day all 5 minutes'); END; / begin -- Call a procedure of a database package dbms_scheduler.create_program (program_name=> 'PROG_COLLECT_SESS_DATA', program_type=> 'STORED_PROCEDURE', program_action=> '?????? i dont have this yet', enabled=>true, comments=>'Procedure to collect session information' ); end; begin -- Connect both dbms_scheduler parts by creating the final job dbms_scheduler.create_job (job_name => 'JOB_COLLECT_SESS_DATA', program_name=> 'PROG_COLLECT_SESS_DATA', schedule_name=>'INTERVAL_EVERY_5_MINUTES', enabled=>true, auto_drop=>false, comments=>'Job to collect data about session values every 5 minutes'); end; I AM GOING TO USE THIS SCHEDULED JOB AND NOW I WANT TO CREATE MY SQL AND I HAVE A LITTLE BIT OF TROUBLE. I HAVE DONE THIS DBLINK: CREATE DATABASE LINK DB_2 CONNECT TO COMPUSOFT IDENTIFIED BY albosapam02 USING 'inv2' AND THEN I HAVE DONE THIS: SELECT * FROM PESAJE MINUS SELECT * FROM PESAJE@DB_2 IT GIVES ME A RESULT BUT NOW I WANT TO INSERT THAT RESULT IN THE DB_2 AND STORE THE QUERY SO I CAN ALL IT ON THE JOB.. aNY HELP???
-
I have done this on TOAD for oracle: -- run every 5 MINUTES BEGIN dbms_scheduler.create_schedule( schedule_name => 'INTERVAL_EVERY_5_MINUTES', start_date => trunc(sysdate)+18/24, repeat_interval => 'freq=MINUTELY;interval=5', comments => 'Runtime: Every day all 5 minutes'); END; / begin -- Call a procedure of a database package dbms_scheduler.create_program (program_name=> 'PROG_COLLECT_SESS_DATA', program_type=> '???????', HERE IS MY PROBLEM IS IT POSIBLE TO MAKE A DBLINK TO CONNECTO TO THE OTHER DB AND RETRIEVE INFORMATION FROM A TABLE AND PAST IT ON TO SIMILAR TABLE ON THIS DB. AND PUT A FIELD DNM_SINC TO 'YES' WHEN IN DEFAULT IS NO? program_action=> '??????????????', enabled=>true, comments=>'Procedure to collect session information' ); end; begin -- Connect both dbms_scheduler parts by creating the final job dbms_scheduler.create_job (job_name => 'JOB_COLLECT_SESS_DATA', program_name=> 'PROG_COLLECT_SESS_DATA', schedule_name=>'INTERVAL_EVERY_5_MINUTES', enabled=>true, auto_drop=>false, comments=>'Job to collect data about session values every 5 minutes'); end;
-
I HAVE DONE THIS DBLINK: CREATE DATABASE LINK DB_2 CONNECT TO COMPUSOFT IDENTIFIED BY albosapam02 USING 'inv2' AND THEN I HAVE DONE THIS: SELECT * FROM PESAJE MINUS SELECT * FROM PESAJE@DB_2 IT GIVES ME A RESULT BUT NOW I WANT TO INSERT THAT RESULT IN THE DB_2 AND STORE THE QUERY SO I CAN cALL IT ON THE JOB.. aNY HELP???
-
I HAVE DONE THIS DBLINK: CREATE DATABASE LINK DB_2 CONNECT TO COMPUSOFT IDENTIFIED BY albosapam02 USING 'inv2' AND THEN I HAVE DONE THIS: SELECT * FROM PESAJE MINUS SELECT * FROM PESAJE@DB_2 IT GIVES ME A RESULT BUT NOW I WANT TO INSERT THAT RESULT IN THE DB_2 AND STORE THE QUERY SO I CAN cALL IT ON THE JOB.. aNY HELP???
Your message is all in caps-lock; on the internet that's interpreted as shouting.
Miguel Quintanilla wrote:
IT GIVES ME A RESULT BUT NOW I WANT TO INSERT THAT RESULT IN THE DB_2 AND STORE THE QUERY SO I CAN cALL IT ON THE JOB..
aNY HELP??With what exactly? You can select and insert; please describe the problem you're having.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Your message is all in caps-lock; on the internet that's interpreted as shouting.
Miguel Quintanilla wrote:
IT GIVES ME A RESULT BUT NOW I WANT TO INSERT THAT RESULT IN THE DB_2 AND STORE THE QUERY SO I CAN cALL IT ON THE JOB..
aNY HELP??With what exactly? You can select and insert; please describe the problem you're having.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Ok i have the result from the both db's in this query now i want to insert the result in DB_2 this the query that gives me the result: select /*+ FULL(Tbl1) */ PSJ_GESTION, PSJ_COD, PSJ_PLACA, PSJ_PESO, PSJ_FECHA , PSJ_ESTADO, BLZ_COD, MNF_COD, DMN_COD, USR_COD , PSJ_OPERACION, TIC_COD, PSJ_TARA, PSJ_NETO from COMPUSOFT.PESAJE Tbl1 minus select /*+ FULL(Tbl2) */ PSJ_GESTION, PSJ_COD, PSJ_PLACA, PSJ_PESO, PSJ_FECHA , PSJ_ESTADO, BLZ_COD, MNF_COD, DMN_COD, USR_COD , PSJ_OPERACION, TIC_COD, PSJ_TARA, PSJ_NETO from COMPUSOFT.PESAJE@"DB_2" Tbl2 Order by PSJ_GESTION ASC NULLS LAST, PSJ_COD ASC NULLS LAST, PSJ_PLACA ASC NULLS LAST, PSJ_PESO ASC NULLS LAST, PSJ_FECHA ASC NULLS LAST, PSJ_ESTADO ASC NULLS LAST, BLZ_COD ASC NULLS LAST, MNF_COD ASC NULLS LAST, DMN_COD ASC NULLS LAST, USR_COD ASC NULLS LAST, TIC_COD ASC NULLS LAST, PSJ_TARA ASC NULLS LAST, PSJ_NETO ASC NULLS LAST
-
Ok i have the result from the both db's in this query now i want to insert the result in DB_2 this the query that gives me the result: select /*+ FULL(Tbl1) */ PSJ_GESTION, PSJ_COD, PSJ_PLACA, PSJ_PESO, PSJ_FECHA , PSJ_ESTADO, BLZ_COD, MNF_COD, DMN_COD, USR_COD , PSJ_OPERACION, TIC_COD, PSJ_TARA, PSJ_NETO from COMPUSOFT.PESAJE Tbl1 minus select /*+ FULL(Tbl2) */ PSJ_GESTION, PSJ_COD, PSJ_PLACA, PSJ_PESO, PSJ_FECHA , PSJ_ESTADO, BLZ_COD, MNF_COD, DMN_COD, USR_COD , PSJ_OPERACION, TIC_COD, PSJ_TARA, PSJ_NETO from COMPUSOFT.PESAJE@"DB_2" Tbl2 Order by PSJ_GESTION ASC NULLS LAST, PSJ_COD ASC NULLS LAST, PSJ_PLACA ASC NULLS LAST, PSJ_PESO ASC NULLS LAST, PSJ_FECHA ASC NULLS LAST, PSJ_ESTADO ASC NULLS LAST, BLZ_COD ASC NULLS LAST, MNF_COD ASC NULLS LAST, DMN_COD ASC NULLS LAST, USR_COD ASC NULLS LAST, TIC_COD ASC NULLS LAST, PSJ_TARA ASC NULLS LAST, PSJ_NETO ASC NULLS LAST
-
I have try this: Insert into COMPUSOFT.PESAJE@DB_2 select /*+ FULL(Tbl1) */ PSJ_GESTION, PSJ_COD, PSJ_PLACA, PSJ_PESO, PSJ_FECHA , PSJ_ESTADO, BLZ_COD, MNF_COD, DMN_COD, USR_COD , PSJ_OPERACION, TIC_COD, PSJ_TARA, PSJ_NETO from COMPUSOFT.PESAJE Tbl1 minus select /*+ FULL(Tbl2) */ PSJ_GESTION, PSJ_COD, PSJ_PLACA, PSJ_PESO, PSJ_FECHA , PSJ_ESTADO, BLZ_COD, MNF_COD, DMN_COD, USR_COD , PSJ_OPERACION, TIC_COD, PSJ_TARA, PSJ_NETO from COMPUSOFT.PESAJE@"DB_2" Tbl2; But i get "missing values"
-
I have try this: Insert into COMPUSOFT.PESAJE@DB_2 select /*+ FULL(Tbl1) */ PSJ_GESTION, PSJ_COD, PSJ_PLACA, PSJ_PESO, PSJ_FECHA , PSJ_ESTADO, BLZ_COD, MNF_COD, DMN_COD, USR_COD , PSJ_OPERACION, TIC_COD, PSJ_TARA, PSJ_NETO from COMPUSOFT.PESAJE Tbl1 minus select /*+ FULL(Tbl2) */ PSJ_GESTION, PSJ_COD, PSJ_PLACA, PSJ_PESO, PSJ_FECHA , PSJ_ESTADO, BLZ_COD, MNF_COD, DMN_COD, USR_COD , PSJ_OPERACION, TIC_COD, PSJ_TARA, PSJ_NETO from COMPUSOFT.PESAJE@"DB_2" Tbl2; But i get "missing values"
-
Hi many thanks for all the help you give me. here is my problem i want to use me of this: this i have on toad: -- run every 5 MINUTES BEGIN dbms_scheduler.create_schedule( schedule_name => 'INTERVAL_EVERY_5_MINUTES', start_date => trunc(sysdate)+18/24, repeat_interval => 'freq=MINUTELY;interval=5', comments => 'Runtime: Every day all 5 minutes'); END; / begin -- Call a procedure of a database package dbms_scheduler.create_program (program_name=> 'PROG_COLLECT_SESS_DATA', program_type=> 'STORED_PROCEDURE', program_action=> '?????? i dont have this yet', enabled=>true, comments=>'Procedure to collect session information' ); end; begin -- Connect both dbms_scheduler parts by creating the final job dbms_scheduler.create_job (job_name => 'JOB_COLLECT_SESS_DATA', program_name=> 'PROG_COLLECT_SESS_DATA', schedule_name=>'INTERVAL_EVERY_5_MINUTES', enabled=>true, auto_drop=>false, comments=>'Job to collect data about session values every 5 minutes'); end; I AM GOING TO USE THIS SCHEDULED JOB AND NOW I WANT TO CREATE MY SQL AND I HAVE A LITTLE BIT OF TROUBLE. I HAVE DONE THIS DBLINK: CREATE DATABASE LINK DB_2 CONNECT TO COMPUSOFT IDENTIFIED BY albosapam02 USING 'inv2' AND THEN I HAVE DONE THIS: SELECT * FROM PESAJE MINUS SELECT * FROM PESAJE@DB_2 IT GIVES ME A RESULT BUT NOW I WANT TO INSERT THAT RESULT IN THE DB_2 AND STORE THE QUERY SO I CAN ALL IT ON THE JOB.. aNY HELP???
Sorry my knowledge of Oracle and DB2 is very limited, I'm not going to be able to help with the details on those platforms. Oracle has a dedicated support forum, Ask Ted I think, you should get more relevant support there!
Never underestimate the power of human stupidity RAH
-
Sorry my knowledge of Oracle and DB2 is very limited, I'm not going to be able to help with the details on those platforms. Oracle has a dedicated support forum, Ask Ted I think, you should get more relevant support there!
Never underestimate the power of human stupidity RAH
Thanks anyway
-
I have solved it ! It was a missmatch in the first select ok how do i store the query so i can call it in a store_procedure
-
I have solved it ! It was a missmatch in the first select ok how do i store the query so i can call it in a store_procedure