SELECT * FROM TABLE(Oracle REF CURSOR)

Below is the most trivial of C# applications that generates an enumerable anonymous class collection, and spits out the content to the console.

using System;
using System.Collections.Generic;

namespace ConsoleApplication1565
{
    class Program
    {
        public static void WriteToConsole(IEnumerable<dynamic> bands)
        {
            foreach (var band in bands)
                Console.WriteLine("Rock legend {0} has {1} previously married Jerry Hall", band.Legend, band.HasMarriedJerryHall ? "" : "not");
        }

        static void Main(string[] args)
        {
            var bands = new[] { //Some great members of great bands. Sorry they're all British; I couldn't find American examples, and I also don't think there are any
                                        new { Legend = "Richard Wright", HasMarriedJerryHall = false},      //Pink Floyd
                                        new { Legend = "Roger Waters", HasMarriedJerryHall = false},
                                        new { Legend = "David Gilmour", HasMarriedJerryHall = false},
                                        new { Legend = "Nick Mason",HasMarriedJerryHall = false},
                                        new { Legend = "Freddie Mercury", HasMarriedJerryHall = false},     //Queen
                                        new { Legend = "Roger Taylor",  HasMarriedJerryHall = false},
                                        new { Legend = "John Deacon",  HasMarriedJerryHall = false},
                                        new { Legend = "Brian May", HasMarriedJerryHall = false},
                                        new { Legend = "John Lennon",  HasMarriedJerryHall = false},        //The Beatles
                                        new { Legend = "Paul McCartney",  HasMarriedJerryHall = false},
                                        new { Legend = "Ringo Star",  HasMarriedJerryHall = false},
                                        new { Legend = "George Harrison", HasMarriedJerryHall = false},
                                        new { Legend = "Mick Jagger",  HasMarriedJerryHall = true},         //Rolling Stones
                                        new { Legend = "Keith Richards",  HasMarriedJerryHall = false},
                                        new { Legend = "Charlie Watts", HasMarriedJerryHall = false},
                                        new { Legend = "Ronnie Wood", HasMarriedJerryHall = false}
                                };
            WriteToConsole(bands);

        }
    }
}

This program demonstrates two things exceptionally clearly. They are:

• C# is a statically typed language, but the object is not (see dynamic), and that the following code would compile without error even though IsAMemberOfPinkFloyd has not been aliased during object instantiation.

        public static void WriteToConsole(IEnumerable<dynamic> bands)
        {
            foreach (var band in bands)
                Console.WriteLine("Rock legend {0} is a member of Pink Floyd", band.Legend, band.IsAMemberOfPinkFloyd);
        }

• The collection passed into method WriteToConsole is not a specific predefined class with specific public properties Legend and HasMarriedJerryHall (or IsAMemberOfPinkFloyd either).

Now, lets turn our heads to SQL, and Oracle in particular.

Here is a snippet of equally trivial code:

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  /

Procedure created.

SQL>
SQL> var rc refcursor
SQL> exec getBands(:rc);

PL/SQL procedure successfully completed.

SQL> print rc

LEGEND               HASMARRIEDJERRYHALL
-------------------- --------------------
Richard Wright       N
Roger Waters         N
David Gilmour        N
Nick Mason           N
Freddie Mercury      N
Roger Taylor         N
John Deacon          N
Brian May            N
John Lennon          N
Paul McCartney       N
Ringo Star           N
George Harrison      N
Mick Jagger          Y
Keith Richards       N
Charlie Watts        N
Ronnie Wood          N

16 rows selected.

SQL>
SQL>

Here’s the question – I have some PSM (in this case a silly PROCEDURE named GETBANDS) returning a database CURSOR that in my ‘day job’ would likely represent data from a bank or pharmaceutical company, and I want to use it, to SELECT from it some way. How can this be done?

How about?

SQL>
SQL>
SQL> select t.legend
2 from table(:rc) t;
from table(:rc) t
*
ERROR at line 2:
ORA-22905: cannot access rows from a non-nested table item
SQL>

Obviously not! Over the years I have experimented with a lot else too, even resorting to thoroughly RTFM from Oracle. In the end and in summary,

SELECT *
FROM TABLE(:rc)

or variants thereof are not an option and you have a CURSOR representing some data in the form of a REF CURSOR that you cannot easily do anything with. Instead you have to code work-arounds, perhaps extproc to a Java stored procedure or something in .Net, or convert the CURSOR to XML and xpath out the information you are wanting. All options are unglamorous.

When you compare this feature and design limitation with something as elegant as the dynamic type similarly implemented C# above, it just makes me sad. It is almost like Oracle have implemented only half a job here; you have this REF CURSOR generated in PL/SQL, and you cannot use it in a meaningful way in SQL or PL/SQL. To put it another way, by using REF CURSOR’s you have made a rod for your own back. Sorry ’bout that guys.

Good luck all.

— Published by Mike, 14:13 18 December 2016