Re: best way to "parameterize" a tablename?

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 22 Aug 2008 12:17:26 -0800
Message-ID: <48af10c6$1@news.victoria.tc.ca>


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 Received on Fri Aug 22 2008 - 15:17:26 CDT

Original text of this message