Re: best way to "parameterize" a tablename?
- From: yf110@xxxxxxxxxxxxxxxxxxx (Malcolm Dew-Jones)
- Date: 22 Aug 2008 12:17:26 -0800
DA Morgan (damorgan@xxxxxxxxx) wrote:
: Carlos wrote:
: > On 22 ago, 04:25, t...@xxxxxxxxx (Dan Blum) wrote:
: >> gym dot scuba dot kennedy at gmail <kenned...@xxxxxxxxxxx> wrote:
: >>
: >>
: >>
: >>> "Dan Blum" <t...@xxxxxxxxx> wrote in message
: >>> news:g8l39p$510$1@xxxxxxxxxxxxxxxxxxxx
: >>>> m...@xxxxxxxxx wrote:
: >>>>> gym dot scuba dot kennedy at gmail <kenned...@xxxxxxxxxxx> wrote:
: >>>>>> <m...@xxxxxxxxx> 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...@xxxxxxxxx
: >>>> "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...@xxxxxxxxx
: >> "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
.
- Follow-Ups:
- Re: best way to "parameterize" a tablename?
- From: DA Morgan
- Re: best way to "parameterize" a tablename?
- References:
- best way to "parameterize" a tablename?
- From: mh
- Re: best way to "parameterize" a tablename?
- From: gym dot scuba dot kennedy at gmail
- Re: best way to "parameterize" a tablename?
- From: mh
- Re: best way to "parameterize" a tablename?
- From: gym dot scuba dot kennedy at gmail
- Re: best way to "parameterize" a tablename?
- From: Carlos
- Re: best way to "parameterize" a tablename?
- From: DA Morgan
- best way to "parameterize" a tablename?
- Prev by Date: Re: best way to "parameterize" a tablename?
- Next by Date: Re: DECODE QuestionHi,
- Previous by thread: Re: best way to "parameterize" a tablename?
- Next by thread: Re: best way to "parameterize" a tablename?
- Index(es):
Relevant Pages
|
Loading
