Re: best way to "parameterize" a tablename?

From: gym dot scuba dot kennedy at gmail <>
Date: Fri, 22 Aug 2008 02:16:59 GMT
Message-ID: <vkprk.406$w51.21@trnddc01>

"Dan Blum" <> wrote in message news:g8l39p$510$
> wrote:

>> gym dot scuba dot kennedy at gmail <> wrote:
>> > <> 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

> --
> _______________________________________________________________________
> Dan Blum
> "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)

create or replace procedure myinsert(i_tablename in varchar2, i_arg1 in integer, i_arg2 in varchar2) as
  sql_string varchar2(200);
 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

Original text of this message