RE: substitute object name with bind variables

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Tue, 11 Jan 2005 15:39:33 -0500
Message-ID: <C9995D8C5E0DDA4A8FF9D68EE666CE0702A97213@exchsen0a1ma>

Geraldine,

Your own proc will do it:

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;
/

Tom

-----Original Message-----
From: geraldine_2_at_comcast.net [mailto:geraldine_2_at_comcast.net] Sent: Tuesday, January 11, 2005 3:20 PM
To: Igor Neyman; oracle-l_at_freelists.org
Subject: 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_at_perceptron.com
>
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
> geraldine_2_at_comcast.net
> Sent: Tuesday, January 11, 2005 2:56 PM
> To: oracle-l_at_freelists.org
> 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
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 11 2005 - 14:41:54 CST