RE: substitute object name with bind variables
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- 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,
Lex.
----------------------------------------------------------------
Tom Kyte Seminar: http://www.naturaljoin.nl/events/seminars.html
----------------------------------------------------------------
-----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
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
