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.
CURSOR_1 IS ...
CURSOR_2 IS ...
CURSOR_3 IS ...
PROCEDURE Write(I_Cursor CURSOR) AS
FOR Record IN I_CURSOR LOOP
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(1) := 'Cursor_1';
Cursors(2) := 'Cursor_2';
FOR Cursor_Name IN Cursors.FIRST..Cursors.LAST
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.
Try something like this:
TYPE Ref_Csr IS REF CURSOR;
Cursor_1 VARCHAR2 (4000) := 'SELECT whatever FROM MyTable1';
Cursor_2 VARCHAR2 (4000) := 'SELECT something, else FROM MyTable2, Mytable3';
Cursor_3 VARCHAR2 (4000) := 'SELECT goblygook FROM MyTable4';
PROCEDURE Write ( I_Query VARCHAR2)
OPEN Work_Csr FOR I_Query;
FOR Record IN Work_Csr
UTL_FILE.Put_Line ( 'This is it!');
-- PROCEDURE Run
Write ( Cursor_1);
Write ( Cursor_2);
Your problem has nothing to do with package, main issue is that UTL_FILE.PUT_LINE, unless all cursors return same record type. If theu are, you use package cursors, just either add procedure WRITE to your package or declare cursors in package specification, not in package body to make them public.
The CURSORs have different data returned, but as each datum is of fixed width, they can simply be concatenated into one value per returned record, or so the thought goes. This means the loop for each CURSOR to write to file is exactly the same, to which i would think a common PROCEDURE for all the CURSOR loops would be ideal. The issue is, the loop would need a to use a variable that can be set to any of the CURSORs, which is where the confusion arises.
Hoek, the example shows OPEN FOR dynamic_sql. In my case, i have 8 static queries. So, i would really want to OPEN FOR Cursor_1, if possible.
Please answer me this, i do want to learn: Is there any difference between OPEN FOR dynamic_sql and a hypothetical OPEN FOR Cursor_variable to which is passed CURSOR_1? I am under the impression that having a static query (in a CURSOR or otherwise) lets the optimizer know ahead of time what the query will be, but that is not available with dynamic SQL. Being i know what the queries will be, i wanted to take advantage of that via a defined CURSOR.
If that is not an option, am i trading off any possible performance by using dynamically defined queries rather than separate PRCOEDUREs for each query with static CURSORs? I only want the single PROCEDURE so as not to duplicate code, which is usually a cleaner and better option.
Solomon, all the queries are formatted for a fixed width output file. This means, all the values returned can be concatenated.
SELECT TO_CHAR(value_1) || RPAD(value_2, 14) || etc
That is what the current code does, just not in the query itself. I figure, let's just do everything in the query itself so it is less confusing (and transportable if i can convince them to do it all in SQL*Plus.) So, yes, there would be one expression returning data in each query, no more.
L-, no, i did not. I am off this week and next, so i can't try anything. I am just trying to learn what to do while i am still at home.
I did not realize your code changed it to a REF CURSOR. I am new to REF CURSORs (and i have not been using Oracle regularly in a few years), so i am looking for a good answer and even more for better explanations.
To your example, the question is, as i just asked Hoek, am i trading off any performance for the convenience of a single routine?
Then you could use something like:
g_sql_list sys.OdciVarchar2List := sys.OdciVarchar2List(
'first select statement',
'second select statement',
'third select statement',
OPEN v_cur FOR g_sql_list(p_sql_id);
EXIT WHEN v_cur%notfound;
FOR v_i IN 1..g_sql_list.count LOOP