Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Please help me on how to pass date condition Dynamically.

user9077483Dec 27 2012 — edited Dec 28 2012
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 25 2013
Added on Dec 27 2012
15 comments
615 views