Home » Infrastructure » Unix » Problem: How to schedule and execute the PL/SQL Procedure through shell scripting (Unix,Oracle9I)
Problem: How to schedule and execute the PL/SQL Procedure through shell scripting [message #324571] Tue, 03 June 2008 03:33 Go to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
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

Re: Problem: How to schedule and execute the PL/SQL Procedure through shell scripting [message #324583 is a reply to message #324571] Tue, 03 June 2008 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When I said (http://www.orafaq.com/forum/t/119771/102589/):
Quote:
Go to Unix forum, there are many examples of how to use sqlplus inside a shell script.

I meant you have to search for them not just repost the question.

Regards
Michel

Re: Problem: How to schedule and execute the PL/SQL Procedure through shell scripting [message #324649 is a reply to message #324571] Tue, 03 June 2008 08:46 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
VEDDETA,
why do you post invalid PL/SQL code?
Why do you expect code with syntax errors to actually run when it fails to compile cleanly?
Previous Topic: Clean up the Oracle database
Next Topic: problem starting oracle listener
Goto Forum:
  


Current Time: Thu Mar 28 07:28:56 CDT 2024