Re: best way to "parameterize" a tablename?

On 22 ago, 04:25, t..._at_panix.com (Dan Blum) wrote:
> gym dot scuba dot kennedy at gmail <kenned..._at_verizon.net> wrote:
>
>
>
> > "Dan Blum" <t..._at_panix.com> wrote in message
> >news:g8l39p$510$1_at_reader1.panix.com...
> > > m..._at_pixar.com wrote:
> > >> gym dot scuba dot kennedy at gmail <kenned..._at_verizon.net> wrote:
> > >> > <m..._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 t..._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 t..._at_panix.com
> "I wouldn't have believed it myself if I hadn't just made it up."

You can also use 'sed' (if in Linux) with your sql script in order to find and substitute the table name (which you should have marked with some kind of token) and pipe it to sqlplus.

HTH. Cheers.

Carlos. Received on Fri Aug 22 2008 - 01:57:23 CDT