Oracle Dynamic SQL – Execute Immediate with Column and Table Names

dynamic-sqloracle

I have to make dynamic query, which binds column names and table name dynamically using native dynamic sql in oracle pl/sql.

Here is my first try:

declare 
  komenda varchar2(255);
  v1 varchar2(10):='id_osrodek';
  v2 varchar2(10):='nazwa_o';
  v3 varchar2(10):='osrodki';
begin
  komenda := 'select distinct e.id_student, e.przedmiot, e.:1, o.:2 from egzaminy e
    inner join :3 o on o.:4 = e.:5
    inner join przedmioty p on e.id_przedmiot = p.id_przedmiot';
  execute immediate komenda using v1, v2, v3, v1, v1;
end;

And second:

declare 
  komenda varchar2(255);
  v1 varchar2(10):='id_osrodek';
  v2 varchar2(10):='nazwa_o';
  v3 varchar2(10):='osrodki';
begin
  komenda := 'select distinct e.id_student, p.nazwa_p, e.' || v1 || ', o.' || v2 || ' from egzaminy e
    inner join '|| v3 ||' o on o.' || v1 || ' = e.' || v1 || '
    inner join przedmioty p on e.id_przedmiot = p.id_przedmiot';
  execute immediate komenda;
end;

And I wonder why first doesn't work?

Best Answer

Because a bind variable is a value or value address, not a table or column name.

bind variable

A placeholder in a SQL statement that must be replaced with a valid value or value address for the statement to execute successfully. By using bind variables, you can write a SQL statement that accepts inputs or parameters at run time.

Binding happens after parsing, but a statement can not be parsed without knowing the required tables and columns.

Related Question