Toad: Automate daily transfer between two Oracle tables?

Question

I'm trying to copy rows from one Oracle table to another. Since I'm used to Microsoft's BI tools, I tried creating an SSIS project for this, but it's been an uphill battle.

We also have Toad Data Point, which is the tool that I use to query Oracle databases.

Since the only difference between the source and destination table is the table name, is it possible to create a job, to run daily, that will copy data from one Oracle table to another?

I forgot to mention that the tables are not in the same database.

Answer 1

There are a number of ways to copy data from one Oracle database to another - database links, DataPump, exp/imp (generally desupported, but still works), SQL*Loader load of CSV export, etc.

The example below creates a daily job that pulls data in the source database across a database link into the destination.

-- Source database: srcdb
-- Source user    : srcuser
-- Source table   : srctbl (owned by srcuser)

-- Destination database: destdb
-- Destination user    : destuser
-- Destination table   : desttbl (owned by destuser)

-- On destdb: grant destuser privilege to create database link and job
-- (Execute as SYS, or ask your DBA to do this)
SQL> GRANT CREATE DATABASE LINK TO destuser;

Grant succeeded.

SQL> GRANT CREATE JOB to destuser;

Grant succeeded.

-- On destdb, login as destuser and create database link that points to srcdb.
-- Link connects to srcdb as srcuser.  Substitute your HOST, PORT, and SERVICE_NAME
SQL> CREATE DATABASE LINK srcdb CONNECT TO srcuser IDENTIFIED BY srcuserpass USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=srcdb)))';

Database link created.

-- On destdb: Log in as destuser and create daily job to
-- copy all rows from the source to the destination
SQL> EXECUTE DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'destuser.copyjob',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'BEGIN INSERT INTO desttbl SELECT * FROM srctbl@srcdb; END;',
   start_date           => TO_DATE('06/17/2019 05:45 PM', 'MM/DD/YYYY HH:MI PM'),
   repeat_interval      => 'FREQ=DAILY',
   end_date             => NULL,
   enabled              =>  TRUE,
   comments             => 'Daily copy of data to dest from source');
END;

PL/SQL procedure successfully completed.

You can use the USER_SCHEDULER_JOBS view to monitor execution of the job.