I am writing a PACKAGE to print the results of 8 queries to separate files. Each file gets a static header and a dynamic footer. The footer include rowcount and some totals. The queries are not dynamic SQL and take no parameters.
Here is what i am trying. I by no means mean to say this is best, it is just what i am thinking of.
PACKAGE BODY
CURSOR_1 IS ...
CURSOR_2 IS ...
CURSOR_3 IS ...
...
PROCEDURE Write(I_Cursor CURSOR) AS
BEGIN
OPEN I_CURSOR;
FOR Record IN I_CURSOR LOOP
UTL_FILE.PUT_LINE
END LOOP;
CLOSE I_CURSOR;
END Write;
PROCEDURE Run
AS
BEGIN
Write(Cursor1)
Write(Cursor2)
...
END
CURSORs cannot be passed, so Write() will not work. The answer i have seen is to use a REF CURSOR, however, a REF CURSOR cannot be OPENed. Unless i am misundertanding the topic.
Ultimately, what i really want to do is use an array to hold the CURSOR names, if possible.
Cursors DBMS_SQL.VARCHAR2_TABLE;
Cursors(1) := 'Cursor_1';
Cursors(2) := 'Cursor_2';
FOR Cursor_Name IN Cursors.FIRST..Cursors.LAST
LOOP
Write(Cursor_Name)
END LOOP;
1) Is this a good approach?
2) Is there a way to pass a CURSOR to a PROCEDURE and have that PROCEDURE open it?
I could just repeat the code for each CURSOR. I was hoping to avoid the duplication, and to make it very easy and clear for anyone to comment out a specific query from running for whatever reason.