How to output all rows in a pl/sql dynamic select without dbms_output.put_line

Question

I have three sql blocks below. The first and second blocks work fine. But the third only returns one row. In my real world example, I have 13 refcursors and each query has several columns in it. I want to avoid writing hundreds of dbms_out.put_line(cur.column_name) statements

--#1 correctly returns 8 rows.
VAR rc REFCURSOR
BEGIN
OPEN :rc FOR SELECT object_id,object_name from user_objects where rownum < 9;
END;
print rc
--------------------------------------------------------------

--#2 correctly returns 8 rows
set serveroutput on
BEGIN
for cur in (select object_id,object_name from user_objects where rownum < 9)
loop
    dbms_output.put_line(cur.object_id);
    dbms_output.put_line(cur.object_name);
end loop;
END;
---------------------------------------------------------------

--#3 FAIL, only returns 1 row
set serveroutput on
VAR rc REFCURSOR
BEGIN
for cur in (select object_id,object_name from user_objects where rownum < 9)
loop
OPEN :rc FOR SELECT object_id,object_name from user_objects where object_id = cur.object_id;
end loop;
END;
print rc

Alex Poole · Accepted Answer · 2013-07-25 18:57:33Z

It's not terribly pretty, but you could do something like this:

VAR rc1 REFCURSOR
VAR rc2 REFCURSOR
VAR rc3 REFCURSOR

BEGIN
  for cur in (select object_id,rownum from user_objects where rownum < 4)
  loop
    case cur.rownum
      when 1 then OPEN :rc1 FOR
        SELECT object_id,object_name from user_objects
        where object_id = cur.object_id;
      when 2 then OPEN :rc2 FOR
        SELECT object_id,object_name from user_objects
        where object_id = cur.object_id;
      when 3 then OPEN :rc3 FOR
        SELECT object_id,object_name from user_objects
        where object_id = cur.object_id;
    end case;
  end loop;
END;
/

print rc1
print rc2
print rc3

That works, in as much as you get multiple cursors printed out after the block runs.

If you were on 11g you could perhaps have done something like this article suggests, via its rc_to_dbms_sql procedure. I'm not sure if that would quite have met the brief anyway, but it at least automates the dbms_output generation. You could probably do something similar with dbms_sql instead of a sys_refcursor, parsing your inner select and still using a procedure to display the results automatically. I'm not sure if it would be overkill for your real world case though.

kubanczyk · Accepted Answer · 2013-07-25 17:09:04Z

0

set serveroutput on
VAR rc REFCURSOR
BEGIN
  OPEN :rc FOR SELECT object_id, object_name from user_objects where object_id IN (
     SELECT object_id FROM user_objects WHERE rownum < 9
  );
END;
print rc

I don't know what do you need cur for if a subquery is sufficient. Anyway cursor is bound to one select, and there is no easy way to append to it another select dynamically (this is what UNION ALL does statically

Don't worry about performance, engine (the optimizer) is smart enough to execute the IN ( SELECT ) only once in this case.

answered Jul 25, 2013 at 17:09

kubanczyk

6,1661 gold badge51 silver badges57 bronze badges

2 Comments

user584583 Over a year ago

Kubanczyk, I get that the refcursor is bound to only select, I just want to avoid writing several put_line statements. Note my real world example is not as simple as my stackoverflow example. The basic concept is like sql block #3. A top (main) select is run to get a list of ids. Then 13 select statements are run using the id provided by the top (main) select, looping through until all ids are run through the 13 inner selects

user584583 Over a year ago

I should also say that I understand that looping through row by row is not the most efficient way to get the data, but the ancient application is built to use the data this way and there is no budget to fix that part of the application.

Collectives™ on Stack Overflow

How to output all rows in a pl/sql dynamic select without dbms_output.put_line

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related