Re: best way to "parameterize" a tablename?

From: Carlos <miotromailcarlos_at_netscape.net>
Date: Mon, 25 Aug 2008 00:06:16 -0700 (PDT)
Message-ID: <e1b098f8-ec47-42f6-a1e0-69d1a9d06482@f63g2000hsf.googlegroups.com>


On 23 ago, 06:06, DA Morgan <damor..._at_psoug.org> wrote:
> Malcolm Dew-Jones wrote:
> > DA Morgan (damor..._at_psoug.org) wrote:
> > : 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."
> > : >
> > : > 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.
>

> > Surely you misunderstand.
>

> > sed is used by the programmer who is writing the module so that a single
> > statement in the source code will be repeated multiple times when the
> > source code is compiled.
>

> > SQL "compiled" using sqlplus can be manipulated in many ways. A quick
> > summary of techniques to consider
>

> > define
> > column X new_value Y
> > @script parameters
> > spool temp.sql
> > @temp.sal
>

> > the @ command in particular is extremely useful for sharing code snippets
> > between many modules, since it accepts parameters, so it acts very much
> > like a multi line parameterized #define in C. The biggest problem is that
> > the parameters passed in to @ cannot vary within a statement since the
> > code is expanded just once, so you can't do things like
>

> > create procedure WONT_WORK as
> > begin
>

> > @DO_IF_TABLE_THEN_INSERT 'TABLE_ONE'
> > @DO_IF_TABLE_THEN_INSERT 'TABLE_TWO'
> > @DO_IF_TABLE_THEN_INSERT 'TABLE_THREE'
> > end;
> > /
>

> > For more complex things I might write a package that generates the code I
> > need and include that as part of the development system
>

> > -- SQLPLUS SCRIPT
> > spool TEMP1.SQL
> > exec my_generator.generate_insert('TABLE_ONE');
> > exec my_generator.generate_insert('TABLE_TWO');
> > exec my_generator.generate_insert('TABLE_THREE');
> > spool off
>

> > create or replace procedure TRY_PROC AS
> > begin
> > @TEMP.SQL
> > end;
> > /
>

> > Personally I don't use sed much, I prefer perl. The following does
> > nothing useful, but it does show how a sqlplus script can be self
> > contained and generate its own code. (by "self contained" I mean that all
> > the logic of the code is contained in this single file).
>

> > --
> > -- TRYING.SQL
> > --
> > /*
> > #!perl
> > foreach (qw( ONE TWO THREE))
> > { print "
> > SELECT x into Y FROM $_;
> > "
> > }
> > __END__
> > */
>

> > host cmd/c perl -x trying.sql > temp.sql
>

> > create or replace procedure TRYING AS
> > Y number;
> > begin
> > @temp.sql
> > end;
> > /
>

> > $0.10
>

> Makes sense. Not too disimilar from the example I have on my
> UTL_FILE page where I use UTL_FILE to dynamically create
> SQL*Loader control files.
>

> Given a choice of sed, perl, or UTL_FILE ... for this purpose
> ... I'd take UTL_FILE. Easier to handle security, only one
> skill set required, no need for the power of the other two.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

DA & Malcolm:

I've been out for the weekend...

Malcolm explains my point OK.

We use 'sed' a lot in server scripts with Teradata tools. Teradata tools are not as versatile as sql*plus and we use many bash shell scripts. Some of them use 'sed' among some other Linux/bash fine commands in a way that the OP could find helpful, so that was my point.

Cheers.

Carlos. Received on Mon Aug 25 2008 - 02:06:16 CDT

Original text of this message