Home » SQL & PL/SQL » SQL & PL/SQL » Problem: Whats wrong with this PL/SQL procedure? (Oracle9i)
Problem: Whats wrong with this PL/SQL procedure? [message #324565] Tue, 03 June 2008 03:07 Go to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
In the procedure below, I am checking if the table contains data
which are less tha 1 month old.If its true than Truncate the table Snapshot_b and insert the selected rows from table b into table snapshot_b.

While executing the procedure for the 2nd time, the records added to snapshot_b is doubled.
Can anyone please let me know where I am wrong here.

Thanks,
Veddeta


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.SNAPSHOTY_B';
	INSERT INTO SNAPSHOT_B 
	SELECT * FROM B WHERE LAST_UPD_TMS < SYSTIMESTAMP - INTERVAL '30' DAY;
	COMMIT;
END IF;
END;



Re: Problem: Whats wrong with this PL/SQL procedure? [message #324566 is a reply to message #324565] Tue, 03 June 2008 03:11 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You truncate SNAPSHOTY_B but insert into SNAPSHOT_B

Re: Problem: Whats wrong with this PL/SQL procedure? [message #324567 is a reply to message #324566] Tue, 03 June 2008 03:18 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Thomas, Thank you very much!
Re: Problem: Whats wrong with this PL/SQL procedure? [message #324640 is a reply to message #324565] Tue, 03 June 2008 07:51 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Sheeesh, this is like the 20th question with the same piece of code.
Previous Topic: How to tuning this query
Next Topic: Select Statement inside a Trigger
Goto Forum:
  


Current Time: Fri Dec 02 14:26:39 CST 2016

Total time taken to generate the page: 0.59208 seconds