Home » SQL & PL/SQL » SQL & PL/SQL » Problem: Run schedular to copy from one table to other (Oracle9i,Unix)
Problem: Run schedular to copy from one table to other [message #324425] Mon, 02 June 2008 07:54 Go to next message
VEDDETA
Messages: 54
Registered: May 2008
Member


Check if records of table B are 1 month old. if yes then Truncate table B_SNAPSHOT and then copy data from B to SNAPSHOT_B table otherwise no need to copy.
Run a schedular after every 15 minutes.


The tables B and SNAPSHOT_B has the below structure(same for both):

PROCESS_NAME
MQ_NAME
LAST_UPD_TMS

How I can do that using Unix and Oracle 9i.Need help with example script.
Re: Problem: Run schedular to copy from one table to other [message #324427 is a reply to message #324425] Mon, 02 June 2008 07:56 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Show us what you have tried.
Re: Problem: Run schedular to copy from one table to other [message #324430 is a reply to message #324425] Mon, 02 June 2008 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why have you to run every 15 minutes if you are just interested in rows that are 1 month old?
Isn't an execution each day just sufficient?

Regards
Michel
Re: Problem: Run schedular to copy from one table to other [message #324433 is a reply to message #324427] Mon, 02 June 2008 08:26 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
-- Test.sql
DECLARE
V_COUNT NUMBER; 
BEGIN

SELECT NVL((CASE WHEN (  MAX(LAST_UPD_TMS) < SYSTIMESTAMP - INTERVAL '30' DAY)  THEN  1  END),0) 
INTO  V_COUNT 
FROM B;
IF V_COUNT > 0 THEN 
	TRUNCATE TABLE SNAPSHOTY_B;
	INSERT INTO SNAPSHOT_B SELECT * FROM PROCESS_WIP;
END IF;
END;

Tried liked this way.

How can I use it as a scheduling jobs.Please do let me know if I am wrong somewhere.

[Updated on: Mon, 02 June 2008 08:42] by Moderator

Report message to a moderator

Re: Problem: Run schedular to copy from one table to other [message #324434 is a reply to message #324425] Mon, 02 June 2008 08:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>How can I use it as a scheduling jobs.Please do let me know if I am wrong somewhere
what invokes it & when?
Re: Problem: Run schedular to copy from one table to other [message #324435 is a reply to message #324430] Mon, 02 June 2008 08:32 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Michel Cadot wrote on Mon, 02 June 2008 08:03
Why have you to run every 15 minutes if you are just interested in rows that are 1 month old?
Isn't an execution each day just sufficient?

Regards
Michel



Yes, you are correct execution each day will be sufficient.But if someone wants last 15 minutes snapshot of data then I think it will be ok.Rite? Thanks Michel for pointing it out.
My question is how I would use as a scheduling jobs? Need your help.
Re: Problem: Run schedular to copy from one table to other [message #324436 is a reply to message #324434] Mon, 02 June 2008 08:33 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Using cron jobs? But I am not aware of how to do that.
Re: Problem: Run schedular to copy from one table to other [message #324442 is a reply to message #324433] Mon, 02 June 2008 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
VEDDETA wrote on Mon, 02 June 2008 15:26
-- Test.sql
DECLARE
V_COUNT NUMBER; 
BEGIN

SELECT NVL((CASE WHEN (  MAX(LAST_UPD_TMS) < SYSTIMESTAMP - INTERVAL '30' DAY)  THEN  1  END),0) 
INTO  V_COUNT 
FROM B;
IF V_COUNT > 0 THEN 
	TRUNCATE TABLE SNAPSHOTY_B;
	INSERT INTO SNAPSHOT_B SELECT * FROM PROCESS_WIP;
END IF;
END;

Tried liked this way.

How can I use it as a scheduling jobs.Please do let me know if I am wrong somewhere.

This does not compile.
Then use DBMS_JOB

Regards
Michel

Re: Problem: Run schedular to copy from one table to other [message #324544 is a reply to message #324442] Tue, 03 June 2008 01:25 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Can not we use cron job for this? If yes, how to do that?
Re: Problem: Run schedular to copy from one table to other [message #324545 is a reply to message #324544] Tue, 03 June 2008 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
yes it is possible.
Go to Unix forum, there are many examples of how to use sqlplus inside a shell script.

Regards
Michel
Re: Problem: Run schedular to copy from one table to other [message #324574 is a reply to message #324545] Tue, 03 June 2008 03:34 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Thank you Michel.
Re: Problem: Run schedular to copy from one table to other [message #324591 is a reply to message #324574] Tue, 03 June 2008 04:26 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
I want to execute below stored procedure after every 30 minutes using DBMS_JOBS.How can I do that? How I would use DBMS_JOB.SUBMIT. I need to know the syntax.Please 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 04:27]

Report message to a moderator

Re: Problem: Run schedular to copy from one table to other [message #324602 is a reply to message #324591] Tue, 03 June 2008 05:05 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
sigh.. here we go again..


What did you try yourself?
Re: Problem: Run schedular to copy from one table to other [message #324603 is a reply to message #324602] Tue, 03 June 2008 05:29 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Will it be something like this?

SQL>VARIABLE jobno number;
SQL> VARIABLE cnt number;
sql>

begin

dbms_job.submit(:job_no,'Test(:cnt);',sysdate + 30/1440,'sysdate + 60/1440');
end;
/

[Updated on: Tue, 03 June 2008 05:30]

Report message to a moderator

Re: Problem: Run schedular to copy from one table to other [message #324604 is a reply to message #324591] Tue, 03 June 2008 05:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is EXACTLY an example of what you want to do in the link I posted.

Regards
Michel
Re: Problem: Run schedular to copy from one table to other [message #324610 is a reply to message #324604] Tue, 03 June 2008 05:41 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Thanks for the reply Michel. But I am unable to find it in forum.Can you please provide me the link? Can anyone please let me know where I am wrong in above code? Thanks in advance...

[Updated on: Tue, 03 June 2008 06:19]

Report message to a moderator

Re: Problem: Run schedular to copy from one table to other [message #324626 is a reply to message #324610] Tue, 03 June 2008 06:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 02 June 2008 15:44
Then use DBMS_JOB

Regards
Michel

Re: Problem: Run schedular to copy from one table to other [message #324635 is a reply to message #324610] Tue, 03 June 2008 07:24 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
VEDDETA wrote on Tue, 03 June 2008 12:41
Can anyone please let me know where I am wrong in above code? Thanks in advance...

commit.
Re: Problem: Run schedular to copy from one table to other [message #324807 is a reply to message #324635] Wed, 04 June 2008 02:50 Go to previous message
VEDDETA
Messages: 54
Registered: May 2008
Member
Frank, Thank you very much!
Previous Topic: How to write a query for 4th Thursday of November in each year
Next Topic: How to know what are the constraints defined on a table?
Goto Forum:
  


Current Time: Thu Mar 28 14:55:43 CDT 2024