You can use partitioning and create the tables. Then when the tables are
imported (under someother name) just do a switch partition. (it is just a
data dictionary update so it is very fast) That way you wouldn't have to
use a complex dynamic sql scheme.
Jim
"Graham Turner" <turnergw_at_logica.nojunk.com> wrote in message
news:1011713692.801417_at_ernani.logica.co.uk...
> >While you can use dynamic sql of this (execute immedite or dbms_sql) I
think
> >you might have a design problem. Why are the tables not in existance, or
> >may not, are you dynamically creating temporary tables? Not a good idea.
>
> To clarify: I'm using Oracle 8.1.6 running on DEC Unix V4.0F
>
> The tables being used are of the type HISTORIC_DATA_1M, HISTORIC_DATA_5M,
> HISTORIC_DATA_15M, etc where 1M, 5M, 15M, etc. are 1 minute, 5 minute and
15 minute
> timebases of the time series values held in the tables. It's for a large
data
> warehouse installation, and these historic data tables are partitioned
tables, for
> which partitions are created dynamically by a separate application. The
size of
> each partition is up to around 1 gigabyte, and they are only created where
> necessary. The table itself is created with the first partition - hence
if no 5
> minute data is ever logged, the table will never be created.
>
> The problem I'm having is writing a generic procedure in PL/SQL to extract
data from
> one of these tables. This procedure will be installed when the database
is first
> set up, so none of the tables will yet have been created.
>
> The other problem is that the procedure has to return a cursor on the
returned
> information for reading by a web based front end (Specifically Business
Objects).
> The procedure I have is basically as follows:
>
> create or replace procedure trend_display_data_point
> (Startit in DATE,
> Finish in DATE,
> Timebase in NUMBER,
> RecordID in NUMBER,
> Outstation in CHAR,
> Point in CHAR,
> hist_cursor in out mcbo_package.hist_cursor_type)
> as
> stream NUMBER;
> time_zone_correction NUMBER;
> BEGIN
> -- Extract the stream ID
> -- Irrelevant here
>
> -- Now get the data
> IF Timebase = 0
> THEN
> OPEN hist_cursor FOR
> SELECT
> h.time + time_zone_correction as time, h.val, h.derivation,
h.qflags
> FROM HISTORIC_DATA_1M h
> WHERE h.stream_id = stream AND h.TIME + time_zone_correction
BETWEEN
> Startit AND Finish;
> ELSIF Timebase = 1
> THEN
> OPEN hist_cursor FOR
> SELECT
> h.time + time_zone_correction as time, h.val, h.derivation,
h.qflags
> FROM HISTORIC_DATA_5M h
> WHERE h.stream_id = stream AND h.TIME + time_zone_correction
BETWEEN
> Startit AND Finish;
> ELSIF Timebase = 2
> THEN
> OPEN hist_cursor FOR
> SELECT
> h.time + time_zone_correction as time, h.val, h.derivation,
h.qflags
> FROM HISTORIC_DATA_15M h
> WHERE h.stream_id = stream AND h.TIME + time_zone_correction
BETWEEN
> Startit AND Finish;
>
> ... etc.
> END IF;
>
> END trend_display_data_point;
>
>
>
>
> This works fine when all the HISTORIC_DATA_XXX tables have been created,
but
> obviously won't compile if they don't. Hence the need for a dynamic SQL
statement
> that will allow me to return a single cursor on a variable table, so the
procedure
> can be compiled at install time.
>
> What I'm trying to achieve is something along the lines of:
>
> execstring := concat('Select ... from ', concat(table_name,' where
...'))
> open hist_cursor for execstring;
>
> However this format simply gives me the following error:
>
> PLS-00455: cursor 'RET_CURSOR' cannot be used in dynamic SQL OPEN
statement.
>
> This, helpfully, doesn't appear in the Oracle8 error messages books ...
The PLSQL
> errors jump from 454 to 483.
>
> I assume that the DBMS_SQL package won't work either, since I can't return
a cursor
> opened within the package.
>
> Thanks for all the help so far ... if anyone can help clear up this last
query, I'd
> be extremely grateful!
>
> Cheers
>
> Graham
>
>
>
Received on Wed Jan 23 2002 - 00:16:43 CST
