Oracle REF CURSORS - Only half a job done, again

Having recently whinged about Oracle REF CURSOR’s, I have decided to do it again. So there is no ambiguity, right from the outset I need to state I loathe these things. My fundamental gripe is that you can create a REF CURSOR, easily, but you just cannot easily use it with SQL or PL/SQL, the lingua franca of Oracle.

To labour the point, attempt to answer these questions yourself – you have a REF CURSOR returned from some arbitrary PSM, a) how do you use it?, b) how do you query it?, c) how do you persist the content to another table?

The answer to all three of these questions more often than not is that you have to use a third party language or process to consume the REF CURSOR;  PL/SQL is so deficient you cannot easily manipulate this Oracle object within the Oracle database. Put another way, Oracle have provided half of what you need, or perhaps better put, Oracle have only implemented half a job.

This should be an untenable situation, but it is tolerated, and new REF CURSOR’s are written into new or existing codebases by developers without much thought.

Here is the code snippet I included in my prior post

SQL>
SQL>
SQL>
SQL> create or replace procedure getBands(p_recordSet out sys_refcursor) as
 2 begin
 3   open p_recordSet for
 4     select legend, hasMarriedJerryHall
 5       from bands;
 6 end getBands;
 7 / 

How do you manipulate or query the ‘columns’ in the returned REF CURSOR in Oracle in SQL or PL/SQL? The answer is you cannot, or at least not without a lot of pain and some very delicate code.

The situation is propagated when dealing with multiple REF CURSOR’s, from multiple sources. Ask yourself another question – if you have some internal API, perhaps across multiple database schemata or databases, that return multiple REF CURSOR’s, all with different pieces of information about the same thing, how do you aggregate all of that in the database (where it should be done) rather than causing unnecessary collation overhead in the middleware? This will be a familiar nightmare to any seasoned developer that uses Oracle!

In this post I have decided to address the problem, or perhaps better put that I have had a stab at addressing the problem. There are two basic problems with my approach:

  1. I had to do this as the functionality isn’t provided natively and robustly out-of-the-box in Oracle
  2. Goto point 1.

Let me paint this contrived scenario.

  • There is a PSM that returns a REF CURSOR from some database about rock legends, members of Pink Floyd, Queen, etc. Remember it is a REF CURSOR, not a database table, so there is no easy way in PL/SQL or SQL of knowing the column names, their data types, and so on.
  • There is a PSM that returns a REF CURSOR about band members and some ID that links Freddie Mercury with Queen, David Gilmour with Pink Floyd, …
  • I need to return a single aggregated result set to an invoking process. How do I do this – how are these two (or a real system perhaps 3, or 4, or more) REF CURSOR’s merged within the database into a single result set and returned to the calling process in one round trip?

Below, I describe my work-around, at least for a few basic Oracle data types, with no error handling, and many constraints and limitations. It is demo code that addresses the question – is it possible to manipulate REF CURSOR’s within the database. I won’t hold you in suspense – the answer is yes, it can be done, but as Oracle have only done half a job here, it should also be obvious that I have had to jump through a few hoops here to get where I needed to go.

First of all, let me show you demo output of my experimentation, executed from within SQL/Plus.


SQL>
SQL>
SQL>
SQL>
SQL> VAR rc REFCURSOR
SQL>
SQL> DECLARE
2     TYPE tsrc IS REF CURSOR;
3     rc1 tsrc;
4     rc2 tsrc;
5     BEGIN
6      utilRefCursor.tearDown;
7
8      -- Step 1, populate the legends. In this query, rc1 is a REF CURSOR created in this block, but it could equally
9      -- be a REF CURSOR returned from a stored procedure or other PSM
10     OPEN rc1 FOR
11       SELECT id, christianName, surname, dob
12         FROM legend;
13     utilRefCursor.populate(rc1);
14
15     -- Step 2, merge in some more content, and from a second REF CURSOR. As above, the REF CURSOR could
16     -- come from some other inexcessible process, not necessarily created on the fly by querying a
17     -- small handful of tables.
18     OPEN rc2 FOR
19       SELECT l.id, b.bandName
20         FROM legend l,
21              band b,
22              bandLegend bl
23          WHERE bl.legendID = l.id
24            AND bl.bandID = b.id;
25     utilRefCursor.populate(rc2);
26
27     -- Step 3, populate the SQL/Plus cursor variable, that could equally be returned to
28     -- some invoking process
29     :rc := utilRefCursor.resultSet;
30 END;
31 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> print rc

ID       CHRISTIANNAME SURNAME    DOB         BANDNAME
-----    ------------- ---------- ---------   ----------
    5    Freddie       Mercury    05-SEP-46   Queen
    6    Brian         May        19-JUL-47   Queen
    7    Roger         Taylor     26-JUL-49   Queen
    8    John          Deacon     19-AUG-51   Queen
   20    Roger         Waters     06-SEP-43   Pink Floyd
   21    David         Gilmour    06-MAR-46   Pink Floyd
   22    Nick          Mason      27-JAN-44   Pink Floyd
   23    Richard       Wright     28-JUL-43   Pink Floyd

8 rows selected.

SQL> SELECT *
2 FROM tempTable;

ID       CHRISTIANNAME SURNAME    DOB         BANDNAME
-----    ------------- ---------- ---------   ----------
    5    Freddie       Mercury    05-SEP-46   Queen
    6    Brian         May        19-JUL-47   Queen
    7    Roger         Taylor     26-JUL-49   Queen
    8    John          Deacon     19-AUG-51   Queen
   20    Roger         Waters     06-SEP-43   Pink Floyd
   21    David         Gilmour    06-MAR-46   Pink Floyd
   22    Nick          Mason      27-JAN-44   Pink Floyd
   23    Richard       Wright     28-JUL-43   Pink Floyd

8 rows selected.

SQL>

At this point, you should be impressed! If you are not impressed by what you see, go back to the top of the blog and start reading again as you likely don’t understand what has been demonstrated by this code. To help you along however, what has been demonstrated is:

  • the manipulation of relational REF CURSOR content within the Oracle database
  • the ability to merge in multiple (in this case two) different data sources as two different REF CURSOR’s (note the ‘column’ BANDNAME was added) in the code block above producing a third REF CURSOR
  • the conversion of a REF CURSOR to a database table, enabling manipulation using familiar SQL or PL/SQL in a useful way.

So, how was it done? The demo prototype code is shown below:

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE utilRefCursor AS
2
3     /*
4      * Upsert in the current ref cursor
5      */
6     PROCEDURE populate(src IN sys_refcursor);
7
8     /*
9      * Return the merged result set
10     */
11    FUNCTION resultSet RETURN sys_refcursor;
12
13    /*
14     * Teardown
15     */
16    PROCEDURE tearDown;
17
18 END utilRefCursor;
19 /

Package created.

SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY utilRefCursor AS
2
3      tempTableName CONSTANT VARCHAR2(10) := 'TEMPTABLE';
4      idColumnName CONSTANT CHAR(2) := 'ID';
5
6      idColumnNumber NUMBER := NULL;
7
8      columnCount NUMBER;
9      describeTable DBMS_SQL.desc_tab;
10
11     cursorNumber NUMBER;
12
13     /*
14      * From the provided REF CURSOR, get the ID column number, returning NULL if it does not exist.
15      */
16     PROCEDURE setState(src IN sys_refcursor) IS
17       r1 sys_refcursor := src;
18     BEGIN
19       r1 := src;
20       cursorNumber := DBMS_SQL.TO_CURSOR_NUMBER(r1);
21       DBMS_SQL.DESCRIBE_COLUMNS(c => cursorNumber, col_cnt => columnCount, desc_t => describeTable);
22
23       idColumnNumber := NULL;
24       FOR colNo IN 1..columnCount
25        LOOP
26         idColumnNumber := CASE
27                             WHEN describeTable(colNo).col_name = 'ID' THEN colNo
28                             ELSE idColumnNumber
29                           END;
30        END LOOP;
31      END;
32
33     /*
34      * Add columns from ref cursor by name to temporary table
35      */
36     PROCEDURE addColsToTempTable(src IN sys_refcursor) IS
37     BEGIN
38
39       IF idColumnNumber IS NOT NULL THEN
40
41         FOR colNo IN 1..columnCount
42           LOOP
43             FOR col IN (
44                           SELECT describeTable(colNo).col_name columnName,
45                                  describeTable(colNo).col_type columnType,
46                                  describeTable(colNo).col_max_len columnMaxLength,
47                                  NVL2(utc.column_name, 'Y', 'N') columnPresentInTT
48                             FROM user_tab_cols utc
49                                RIGHT OUTER JOIN DUAL
50                                  ON (utc.table_name = tempTableName AND utc.column_name = describeTable(colNo).col_name)
51                                   WHERE describeTable(colNo).col_type IN (1, 2, 12, 96) -- not bothering with CLOB, XMLTYPE, TIMESTAMP, NUMERic precision and scale etc.
52                         ) LOOP
53                             IF col.columnPresentInTT = 'N' THEN
54                                EXECUTE IMMEDIATE 'ALTER TABLE ' || tempTableName
55                                                                 || ' ADD ' || col.columnName
56                                                                 || ' '
57                                                                 || CASE
58                                                                        WHEN col.columnType = 1 THEN 'VARCHAR2(' || col.columnMaxLength || ')'
59                                                                        WHEN col.columnType = 2 THEN 'NUMBER'
60                                                                        WHEN col.columnType = 12 THEN 'DATE'
61                                                                        WHEN col.columnType = 96 THEN 'CHAR(' || col.columnMaxLength || ')'
62                                                                    END;
63
64                             END IF;
65                          END LOOP;
66         END LOOP;
67     END IF;
68 END addColsToTempTable;
69
70
71
72    /*
73     * Setup the system to perform the utility functions
74     */
75    PROCEDURE setup(src IN sys_refcursor) IS
76    BEGIN
77
78      -- 1. Create the table to hold the REF CURSOR content, if it doesn't already exist
79      FOR r IN (
80                 SELECT 1
81                   FROM user_tables ut
82                      RIGHT OUTER JOIN DUAL
83                        ON (ut.table_name = tempTableName)
84                          WHERE ut.table_name IS NULL
85               ) LOOP
86                   EXECUTE IMMEDIATE 'CREATE TABLE  || tempTableName || '(id NUMBER PRIMARY KEY)';
87                 END LOOP;
88
89      -- 2. Get package internal state for subsequent operations
90     setState(src => src);
91
92     -- 3. Add the columns from the REF CURSOR to the TT iif they do not already exist
93     addColsToTempTable(src => src);
94   END setup;
95
96
97
98
99
100    /*
101     * Upsert the ref cursor content
102     */
103    PROCEDURE populate(src IN sys_refcursor) IS
104
105    dataTypeVarchar2 VARCHAR(32767);
106    dataTypeNumber NUMBER;
107    dataTypeDate DATE;
108
109    idColumnValue NUMBER; -- Assuming that the ID column value data type is a NUMBER that will do for demo code, a PoC
110
111    BEGIN
112      setup(src => src);
113
114      FOR colNo IN 1..columnCount
115        LOOP
116          IF describeTable(colNo).col_type = 1 THEN DBMS_SQL.DEFINE_COLUMN(cursorNumber, colNo, dataTypeVarchar2, 32767);
117            ELSIF describeTable(colNo).col_type = 2 THEN DBMS_SQL.DEFINE_COLUMN(cursorNumber, colNo, dataTypeNumber);
118              ELSIF describeTable(colNo).col_type = 12 THEN DBMS_SQL.DEFINE_COLUMN(cursorNumber, colNo, dataTypeDate);
119                ELSIF describeTable(colNo).col_type = 96 THEN DBMS_SQL.DEFINE_COLUMN(cursorNumber, colNo, dataTypeVarchar2, 32767);
120          END IF;
121       END LOOP;
122
123     -- And now step through the REF CURSOR row at a time, retrieve the values, and populate the GTT
124     WHILE DBMS_SQL.FETCH_ROWS(cursorNumber) > 0
125       LOOP
126          DBMS_SQL.COLUMN_VALUE(cursorNumber, idColumnNumber, idColumnValue);
127
128          FOR colNo IN 1..columnCount
129            LOOP
130              IF describeTable(colNo).col_name != idColumnName THEN
131
132                IF describeTable(colNo).col_type = 1 THEN DBMS_SQL.COLUMN_VALUE(cursorNumber, colNo, dataTypeVarchar2);
133                  ELSIF describeTable(colNo).col_type = 2 THEN DBMS_SQL.COLUMN_VALUE(cursorNumber, colNo, dataTypeNumber);
134                     ELSIF describeTable(colNo).col_type = 12 THEN DBMS_SQL.COLUMN_VALUE(cursorNumber, colNo, dataTypeDate);
135                        ELSIF describeTable(colNo).col_type = 96 THEN DBMS_SQL.COLUMN_VALUE(cursorNumber, colNo, dataTypeVarchar2);
136                END IF;
137
138                EXECUTE IMMEDIATE 'MERGE INTO ' || tempTableName || ' tt'
139                                                || ' USING(SELECT :colID1 id FROM DUAL) r'
140                                                || ' ON (r.id = tt.id)'
141                                                || ' WHEN MATCHED THEN UPDATE SET ' || describeTable(colNo).col_name || '= :collVal1'
142                                                || ' WHEN NOT MATCHED THEN INSERT(id, ' || describeTable(colNo).col_name || ') VALUES(:colID2, :colVal2)'
143                     USING idColumnValue,
144                           CASE
145                              WHEN describeTable(colNo).col_type = 2 THEN TO_CHAR(dataTypeNumber)
146                              WHEN describeTable(colNo).col_type = 12 THEN TO_CHAR(dataTypeDate)
147                              ELSE dataTypeVarchar2
148                           END,
149                           idColumnValue,
150                           CASE
151                              WHEN describeTable(colNo).col_type = 2 THEN TO_CHAR(dataTypeNumber)
152                              WHEN describeTable(colNo).col_type = 12 THEN TO_CHAR(dataTypeDate)
153                              ELSE dataTypeVarchar2
154                           END;
155               END IF;
156            END LOOP;
157      END LOOP;
158
159    END populate;
160
161
162    /*
163     * Return the merged result set
164     */
165    FUNCTION resultSet RETURN sys_refcursor IS
166    rc sys_refcursor;
167     BEGIN
168       OPEN rc FOR 'SELECT * FROM ' || tempTableName;
169       RETURN rc;
170    END resultSet;
171
172
173    /*
174     * Teardown
175     */
176    PROCEDURE tearDown IS
177    BEGIN
178       FOR r IN (
179                   SELECT 1
180                      FROM user_tables ut
181                         WHERE ut.table_name = tempTableName
182                ) LOOP
183                     EXECUTE IMMEDIATE 'DROP TABLE ' || tempTableName;
184                  END LOOP;
185       idColumnNumber := NULL;
186    END tearDown;
187
188 END utilRefCursor;
189 /

Package body created.

SQL>
SQL>

To conclude, or to repeat, this is demo code, with so many limitations, and it is only necessary to write this sort of crap as Oracle have only done half a job with REF CURSORS.

I have placed a copy of the prototype code on GitHub, along with code to generate the sample data used here.

The code above was developed using the following database version etc.

SQL>
SQL>
SQL>
SQL> SELECT *
2 FROM v$version;

  BANNER
  ----------------------------------------------------------------------------
  Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
  PL/SQL Release 11.2.0.2.0 - Production
  CORE 11.2.0.2.0 Production
  TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
  NLSRTL Version 11.2.0.2.0 - Production
SQL>

— Published by Mike, 14:56 06 February 2017

 

Leave a Reply