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

dean (deanbrown3d@xxxxxxxxx) 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
.

Relevant Pages

  • Re: Can you pass a table name into a stored procedure?
    ... It was not my design, there are already 10 tables, 20 in some ... I pushd strongly for partitioning, ... oracle system. ... Prev by Date: ...
    (comp.databases.oracle.misc)
  • Re: Looking for a good book on object-oriented GUI programming
    ... that I'm lacking most of knowledge to do a good object-oriented design of my GUI application. ... However, if your client does more than simple DB-to-UI conversions, you may need additional partitioning on the client side. ... kind of soft target for OO techniques, so most OOA/D books will have examples. ... the most important things to grasp are basic OO design notions like abstraction and encapsulation. ...
    (comp.object)
  • Re: VHDL refactoring tools
    ... and why you got the interfaces between components wrong ... Whether you subscribe to or accept that as a 'good' design approach or not ... development is still disciplined and correct design partitioning for the ... Creating the most generally useful widget right out of the shoot is probably ...
    (comp.lang.vhdl)
  • Re: VHDL refactoring tools
    ... the changing of interfaces and hierarchy and the re-factoring of code ... Whether you subscribe to or accept that as a 'good' design approach or not ... whether the original partitioning was useful; ...
    (comp.lang.vhdl)
  • Re: ISE 10.0 finally with multi-threading and SV support ?
    ... If we pre-decide how to break up a design into ... Early partitioning costs some optimization potential and requires ...
    (comp.arch.fpga)