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

You did not change table name, it is already hard coded into DYNA_QUERY_DEL. You only changed the value of the variable that is not used again. Is there any specific reason for using dynamic SQL? — Goran Stefanović
– Goran Stefanović, Commented Oct 10, 2017 at 14:52

Sabarish Mahalingam · Accepted Answer · 2017-10-13 06:33:18Z

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;

Yes, reassigning the same query is working. But do we have any alternative approach like refresh the cache or session of DYNA_TABLE_NAME variable?
yah... alternate looping method i have updated in my code check it.

Moudiz · Accepted Answer · 2017-10-10 13:51:37Z

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;

Yes, I also got the same idea. But reassign looks some odd for me. I am Java developer and new to PL/SQL program.
the value of DYNA_TABLE_NAME changed thats why you need to assign the DYNA_QUERY_DEL again. anyway can you tell me what is the output of the second execution ? it contains the table name UserData_Backup right?

Collectives™ on Stack Overflow

Oracle PL/SQL Issue in change the variable value dynamically

2 Answers 2

4 Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Related