Dynamic where condition in a stored procedure

Question

I am using Oracle 11g. I am trying to get the table data with this procedure:

CREATE OR REPLACE PROCEDURE test(p_table IN varchar2)
IS
v_sqltxt varchar2(4000);
BEGIN
v_sqltxt:='select count(*) from '||p_table;
dbms_output.put_line(v_sqltxt);
END;
/

But I am unable to give the where condition dynamically. How can I do that?

Answer 1

You'll need to execute your SQL statement. Use execute immediate for this. And because you're selecting a value you'll need to execute immediate ... into it, something like this (replacing 3 > 2 with your own where condition:

CREATE OR REPLACE PROCEDURE test(p_table IN varchar2) IS
  v_sqltxt varchar2(4000);
  v_count integer;
BEGIN
  v_sqltxt:='select count(*) from '||p_table||' where 3 > 2';

  execute immediate v_sqltxt into v_count;

  dbms_output.put_line(v_count);
END;
/