Table Snapshot_B is the snapshot of table B which should be refreshed after every 30 minutes.The one month old data will be copied to Snapshot_B. I have written the below SQL for this.
I am a beginner in unix.Can anyone guide me how to schedule the below code in unix shell scripting? I need to execute the procedure after every 30 minute so that user can see the updated data from table B into Snapshot_B. Need your help!
CREATE OR REPLACE PROCEDURE MF01.TEST( V_CNT OUT NUMBER)
AS
BEGIN
SELECT NVL((CASE WHEN ( MAX(LAST_UPD_TMS) > SYSTIMESTAMP - INTERVAL '30' DAY) THEN 1 END),0) INTO V_CNT FROM B;
IF V_CNT > 0 THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE MF01.SNAPSHOT_B';
INSERT INTO SNAPSHOT_B
SELECT * FROM B WHERE LAST_UPD_TMS < SYSTIMESTAMP - INTERVAL '30' DAY;
COMMIT;
END IF;
END;
[Updated on: Tue, 03 June 2008 03:38]
Report message to a moderator
] 
Blog
Search
Help
Members
Register
Login
Home
