I understand that this question could be subjective.. but asking for the 'best' way to do nearly anything is subjective.
I have an Oracle database into which I need to import new data from tables in another database (to which I have a database link set up) on a weekly basis. I want to automate this process in a simple and reliable way.
My original plan was to call a procedure using with the source table name as a parameter. The procedure then works out the name of the staging table and inserts the data.
CREATE OR REPLACE PROCEDURE DATA_IMPORT (v_source_table IN VARCHAR2)
AS
V_STAGING_TABLE VARCHAR2(30);
BEGIN
CASE WHEN v_source_table='SOURCE_TABLE_1' THEN V_STAGING_TABLE:='STAGING_TABLE_1';
WHEN v_source_table='SOURCE_TABLE_2' THEN V_STAGING_TABLE:='STAGING_TABLE_2';
ELSE V_STAGING_TABLE:=NULL;
END CASE;
INSERT INTO V_STAGING_TABLE
SELECT * FROM v_source_table@DBLINK1;
END;
/
Then to get this to run on a schedule, I would create another procedure that runs through the DATA_IMPORT procedure for each table to be imported, and schedule this with DBMS_SCHEDULER. Does this sound reasonable?
