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

From: dean <deanbrown3d_at_yahoo.com>
Date: 8 Dec 2005 08:03:12 -0800
Message-ID: <1134057792.253151.147030@o13g2000cwo.googlegroups.com>

Well, I could get the method above to work, if there were no more than 1 record in the expand route tables. As soon as the tables held more than one record, the usual oracle "returns more than one row for single row query" exception is thrown.

However, I did try you method Malcolm, in fact I had in mind to try a union at some point anyway, and it does work. However, although its fast enough doing counts, I think we would lose all indexes on individual tables if we we try joining to them. This works, anyway:

CREATE OR REPLACE PROCEDURE "ACT_JUN"."ZAP"
(TABLE_NUN in number := 1)

IS
BEGIN
  insert into A (B) select CNT from
  (
    with CURRENT_EXPAND_ROUTE_TABLE as
(

      select * from
      (
        select TRAIN_EXPANDED_ROUTE_I, train_i, p_version_i from
TRAIN_EXPANDED_ROUTE_001 where TABLE_NUN = 1
        union all
        select TRAIN_EXPANDED_ROUTE_I, train_i, p_version_i from
TRAIN_EXPANDED_ROUTE_002 where TABLE_NUN = 2
        union all
        select TRAIN_EXPANDED_ROUTE_I, train_i, p_version_i from
TRAIN_EXPANDED_ROUTE_003 where TABLE_NUN = 3
        union all
        select TRAIN_EXPANDED_ROUTE_I, train_i, p_version_i from
TRAIN_EXPANDED_ROUTE_004 where TABLE_NUN = 4
        union all
        select TRAIN_EXPANDED_ROUTE_I, train_i, p_version_i from
TRAIN_EXPANDED_ROUTE_005 where TABLE_NUN = 5
      )

    )
    select count(*) as CNT from CURRENT_EXPAND_ROUTE_TABLE   );
END; Dean Received on Thu Dec 08 2005 - 10:03:12 CST