Stored procedure to load initial bulk load from one DB to another DB [message #669611] |
Wed, 02 May 2018 14:28 |
|
senmng
Messages: 22 Registered: April 2018
|
Junior Member |
|
|
Hi - We have a set of stored procs which can weekly/monthly basis to fetch the data from prod to our DB which is meant for reporting purpose. All the scripts were designed in such a way to address the incremental load based on two columns start date and end date which are referencing from other proc(Proc 2).But we need to change the logic to accomplish bulk load initially from Prod and then incremental load in consecutive load.
DB - Oracle 11.2 ,OS -Linux 6.
Can we comment all the variables (begin date and end date) in declaration and where clause to go for the bulk load? then enable them back to go for incremental load for consecutive loads.
Here is the sample proc which is one among the set of stored proc-
CREATE OR REPLACE PROCEDURE PROC1 AS
P_begin date;
P_end date DATE DEFAULT CURRENT_DATE;
Begin
SELECT START_TIME, END_TIME INTO P_begin, P_END_date FROM table1 WHERE ROWNUM = 1 ORDER BY ID DESC;
Insert into table2 select col2,col2 from table 3@dblink
where record_time > P_begin date and record_time < P_end date;
End;
/
Here is the table DDL and proc2 which are storing the load start time and end time which have been referenced in all store procs to accomplish the incremental load
Create table1 ( ID number(10) not null, load_date date;START_TIME date,END_TIME date);
CREATE OR REPLACE PROCEDURE PROC 2 as
NUM_LOADS INT DEFAULT 0;
LAST_END_TIME DATE;
THIS_START_TIME DATE DEFAULT TRUNC(CURRENT_DATE - 7, 'DD');
HIS_END_TIME DATE DEFAULT TRUNC(CURRENT_DATE, 'DD') - 1 / (24 * 60 * 60);
BEGIN
SELECT COUNT(*) INTO NUM_LOADS FROM table1;
IF to_char(CURRENT_DATE, 'DY') != 'TUE'
THEN
RAISE_APPLICATION_ERROR(-20101, 'Reports are run only on tuesday');
END IF;
IF NUM_LOADS > 0
THEN
SELECT MAX(END_TIME) INTO LAST_END_TIME FROM table1;
IF THIS_START_TIME < LAST_END_TIME
THEN
RAISE_APPLICATION_ERROR(-20101, 'Report has already been run for this week');
END IF;
END IF;
INSERT INTO RPT_LOAD_CONTROL ( ID,LOAD_DATE,START_TIME, END_TIME,
) VALUES (
SEQ1.nextval,
CURRENT_DATE,
THIS_START_TIME,
THIS_END_TIME,
NULL
);
END;
/
|
|
|
|