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
– eaolson, Commented Feb 21, 2019 at 0:06
It's also pointless, even in the answer version, because - as pointed out in your previous question - 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
– Alex Poole, Commented Feb 21, 2019 at 0:12

1pluszara · Accepted Answer · 2019-02-20 21:42:20Z

2

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.

answered Feb 20, 2019 at 21:42

1pluszara

1,5483 gold badges16 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic SQL table name as variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related