Oracle PL/SQL Issue in change the variable value dynamically

Question

I am trying to change the table name in query using dynamic query concept in Oracle.

The initial execution was good. But then once the table name modified with new value, even though it is showing as old value.

Check the below code....

    CREATE OR REPLACE PROCEDURE Test
AS
BEGIN
  DECLARE
    DELETE_OLD_YEARS NUMBER(2);
    RECORD_COUNT NUMBER(10);
    INTERVAL_UNIT VARCHAR2(4);
    DYNA_QUERY_DEL VARCHAR2(280);
    DYNA_TABLE_NAME VARCHAR(30);

  BEGIN

    INTERVAL_UNIT := 'YEAR';
    RECORD_COUNT := 0;
    DYNA_TABLE_NAME := 'UserData';
    DELETE_OLD_YEARS := 7;

    DYNA_QUERY_DEL := 'SELECT COUNT(*) 
                        FROM ' || DYNA_TABLE_NAME || ' UD 
                        WHERE UD.LOGIN_DT < (SYSDATE - NUMTOYMINTERVAL( :1, :2)) 
                        OR UD.LOGIN_DT IS NULL ORDER BY UD.LOGIN_DT DESC';

    -- Delete older than 7 years data
    DBMS_OUTPUT.PUT_LINE('Dynamic Query with UserData: ' || DYNA_QUERY_DEL);
    EXECUTE IMMEDIATE  DYNA_QUERY_DEL INTO RECORD_COUNT USING DELETE_OLD_YEARS, INTERVAL_UNIT;
    DBMS_OUTPUT.PUT_LINE('Record Count : ' || RECORD_COUNT);


    -- Delete older than 7 years data from backup table.
    DYNA_TABLE_NAME := 'UserData_Backup';
    DELETE_OLD_YEARS := 7;
    DBMS_OUTPUT.PUT_LINE('Dynamic Query with UserData_Backup : ' || DYNA_QUERY_DEL);
    EXECUTE IMMEDIATE  DYNA_QUERY_DEL INTO RECORD_COUNT USING DELETE_OLD_YEARS, INTERVAL_UNIT;
    DBMS_OUTPUT.PUT_LINE('Record Count 2 : ' || RECORD_COUNT);

  END;

END;
/

exec Test;

And the output is....

Dynamic Query with UserData : SELECT COUNT(*) 
                        FROM UserData UD 
                        WHERE UD.LOGIN_DT < (SYSDATE - NUMTOYMINTERVAL( :1, :2)) 
                        OR UD.LOGIN_DT IS NULL ORDER BY UD.LOGIN_DT DESC
Record Count : 6220
Dynamic Query with UserData_Backup : SELECT COUNT(*) 
                        FROM UserData UD 
                        WHERE UD.LOGIN_DT < (SYSDATE - NUMTOYMINTERVAL( :1, :2)) 
                        OR UD.LOGIN_DT IS NULL ORDER BY UD.LOGIN_DT DESC
Record Count 2 : 6220

But here the second query should prepare with UserData_Backup table.

Kindly help me to identify the issue....

Answer 1

dynamic query will act a one time in a program unless you are using For Loop. if in case you don't want to use loop, you can use same query to second dynamic execution and here i'm using looping functions.

code

CREATE OR REPLACE PROCEDURE Test
        AS
        BEGIN
          DECLARE
            DELETE_OLD_YEARS NUMBER(2);
            RECORD_COUNT NUMBER(10);
            INTERVAL_UNIT VARCHAR2(4);
            DYNA_QUERY_DEL VARCHAR2(280);
            DYNA_TABLE_NAME VARCHAR(30);
          BEGIN
            INTERVAL_UNIT := 'YEAR';
            RECORD_COUNT := 0;
            --DYNA_TABLE_NAME := 'UserData';
           -- DYNA_TABLE_NAME := 'UserData_Backup';
            DELETE_OLD_YEARS := 7;
            for c1 in (select 'UserData' as DYNA_TABLE_NAME from dual
                       union all
                       select 'UserData_Backup' as DYNA_TABLE_NAME from dual)
            loop
            DYNA_QUERY_DEL := 'SELECT COUNT(*) 
                                FROM ' || DYNA_TABLE_NAME || ' UD 
                                WHERE UD.LOGIN_DT < (SYSDATE - NUMTOYMINTERVAL( :1, :2)) 
                                OR UD.LOGIN_DT IS NULL ORDER BY UD.LOGIN_DT DESC';

            -- Delete older than 7 years data
            DBMS_OUTPUT.PUT_LINE('Dynamic Query with UserData: ' || DYNA_QUERY_DEL);
            EXECUTE IMMEDIATE  DYNA_QUERY_DEL INTO RECORD_COUNT USING DELETE_OLD_YEARS, INTERVAL_UNIT;
            RECORD_COUNT := RECORD_COUNT +1;
            end loop;
            DBMS_OUTPUT.PUT_LINE('Record Count 2 : ' || RECORD_COUNT);
          END;
        END;
        /
        exec Test;

Answer 2

re assign the variable DYNA_QUERY_DEL try to do it like this

    CREATE OR REPLACE PROCEDURE Test
AS
BEGIN
  DECLARE
    DELETE_OLD_YEARS NUMBER(2);
    RECORD_COUNT NUMBER(10);
    INTERVAL_UNIT VARCHAR2(4);
    DYNA_QUERY_DEL VARCHAR2(280);
    DYNA_TABLE_NAME VARCHAR(30);

  BEGIN

    INTERVAL_UNIT := 'YEAR';
    RECORD_COUNT := 0;
    DYNA_TABLE_NAME := 'UserData';
    DELETE_OLD_YEARS := 7;

    DYNA_QUERY_DEL := 'SELECT COUNT(*) 
                        FROM ' || DYNA_TABLE_NAME || ' UD 
                        WHERE UD.LOGIN_DT < (SYSDATE - NUMTOYMINTERVAL( :1, :2)) 
                        OR UD.LOGIN_DT IS NULL ORDER BY UD.LOGIN_DT DESC';

    -- Delete older than 7 years data
    DBMS_OUTPUT.PUT_LINE('Dynamic Query with UserData: ' || DYNA_QUERY_DEL);
    EXECUTE IMMEDIATE  DYNA_QUERY_DEL INTO RECORD_COUNT USING DELETE_OLD_YEARS, INTERVAL_UNIT;
    DBMS_OUTPUT.PUT_LINE('Record Count : ' || RECORD_COUNT);


    -- Delete older than 7 years data from backup table.
      DYNA_TABLE_NAME := 'UserData_Backup';

    DYNA_QUERY_DEL := 'SELECT COUNT(*) 
                        FROM ' || DYNA_TABLE_NAME || ' UD 
                        WHERE UD.LOGIN_DT < (SYSDATE - NUMTOYMINTERVAL( :1, :2)) 
                        OR UD.LOGIN_DT IS NULL ORDER BY UD.LOGIN_DT DESC';

    DELETE_OLD_YEARS := 7;
    DBMS_OUTPUT.PUT_LINE('Dynamic Query with UserData_Backup : ' || DYNA_QUERY_DEL);
    EXECUTE IMMEDIATE  DYNA_QUERY_DEL INTO RECORD_COUNT USING DELETE_OLD_YEARS, INTERVAL_UNIT;
    DBMS_OUTPUT.PUT_LINE('Record Count 2 : ' || RECORD_COUNT);

  END;

END;
/

exec Test;