0

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?

4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.