CODEWITHSUNDEEP
sqloracle-databasedynamic-sql
kr_v
kr_v

Reputation: 139

Dynamic SQL table name as variable

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');

Upvotes: 0

Views: 292

Answers (1)

1pluszara
1pluszara

Reputation: 1528

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.

Upvotes: 2

Related Questions