1 2 Previous Next 15 Replies Latest reply: Dec 28, 2012 6:16 AM by user9077483 RSS

    Please help me on how to pass date condition Dynamically.

    user9077483
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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