Re: best way to "parameterize" a tablename?

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."