Re: Can you pass a table name into a stored procedure?

dean (deanbrown3d_at_yahoo.com) wrote:
: It was not my design, there are already 10 tables, 20 in some
: instances. I pushd strongly for partitioning, but we did not use
: partitioning because our client did not have that enabled in their
: oracle system. We will look into it some time soon though, but until
: then we are stuck with 10 tables. The reason was that its faster to
: truncate a table than delete from it.

: I don't have oracle here at home, but I'm wondering if I can do
: something like this:

: with CURRENT_EXPAND_ROUTE as
: (
: case TABLE_INDEX of
: when 1: select A, B, C, D, E from EXPAND_ROUTE_001
: when 2: select A, B, C, D, E from EXPAND_ROUTE_002
: when 3: select A, B, C, D, E from EXPAND_ROUTE_003
: ...
: else select A, B, C, D, E from EXPAND_ROUTE_010
: ) .. do something with CURRENT_EXPAND_ROUTE

: I'll try this tomorrow. Thanks all!

One similarish idea, (you said "join" so I'll assume there is another table ABC being joined to each time).

	select * 
	from EXPAND_ROUTE_001 , ABC
	where EXPAND_ROUTE_001.col1 = ABC.col1
	  and :which_table == 1
	--
	union all
	--
	select * 
	from EXPAND_ROUTE_002 , ABC
	where EXPAND_ROUTE_002.col1 = ABC.col1
	  and :which_table == 2
	--
	union all
	--
	... etc ...

in other words, simply do them all, secure in the knowledge that only the correct one will provide data (based on a bind variable "which_table").

If some sites have different numbers of tables then make the code slightly different for each site, or define a bunch of dummy views for the tables that don't exist on some sites.

$0.10 Received on Thu Dec 08 2005 - 00:08:01 CST