Home » SQL & PL/SQL » SQL & PL/SQL » Stored procedure to load initial bulk load from one DB to another DB (Oracle 11.2,Linux 6)
Stored procedure to load initial bulk load from one DB to another DB [message #669611] Wed, 02 May 2018 14:28 Go to next message
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;

/
Re: Stored procedure to load initial bulk load from one DB to another DB [message #669612 is a reply to message #669611] Wed, 02 May 2018 14:44 Go to previous message
BlackSwan
Messages: 26730
Registered: January 2009
Location: SoCal
Senior Member
yes
Previous Topic: Rollup master row
Next Topic: SQL statement with averages
Goto Forum:
  


Current Time: Fri Aug 14 06:49:02 CDT 2020