Re: best way to "parameterize" a tablename?

From: DA Morgan <>
Date: Fri, 22 Aug 2008 09:50:49 -0700
Message-ID: <>

Carlos wrote:
> On 22 ago, 04:25, (Dan Blum) wrote:

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

Give an end-user access to sed on a *NIX host? Surely you jest.

Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Fri Aug 22 2008 - 11:50:49 CDT

Original text of this message