CODEWITHSUNDEEP
oracle-databasewhere-clauseexecute-immediate
Elena LL
Elena LL

Reputation: 13

Need to use dynamic variables in where clause used in EXECUTE IMMEDIATE statement in ORACLE

ORACLE (using SQL DEVELOPER). I need to properly structure EXECUTE IMMEDIATE statement. I do not have "create" priveleges. The task is to get number of rows per table per date for dynamic list of tables/dates. I have the following:

 DECLARE CURSOR cur_table_name IS SELECT TABLE_NAME 
 FROM ALL_TABLES WHERE TABLE_NAME IN ('table_a', 'table_b', 'table_c');
 CURSOR cur_BEGIN_DATE IS 
  select to_date('2014-09-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + rownum -1 AS BEGIN_DATE,  
  to_date('2014-09-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + rownum -1 AS END_DATE from dual 
  Connect by level <= to_date('2014-09-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS') - to_date('2014-09-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + 1;

    var_total_rows NUMBER(15);
    var_table_name VARCHAR2 (50);
    var_bgn_date DATE;
    var_end_date DATE;

    BEGIN
  OPEN cur_TABLE_NAME;
  LOOP
    FETCH cur_TABLE_NAME INTO var_table_name;
    EXIT WHEN cur_TABLE_NAME%NOTFOUND;
    --testing output 
   DBMS_OUTPUT.PUT_LINE ('Table: '|| var_table_name);
    var_total_rows :=0;        
  OPEN cur_BEGIN_DATE; 
  LOOP
    FETCH cur_BEGIN_DATE INTO var_bgn_date, var_end_date;
    EXIT WHEN cur_BEGIN_DATE%NOTFOUND;
    --TESTING OUTPUT
     DBMS_OUTPUT.PUT_LINE ('DATES ARE: ' || var_bgn_date || ', ' ||var_end_date|| ' Table IS: '||var_table_name);

  --------THIS IS THE NOT WORKING STATEMENT DUE TO VARIABLES IN THE WHERE STATEMENT:

    execute immediate 'SELECT COUNT(*) FROM '||var_table_name || ' where  DTM >= '|| var_bgn_date ||' and DTM < '||var_end_date INTO var_total_rows;

   DBMS_OUTPUT.PUT_LINE (var_table_name||' '||var_bgn_date||' '||var_end_date ||' '||var_total_rows);

END LOOP;
CLOSE cur_BEGIN_DATE;
END LOOP;
CLOSE cur_TABLE_NAME;

END;

If I remove the variables from the where statement (just do 'Select * from || var_table_name into var_total_rows; ) this works. And if there is a static value in the where clause - it works (but loops through with the same date and I need the changing dates!). But I cannot make the syntax to work for dynamic variables in the where clause. Can this be done?

Appreciate your help!

Upvotes: 1

Views: 4272

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

Your var_bgn_date and var_end_date variables are of DATE type, but are being plugged into the dynamic statement as unquoted strings with implicit formatting based on the session NLS_DATE_FORMAT value. You'll get a generated statement like:

SELECT COUNT(*) FROM table_a  where  DTM >= 2014-09-25 00:00:00 and DTM < 2014-09-26 00:00:00

You could add escaped single quotes to turn that into a valid statement, still relying on implicit conversion back using the same NLS settings:

EXECUTE immediate 'SELECT COUNT(*) FROM '||var_table_name
  || ' where  DTM >= '''|| var_bgn_date ||''' and DTM < '''||var_end_date ||''''
INTO var_total_rows;

which would generate:

SELECT COUNT(*) FROM table_a where  DTM >= '2014-09-25 00:00:00' and DTM < '2014-09-26 00:00:00'

But really you should use bind variables to avoid any conversion to or from strings:

EXECUTE immediate 'SELECT COUNT(*) FROM '||var_table_name
  || ' where  DTM >= :bgn_date and DTM < :end_date'
INTO var_total_rows
USING var_bgn_date, var_end_date;

Upvotes: 2

Related Questions