Re: Dynamic PL/SQL ?

>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)

    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 Tue Jan 22 2002 - 09:34:54 CST