Dynamic SQL table name as variable

Question

I have this procedure which is working, 2 parameters can be passed when calling procedure and it executes the select query.

create or replace procedure dynamic_sql
(input1 varchar2, input2 varchar2)
as begin
execute immediate
'select :variable1, :variable2 from emp'
using input1,input2;
end;
/
exec dynamic_sql('ename','job');

In the same way I try to add third variable which will replace the table Emp, but it doesn't work, passed in table name is 100% correct. This is the code that doesn't work (ORA-00903: invalid table name):

create or replace procedure dynamic_sql
(input1 varchar2, input2 varchar2,input_table varchar2)
as begin
execute immediate
'select :variable1, :variable2 from :variable3'
using input1,input2,input_table;
end;
/
exec dynamic_sql('ename','job','emp');

1pluszara has answered your question, but I'd like to point out your procedure is pointless, since you're just selecting the strings 'ename' and 'job' from this table, which is almost certainly not what you want. — eaolson, Feb 21 '19 at 00:06
It's also pointless, even in the answer version, because - as pointed out in [your previous question](https://stackoverflow.com/q/54792603/266304) - you have to select *into* something. Without that the dynamic query is parsed - and in your version the error is thrown at that point - but not executed. — Alex Poole, Feb 21 '19 at 00:12

score 2 · Answer 1 · answered Feb 20 '19 at 21:42

Try something like this: Its due to the parsing of the table name before execution.

create or replace procedure dynamic_sql
(input1 varchar2, input2 varchar2,input_table varchar2)
as
str varchar2(1000) := NUll; 
begin
str := 'select '||input1||','|| input2 ||' from '||input_table;
execute immediate str;
end;
/
exec dynamic_sql('ename','job','emp');

Procedure created.
 PL/SQL procedure successfully completed.

Dynamic SQL table name as variable

1 Answers1

Linked