pl/sql - to_date not working with execute immediate parameter

Question

i wanna be able to execute my below proc like so:

exec procname('29-JAN-2011');

proc code is:

PROCEDURE procname(pardate VARCHAR2) IS

  vardate DATE := to_date(pardate, 'DD-MON-YYYY');
  SQLS VARCHAR2(4000);

BEGIN    

  SQLS := 'SELECT cola, colb
             FROM tablea 
            WHERE TRUNC(coldate) = TRUNC(TO_DATE('''||pardate||''',''DD/MON/YYYY''))';

  EXECUTE IMMEDIATE SQLS;

END;

It keeps throwing error:

ORA-00904: "JAN": invalid identifier.

It compiles, but it throws the error when I run this command:

EXEC procname('29-JAN-2011');

Answer 1

You declare a variable which casts the input parameter to a date: why not use it?

Also, the TRUNC() applied to a date removes the time element. You don't need it here because the value you're passing has no time.

So, your code should be:

PROCEDURE procname(pardate VARCHAR2) IS

  vardate DATE := to_date(pardate, 'DD-MON-YYYY');
  SQLS VARCHAR2(4000)  := 'select cola, colb FROM tablea 
           WHERE TRUNC(coldate) = :1';

   l_a tablea.cola%type;
   l_b tablea.colb%type;
BEGIN    
  EXECUTE IMMEDIATE SQLS 
      into l_a, l_b
      using vardate;
END;  

Specifying the dynamic SQL statement with a bind variable and executing it with the USING syntax is a lot more efficient. Note that we still have to SELECT into some variables.