RE: substitute object name with bind variables

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Tue, 11 Jan 2005 21:15:18 +0100
Message-ID: <41628E4300010A84@smtp-wifi.orange.fr> (added by postmaster@orange.fr)

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,  

Lex.  

---

Tom Kyte Seminar: http://www.naturaljoin.nl/events/seminars.html

---

 

-----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 20:56
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 Received on Tue Jan 11 2005 - 14:16:33 CST