Re: best way to "parameterize" a tablename?

Carlos wrote:
> 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."

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

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org