Re: Help, SQL Problem!!!

From: Gary SMITH <Gary.Smith_at_cern.ch>
Date: Tue, 11 Aug 1998 12:15:17 +0200
Message-ID: <35D019B5.3531C26A@cern.ch>

In PL/SQL (I've done a lot of this!) you can use the DBMS_SQL package to dynamically build your SQL then use the UTL_FILE package to write this to a text file on the server (you must have the directory specified in init.ora for security reasons). If this is a job that takes a long time, then look at DBMS_JOB to execute it.

Here's some code (it may not be 100% correct):

CREATE OR REPLACE PROCEDURE salesToText AS

    fileId             UTIL_FILE.FILE_TYPE;
    cursorId        INTEGER;
    maxSalesFile INTEGER := 52; /* for example */
    salesDate      DATE := to_date('01/01/1998','DD/MM/YYYY');
    sqlRtn           INTEGER;
    sDate            DATE;
    sVal              FLOAT;

BEGIN
    file_id := UTIL_FILE.FOPEN('dir','filename','W');     FOR fileNum IN 1..maxSalesFile
    LOOP
        cursorId := DBMS_SQL.OPEN_CURSOR;

        DBMS_SQL.PARSE(cursorId,'Select salesDate, val From sales_'||
                        to_char(fileNum,'00000')||' where salesDate >

:bind_1 ',DBMS_SQL.V7);

        /* You can bind variables in the WHERE clause. Here's an example for

bind_1 */

        DBMS_SQL.BIND_VARIABLE(cursorId,'bind_1', salesDate);

        /* You must define the columns that are returned */
        DBMS_SQL.DEFINE_COLUMN(cursorId. 1, sDate);
        DBMS_SQL.DEFINE_COLUMN(cursorId, 2, sVal);

        sqlRtn := DBMS_SQL.EXECUTE(cursorId);
        LOOP
            EXIT WHEN DBMS_SQL.FETCH_ROWS(cursorId) = 0

            /* Get the values */
            DBMS_SQL.COLUMN_VALUE(cursorId, 1, sDate);
            DBMS_SQL.COLUMN_VALUE(cursorId, 2, sVal);

            /* Dump to file */
            UTL_FILE.PUT_LINE(fileId, to_char(sDate,'DD/MM/YYYY')||'
'||to_char(sVal));
        END LOOP;

        DBMS_SQL.CLOSE_CURSOR(cursorId);

    END LOOP;     UTIL_FILE.FCLOSE(file_id);
END; You may not need to open a new cursor each time (i'm not sure about this) you
can probably just open it once and re-use it (this would be more efficient).

Hope this helps.

Gary Smith.

Valerie St Esteve wrote:

> Hi
>
> I hope this doesn't seem like a silly question but I'm new to all this.
> Is there any way to dynamically alter the FROM part of a select
> statement either in SQL or PL/SQL.
>
> My requirement is this: I have to gather data from 52 tables all of
> which have an identical structure. They are only differentiated by their
> names. The first is called sales_00001, the next sales_00002 and so on.
> All the data has to be spooled into a single text file in one go.
> Apart from coding a query that has 52 selects all unioned together with
> hardcoded table names for each of the tables, is there any way of having
> a single select statement, say within some kind of loop that has its
> from part dynamically altered for each iteration of the loop. If so, how
> do I achieve this? I can't find out how it is done anywhere.
>
> Thanks in advance
>
> Val

--

---

Gary Smith
CERN, Geneva, Switzerland
Email: Gary.Smith_at_cern.ch
Tel: +41 22 7678944

---

Received on Tue Aug 11 1998 - 05:15:17 CDT