This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Dec 28, 2012 4:16 AM by user9077483 RSS

Please help me on how to pass date condition Dynamically.

user9077483 Newbie
Currently Being Moderated
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.
  • 1. Re: Please help me on how to pass date condition Dynamically.
    NSK2KSN Journeyer
    Currently Being Moderated
    can you try executing below
    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 rec 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 <=(sysdate-rec.period_value)
                || ')';
     
             EXECUTE IMMEDIATE
                   'DELETE FROM '
                || rec.S_OWNER
                || '.'
                || rec.SOURCE_TABLE
                || ' WHERE '
                || rec.CONDITION_COLUMN <=(sysdate-rec.period_value)
                || '';
     
          COMMIT;
       END LOOP;
    EXCEPTION
       WHEN OTHERS
       THEN
          ROLLBACK;
          DBMS_OUTPUT.PUT_LINE (
             'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM);
    END procedure_control;
    / 
  • 2. Re: Please help me on how to pass date condition Dynamically.
    Suman Rana Explorer
    Currently Being Moderated
    Hi.. try to use the condition like...

    CONDITION_COLUMN <= DECODE(rec.PERIOD_UNIT, 'Days', (sysdate - rec.PERIOD_VALUE), 'Months', ADD_MONTHS(sysdate, -rec.PERIOD_VALUE), 'Years', ADD_MONTHS(sysdate, -(12*rec.PERIOD_VALUE)) )

    since sample data is unavailable so I am not implementing the whole logic...
  • 3. Re: Please help me on how to pass date condition Dynamically.
    Suman Rana Explorer
    Currently Being Moderated
    what is this ???

    user9077483 Newbie
    Handle:      user9077483
    Status Level:      Newbie
    Registered:      Jan 30, 2010
    Total Posts:      269
    Total Questions:      142 (142 unresolved)

    You mean to say none of your questions were resolved!!!!
  • 4. Re: Please help me on how to pass date condition Dynamically.
    kendenny Expert
    Currently Being Moderated
    EXECUTE IMMEDIATE
                   'INSERT INTO '
                || rec.A_OWNER
                || '.'
                || rec.ARC_TABLE
                || '(SELECT * FROM '
                || rec.S_OWNER
                || '.'
                || rec.SOURCE_TABLE
                || ' WHERE '
                || rec.CONDITION_COLUMN
                || '<='
                || CASE WHEN lower(rec.PERIOD_UNIT) = 'days'
                        THEN 'sysdate - '||rec.period_value
                        WHEN lower(rec.PERIOD_UNIT) = 'months'
                        THEN 'add_months(sysdate,'||rec.PERIOD_UNIT||'*-1)'
                        WHEN lower(rec.PERIOD_UNIT) = 'years'
                        THEN 'add_months(sysdate,'||rec.PERIOD_UNIT||'*-12)'
                   END
                || ')';
  • 5. Re: Please help me on how to pass date condition Dynamically.
    user9077483 Newbie
    Currently Being Moderated
    Thanks for your reply.

    I tried to incorporate your code in my statement I am getting the error

    EXECUTE IMMEDIATE
    'INSERT INTO '
    || rec.ARCHIVE_TABLE_OWNER_NAME
    || '.'
    || rec.ARCHIVE_TABLE_NAME
    || '(SELECT * FROM '
    || rec.SOURCE_TABLE_OWNER_NAME
    || '.'
    || rec.SOURCE_TABLE_NAME
    || ' WHERE '
    || rec.CONDITION_COLUMN_NAME
    || '<='DECODE(rec.PERIOD_UNIT,''Days'',(sysdate - rec.PERIOD_VALUE),
    ''Months'', ADD_MONTHS(sysdate,-rec.PERIOD_VALUE),
    ''Years'', ADD_MONTHS(sysdate,-(12*rec.PERIOD_VALUE)))
    || ')';

    Please help me.
  • 6. Re: Please help me on how to pass date condition Dynamically.
    kendenny Expert
    Currently Being Moderated
    That's not what I had in my post is it. Note you have rec.column stuff inside quotes.
  • 7. Re: Please help me on how to pass date condition Dynamically.
    971895 Journeyer
    Currently Being Moderated
    as per you code not necessary execute immedate.. because database contains the table...
  • 8. Re: Please help me on how to pass date condition Dynamically.
    user9077483 Newbie
    Currently Being Moderated
    Thanks for your reply.
    I have incorporated your CASE statement in code but I am getting the error.

    ORA-00936: missing expression
    ORA-06512: at line 21

    DECLARE
    CURSOR C
    IS
    SELECT GROUP_ID,
    SOURCE_TABLE_OWNER_NAME,
    SOURCE_TABLE_NAME,
    ARCHIVE_TABLE_OWNER_NAME,
    ARCHIVE_TABLE_NAME,
    CONDITION_COLUMN_NAME,
    RETENTION_VALUE,
    RETENTION_UNIT,
    FLAG
    FROM APPS_GLOBAL.control_table1
    WHERE GROUP_ID = 1
    ORDER BY GROUP_ID, source_table_name;

    rec c%ROWTYPE;
    BEGIN
    FOR rec IN C
    LOOP
    EXECUTE IMMEDIATE
    'INSERT INTO '
    || rec.ARCHIVE_TABLE_OWNER_NAME
    || '.'
    || rec.ARCHIVE_TABLE_NAME
    || '(SELECT * FROM '
    || rec.SOURCE_TABLE_OWNER_NAME
    || '.'
    || rec.SOURCE_TABLE_NAME
    || ' WHERE '
    || rec.CONDITION_COLUMN_NAME
    || '<='
    || CASE
    WHEN LOWER (rec.RETENTION_UNIT) = 'days'
    THEN
    'sysdate - ' || rec.RETENTION_VALUE
    WHEN LOWER (rec.RETENTION_UNIT) = 'months'
    THEN
    'add_months(sysdate,' || rec.RETENTION_VALUE || '*-1)'
    WHEN LOWER (rec.RETENTION_UNIT) = 'years'
    THEN
    'add_months(sysdate,' || rec.RETENTION_VALUE || '*-12)'
    END
    ||')';
    END LOOP;

    COMMIT;
    END;
    /

    Please help me.
  • 9. Re: Please help me on how to pass date condition Dynamically.
    kendenny Expert
    Currently Being Moderated
    What does this query give you?
    select distinct retention_unit from APPS_GLOBAL.control_table1
     where group_id  = 1;
    If there's anything other than "Days", "Months", or "Years" then that's the problem.
  • 10. Re: Please help me on how to pass date condition Dynamically.
    APC Oracle ACE
    Currently Being Moderated
    You can't use CASE in an EXECUTE IMMEDATE statement like that. You need to build up a string dynamically, and execute it instead.
        v_stmt := 'INSERT INTO '
             || rec.ARCHIVE_TABLE_OWNER_NAME
             || '.'
             || rec.ARCHIVE_TABLE_NAME
             || '(SELECT * FROM '
             || rec.SOURCE_TABLE_OWNER_NAME
             || '.'
             || rec.SOURCE_TABLE_NAME
             || ' WHERE '
             || rec.CONDITION_COLUMN_NAME
             || '<=  ';
    
    CASE LOWER (rec.RETENTION_UNIT)  
        WHEN  'days'
        THEN
             stmt := stmt ||' sysdate - ' || rec.RETENTION_VALUE; 
        WHEN   'months'
       THEN
           stmt := stmt ||' add_months(sysdate,' || rec.RETENTION_VALUE || '*-1)';
      ELSE
          stmt := stmt ||' add_months(sysdate,' || rec.RETENTION_VALUE || '*-12)';
    END CASE;
    execute immediate stmt;
    Cheers, APC

    Edited by: APC on Dec 27, 2012 4:21 PM
  • 11. Re: Please help me on how to pass date condition Dynamically.
    user9077483 Newbie
    Currently Being Moderated
    I have implemented as you said,but its giving my the error.

    ORA-00907: missing right parenthesis
    ORA-06512: at line 54

    I am not able to identified why this error is coming please help me.

    DECLARE
    stmt LONG;

    CURSOR C
    IS
    SELECT GROUP_ID,
    SOURCE_TABLE_OWNER_NAME,
    SOURCE_TABLE_NAME,
    ARCHIVE_TABLE_OWNER_NAME,
    ARCHIVE_TABLE_NAME,
    CONDITION_COLUMN_NAME,
    RETENTION_VALUE,
    RETENTION_UNIT,
    FLAG
    FROM APPS_GLOBAL.control_table1
    WHERE GROUP_ID = 1
    ORDER BY GROUP_ID, source_table_name;

    rec c%ROWTYPE;
    BEGIN
    FOR rec IN C
    LOOP
    stmt :=
    'INSERT INTO '
    || rec.ARCHIVE_TABLE_OWNER_NAME
    || '.'
    || rec.ARCHIVE_TABLE_NAME
    || '(SELECT * FROM '
    || rec.SOURCE_TABLE_OWNER_NAME
    || '.'
    || rec.SOURCE_TABLE_NAME
    || ' WHERE '
    || rec.CONDITION_COLUMN_NAME
    || '<= ';

    CASE LOWER (rec.RETENTION_UNIT)
    WHEN 'd'
    THEN
    stmt := stmt || ' sysdate - ' || rec.RETENTION_VALUE;
    WHEN 'm'
    THEN
    stmt :=
    stmt
    || ' add_months(sysdate,'
    || rec.RETENTION_VALUE
    || '*-1)';
    ELSE
    stmt :=
    stmt
    || ' add_months(sysdate,'
    || rec.RETENTION_VALUE
    || '*-12)';
    END CASE;
    EXECUTE IMMEDIATE stmt;
    END LOOP;

    COMMIT;
    END;
    /

    Thanks.
  • 12. Re: Please help me on how to pass date condition Dynamically.
    user9077483 Newbie
    Currently Being Moderated
    I am providing DDL and insert statetment for my control table.
    CREATE TABLE APPS_GLOBAL.CONTROL_TABLE1
    (
      GROUP_ID                  NUMBER,
      SOURCE_TABLE_OWNER_NAME   VARCHAR2(50 CHAR),
      SOURCE_TABLE_NAME         VARCHAR2(50 CHAR),
      ARCHIVE_TABLE_OWNER_NAME  VARCHAR2(50 CHAR),
      ARCHIVE_TABLE_NAME        VARCHAR2(50 CHAR),
      CONDITION_COLUMN_NAME     VARCHAR2(50 CHAR),
      RETENTION_VALUE           NUMBER,
      RETENTION_UNIT            CHAR(1 CHAR),
      FLAG                      CHAR(1 CHAR)
    );
    Insert into APPS_GLOBAL.CONTROL_TABLE1
       (GROUP_ID, SOURCE_TABLE_OWNER_NAME, SOURCE_TABLE_NAME, ARCHIVE_TABLE_OWNER_NAME, ARCHIVE_TABLE_NAME, 
        CONDITION_COLUMN_NAME, RETENTION_VALUE, RETENTION_UNIT, FLAG)
     Values
       (1, 'APPS_GLOBAL', 'EMP', 'APPS_GLOBAL', 'A_RAM', 
        'HIREDATE', 1, 'Y', 'A');
    
    Insert into APPS_GLOBAL.CONTROL_TABLE1
       (GROUP_ID, SOURCE_TABLE_OWNER_NAME, SOURCE_TABLE_NAME, ARCHIVE_TABLE_OWNER_NAME, ARCHIVE_TABLE_NAME, 
        CONDITION_COLUMN_NAME, RETENTION_VALUE, RETENTION_UNIT, FLAG)
     Values
       (1, 'APPS_GLOBAL', 'DOJ_RAM', 'APPS_GLOBAL', 'B_RAM', 
        'DOJ', 180, 'D', 'A');
    
    Insert into APPS_GLOBAL.CONTROL_TABLE1
       (GROUP_ID, SOURCE_TABLE_OWNER_NAME, SOURCE_TABLE_NAME, ARCHIVE_TABLE_OWNER_NAME, CONDITION_COLUMN_NAME, 
        RETENTION_VALUE, RETENTION_UNIT, FLAG)
     Values
       (1, 'APPS_GLOBAL', 'STUUDENT_RAM', 'APPS_GLOBAL', 'STUD_DOJ', 
        2, 'Y', 'P');
  • 13. Re: Please help me on how to pass date condition Dynamically.
    padders Pro
    Currently Being Moderated
    So your retention unit is 'D', 'M', or 'Y' and you should amend the examples you have been given accordingly.
  • 14. Re: Please help me on how to pass date condition Dynamically.
    user9077483 Newbie
    Currently Being Moderated
    I have added ||')' paranthasis for each CASE stament to avoiid the error.
    Now I am getting the below error
    ORA-00903: invalid table name
    ORA-06512: at line 42

    I am using below code.

    Please find my below code.
    DECLARE
       stmt   LONG;
    
       CURSOR C
       IS
            SELECT GROUP_ID,
                   SOURCE_TABLE_OWNER_NAME,
                   SOURCE_TABLE_NAME,
                   ARCHIVE_TABLE_OWNER_NAME,
                   ARCHIVE_TABLE_NAME,
                   CONDITION_COLUMN_NAME,
                   RETENTION_VALUE,
                   RETENTION_UNIT,
                   FLAG
              FROM APPS_GLOBAL.control_table1
             WHERE GROUP_ID = 1
          ORDER BY GROUP_ID, source_table_name;
    
       rec    c%ROWTYPE;
    BEGIN
       FOR rec IN C
       LOOP
    stmt := 'INSERT INTO '
             || rec.ARCHIVE_TABLE_OWNER_NAME
             || '.'
             || rec.ARCHIVE_TABLE_NAME
             || '(SELECT * FROM '
             || rec.SOURCE_TABLE_OWNER_NAME
             || '.'
             || rec.SOURCE_TABLE_NAME
             || ' WHERE '
             || rec.CONDITION_COLUMN_NAME
             || '<=';
     
    CASE LOWER (rec.RETENTION_UNIT)
        WHEN 'd'
        THEN
         stmt:= stmt||'sysdate -'||rec.RETENTION_VALUE||')';
         ELSE
         stmt := stmt||'add_months(sysdate,'|| rec.RETENTION_VALUE||'*-12)'||')';
    END CASE;
    execute immediate stmt;
       END LOOP;
    
       COMMIT;
    END;
    /
    The values for the field RETENTION_UNIT in my control_table1 are D and Y.

    Before making the date comparison condition it was working fine.
    If I made it as dynamic why I am getting the error.
    If I use condition as below it's working fine.
    EXECUTE IMMEDIATE
                   'INSERT INTO '
                || I.ARCHIVE_TABLE_OWNER_NAME
                || '.'
                || I.ARCHIVE_TABLE_NAME
                || '(SELECT * FROM '
                || I.SOURCE_TABLE_OWNER_NAME
                || '.'
                || I.SOURCE_TABLE_NAME
                || ' WHERE '
                || I.CONDITION_COLUMN_NAME
                || '<=ADD_MONTHS(SYSDATE,-24)'
                || ')';
    Please help me .
    Thanks.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points