Appropriate Use of Bind Variables

Question

In the following PL/SQL block, a bind variable is used in the WHERE clause:

declare
symbol varchar2(6) := 'EPIC';
begin
execute immediate 'delete from stock where symbol = :symbol'
using symbol;
end;
/

This block executes successfully, however, something like the following will fail:

declare
symbol varchar2(15) := 'employees';
begin 
execute immediate 'delete from :symbol where last_name = ''Kochar'''
using symbol
end;
/

My question is: can we use bind variables in any other context besides passing a value to a WHERE clause like in the first example?

Answer 1

You can bind into your SQL statement only those expressions(literals, variables, complex expressions) that replace placeholders for data values inside the dynamic string. you cannot bind in the names of schema elements(tables, columns, etc) or entrie chunks of the SQL statement. For those parts of your string, you must use concatenation(operator)

So Use as in the following :

SQL> create table employees(empid int,last_name varchar2(50));

Table created

SQL> insert into employees values(111,'Kochar');

1 row inserted

SQL> select * from employees;

EMPID LAST_NAME
----- ----------
  111 Kochar

SQL> 
SQL> declare
  2    symbol varchar2(15) := 'employees';
  3  begin
  4    execute immediate 'delete '||symbol||' where last_name = ''Kochar''';
  5  end;
  6  /

PL/SQL procedure successfully completed

SQL> select * from employees;

EMPID LAST_NAME
----- ----------
-- i.e. no row(s) returned.