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:
- I had to do this as the functionality isn’t provided natively and robustly out-of-the-box in Oracle
- 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
Archive
By Month: November 2022, October 2022, August 2022, February 2021, January 2021, December 2020, November 2020, March 2019, September 2018, June 2018, May 2018, April 2018
Categories
Apple, C#, Databases, Faircom, General IT Rant, German, Informatics, LINQ, MongoDB, Oracle, Perl, PostgreSQL, SQL, SQL Server, Unit Testing, XML/XSLT
Leave a Reply