Re: best way to "parameterize" a tablename?

gym dot scuba dot kennedy at gmail <kennedyii_at_verizon.net> wrote:

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

Yes, you can use bind variables with execute immediate - but not for the TABLE NAME. The OP does not want to run SQL entered by a user, he wants to run a given SQL statement against one of two tables without having the statement in the code twice.

-- 
_______________________________________________________________________
Dan Blum					         tool_at_panix.com	
"I wouldn't have believed it myself if I hadn't just made it up."