Re: best way to "parameterize" a tablename?

Malcolm Dew-Jones wrote:
> DA Morgan (damorgan_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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org