Return 2 resultset from cursor based on one query (nested cursor)

Question

I'm trying to obtain 2 different resultset from stored procedure, based on a single query. What I'm trying to do is that:

1.) return query result into OUT cursor;

2.) from this cursor results, get all longest values in each column and return that as second OUT resultset.

I'm trying to avoid doing same thing twice with this - get data and after that get longest column values of that same data. I'm not sure If this is even possible, but If It is, can somebody show me HOW ?

This is an example of what I want to do (just for illustration):

CREATE OR REPLACE PROCEDURE MySchema.Test(RESULT OUT SYS_REFCURSOR,MAX_RESULT OUT SYS_REFCURSOR)
AS

BEGIN

 OPEN RESULT FOR SELECT Name,Surname FROM MyTable; 

 OPEN MAX_RESULT FOR SELECT Max(length(Name)),Max(length(Surname)) FROM RESULT; --error here 

END Test;

This example compiles with "ORA-00942: table or view does not exist".

I know It's a silly example, but I've been investigating and testing all sorts of things (implicit cursors, fetching cursors, nested cursors, etc.) and found nothing that would help me, specially when working with stored procedure returning multiple resultsets.

My overall goal with this is to shorten data export time for Excel. Currently I have to run same query twice - once for calculating data size to autofit Excel columns, and then for writing data into Excel.

I believe that manipulating first resultset in order to get second one would be much faster - with less DB cycles made.

I'm using Oracle 11g, Any help much appreciated.

Answer 1

Each row of data from a cursor can be read exactly once; once the next row (or set of rows) is read from the cursor then the previous row (or set of rows) cannot be returned to and the cursor cannot be re-used. So what you are asking is impossible as if you read the cursor to find the maximum values (ignoring that you can't use a cursor as a source in a SELECT statement but, instead, you could read it using a PL/SQL loop) then the cursor's rows would have been "used up" and the cursor closed so it could not be read from when it is returned from the procedure.

You would need to use two separate queries:

CREATE PROCEDURE MySchema.Test(
  RESULT     OUT SYS_REFCURSOR,
  MAX_RESULT OUT SYS_REFCURSOR
)
AS
BEGIN
 OPEN RESULT FOR
   SELECT Name,
          Surname
   FROM   MyTable; 

 OPEN MAX_RESULT FOR
   SELECT MAX(LENGTH(Name))    AS max_name_length,
          MAX(LENGTH(Surname)) AS max_surname_length
   FROM   MyTable;
END Test;
/

Just for theoretical purposes, it is possible to only read from the table once if you bulk collect the data into a collection then select from a table-collection expression (however, it is going to be more complicated to code/maintain and is going to require that the rows from the table are stored in memory [which your DBA might not appreciate if the table is large] and may not be more performant than compared to just querying the table twice as you'll end up with three SELECT statements instead of two).

Something like:

CREATE TYPE test_obj IS OBJECT(
  name    VARCHAR2(50),
  surname VARCHAR2(50)
);

CREATE TYPE test_obj_table IS TABLE OF test_obj;

CREATE PROCEDURE MySchema.Test(
  RESULT     OUT SYS_REFCURSOR,
  MAX_RESULT OUT SYS_REFCURSOR
)
AS
 t_names test_obj_table;
BEGIN
 SELECT Name,
        Surname
 BULK COLLECT INTO t_names
 FROM   MyTable; 

 OPEN RESULT FOR
   SELECT * FROM TABLE( t_names );

 OPEN MAX_RESULT FOR
   SELECT MAX(LENGTH(Name))    AS max_name_length,
          MAX(LENGTH(Surname)) AS max_surname_length
   FROM   TABLE( t_names );
END Test;
/