Hi Experts,
I have the following control table.
ID S_OWNER SOURCE_TABLE A_OWNER ARC_TABLE CONDITION_COLUMN PERIOD_VALUE PERIOD_UNIT
1 wedb Auction_table wedb Arc_Auction_table Auction_date 15 Days
1 wedb Sales_table wedb ArcSales_table Sales_date 180 Days
1 hr Accounts_table hr Arc_Accounts_table Account_date 2 Years
2 concor Concur_table con Arc_Concur_table Last_update_date 4 Months
Like this 1000 entries are there in the control_table.
I want to pass all the columns from my control table dynamically.
I am able to pass all the columns dynamically,except the PERIOD_VALUE.
I have stucked up how to implement this condition.
My aim is to get the data which is <= sysdate-PERIOD_VALUES based on PERIOD_UNIT.
For Example:
For Auction_date column I want to get the data which 15 days old.
For Sales_date column I want to get the data which 180 days old.
For Account_date column I want to get the data which 2 yers old.
For Last_update_date column I want to get the data which 4 months old.
The conditions something like this.
condition_column<=(sysdate-15 days)
condition_column<=(sysdate-180 days)
condition_column<=(sysdate-2 years)
condition_column<=(sysdate-4 months)
This is my procedure.
CREATE OR REPLACE PROCEDURE WEDB.procedure_control(
P_ID IN NUMBER)
IS
CURSOR C
IS
SELECT ID,S_OWNER,SOURCE_TABLE,A_OWNER,ARC_TABLE,CONDITION_COLUMN,PERIOD_VALUE,PERIOD_UNIT
FROM wedb.CONTROL
WHERE ID = p_id
ORDER BY ID, SOURCE_TABLE;
rec C%ROWTYPE;
BEGIN
FOR I IN C
LOOP
EXECUTE IMMEDIATE
'INSERT INTO '
|| rec.A_OWNER
|| '.'
|| rec.ARC_TABLE
|| '(SELECT * FROM '
|| rec.S_OWNER
|| '.'
|| rec.SOURCE_TABLE
|| ' WHERE '
|| rec.CONDITION_COLUMN
|| '<=I want to pass dynamic condition'
|| ')';
EXECUTE IMMEDIATE
'DELETE FROM '
|| rec.S_OWNER
|| '.'
|| rec.SOURCE_TABLE
|| ' WHERE '
|| rec.CONDITION_COLUMN
|| '<= I want to pass dynamic condition'
|| '';
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE (
'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM);
END procedure_control;
/
Please help me how to implement this.
Thanks in advance.