RE: substitute object name with bind variables

  • From: "Lex de Haan" <>
  • To: <geraldine_2@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 11 Jan 2005 21:15:18 +0100

impossible indeed. you should capture the table name in a client-side
variable, compose the SQL statement with string concatenation, and then use
execute immediate to send it for execution to the server. think about
features like cursor sharing (the whole purpose of bind variables) how can
you share an execution plan if you don't know which tables you want to
access, and which ccolumns you want to see?
kind regards,

Tom Kyte Seminar:

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of geraldine_2@xxxxxxxxxxx
Sent: Tuesday, January 11, 2005 20:56
To: oracle-l@xxxxxxxxxxxxx
Subject: substitute object name with bind variables

Is there a way to substitute object names  as such table_name or view name
with bind variables in the following sample code? 

obviously this code does not run because I cannot substitute :b1 with


create or replace procedure testbind (v_id in number) as
        for tabname in (select table_name from user_tables where table_name
like 'TAB%') 
                execute immediate 'delete :b1 where id=:b2' using
        end loop;




Other related posts: