Re: best way to "parameterize" a tablename?
Date: Fri, 22 Aug 2008 02:16:59 GMT
Message-ID: <vkprk.406$w51.21@trnddc01>
"Dan Blum" <tool_at_panix.com> wrote in message
news:g8l39p$510$1_at_reader1.panix.com...
> mh_at_pixar.com wrote:
>> gym dot scuba dot kennedy at gmail <kennedyii_at_verizon.net> wrote: >> > <mh_at_pixar.com> wrote in message >> > > >> > > if tablename = 'FOO' >> > > insert into foo ... >> > > else if tablename = 'BAR' >> > > insert into bar ... (exact same stuff) >> > > etc... >> > > >> > Use bind >> > variables.) > >> I guess that's what I'm asking... how can I use bind variables or >> equivalent for the table name? >
> You can't.
> >> > You have an odd schema is each table has the same structure but a >> > different >> > name. > >> There's a reason for it, we basically don't want to intermingle >> one set of data with another. Think of the case where there's >> a million records of FOO and 100 records of BAR. An index >> won't be very useful, so you end up doing a lot of full >> table scans to extract the data, and we don't want to pay >> the big cost of FOO for the tiny data from BAR. The real >> case is a bit more complicated, but that's what it boils >> down to. >
> If you can distinguish the BAR records from the FOO records,
> an index on the distinguishing column(s) would in fact be very
> useful - it would let you get the BAR records quite quickly.
>
> However, if you really can't merge the tables, I would use EXECUTE
> IMMEDIATE. Using it with WHERE clauses which you don't have control
> over is a bad idea, but for table names it's simple to verify the
> string you are getting - just make sure it matches an entry in
> USER_TABLES.TABLE_NAME.
>
> --
> _______________________________________________________________________
> Dan Blum tool_at_panix.com
> "I wouldn't have believed it myself if I hadn't just made it up."
You can use bind variables for execute immediate. see using clause. I would recommend not using execute immediate to dynamically execute a user supplied string since you open yourself up to sql injection. So a match on an incomming tablename on user_tables and then using a string you supply (not the arg coming in would be better)
eg
create or replace procedure myinsert(i_tablename in varchar2, i_arg1 in
integer, i_arg2 in varchar2) as
sql_string varchar2(200);
begin
if (upper(i_tablename)='FOO') then
sql_string:='insert into foo( col1,col2) values(:a1,:a2)' ; elsif(upper(i_tablename)='BAR') then
sql_string:='insert into bar( col1,col2) values(:a1,:a2)' ;
...
end if;
execute immediate sql_string using i_arg1,i_arg2;
end;
/
On the other hand if the two tables are the same structure you could just have one attribute that designated which one. create table foobar as (
isfoo_or_bar varchar2(3),-- 'FOO' or 'BAR' or 1 and 2 as numeric(1) with
a 1,2 constraint, but with a constraint
col1both ...,
col2 both...,
constraint pk_foobar primary key (isfoo_or_bar , primary_key_column(s)) ) organization index;
So you would in essesnce have 1 table organized around foo or bar. The
records would be organized like
bar, record1,
bar record2,
...
foo, record1,
foo record2 ,
,,,
where the record1 and record 2 are in order by primary key (other than the
isfoo_or_bar column)
so select * from foobar where isfoo_or_bar ='FOO' will bet the records only
for foo. (large) and same for bar.
See index organized table. It allows you to partition a table by some
repeating key value. (for example, it is not partitioning)
Jim
Received on Thu Aug 21 2008 - 21:16:59 CDT