RE: substitute object name with bind variables

Igor,
Thanks for your response. 

I was probably not clear with my question. I can write the way you suggested 
but tabname.table_name will be replaced by literal strings.

ie
delete table1 where id=:b2;
delete table2 where id=:b2;
delete table3 where id=:b2;
etc.

I would like to know if there is an alternative to writing the code to make use 
of bind variables - 
delete :b1 where id=:b2

thanks.

geraldine


> create or replace procedure testbind (v_id in number) as
>       
> begin
>       for tabname in (select table_name from user_tables where
> table_name like 'TAB%') 
>       loop
>               execute immediate 'delete ' || tabname.table_name || '
> where id=:b2' using v_id;
>       end loop;
> end;
> /
> 
> Igor Neyman, OCP DBA
> ineyman@xxxxxxxxxxxxxx
> 
> 
> 
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
> geraldine_2@xxxxxxxxxxx
> Sent: Tuesday, January 11, 2005 2:56 PM
> 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
> tabname.table_name. 
> 
> suggestions?
> 
> 
> create or replace procedure testbind (v_id in number) as
>       
> begin
>       for tabname in (select table_name from user_tables where
> table_name like 'TAB%') 
>       loop
>               execute immediate 'delete :b1 where id=:b2' using
> tabname.table_name,v_id;
>       end loop;
> end;
> /
> 
> thanks.
> 
> geraldine
> --
> http://www.freelists.org/webpage/oracle-l
> 
> 
--
http://www.freelists.org/webpage/oracle-l

Other related posts: