Re: best way to "parameterize" a tablename?

From: Malcolm Dew-Jones <>
Date: 22 Aug 2008 12:17:26 -0800
Message-ID: <48af10c6$>

DA Morgan ( wrote:
: 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.

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

	column X new_value Y
	@script parameters
	spool temp.sql

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


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

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).

	foreach (qw( ONE TWO THREE))
	{   print "
	SELECT x into Y FROM $_;

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

	create or replace procedure TRYING AS
	  Y number;

$0.10 Received on Fri Aug 22 2008 - 15:17:26 CDT

Original text of this message