PL/SQL - Return cursor that selects data from loop

Question

I have the following problem with PL/SQL:

I'm trying to write a PL/SQL Statement for an Oracle Cloud Control Metric Extension. The Metric wants a CURSOR as result:

The PL/SQL has to end with something like the following

OPEN :1 FOR SELECT var1, var2 FROM DUAL;

The bind variable 1 and the Type "SQL_CURSOR" is defined by the application.

My Code looks like this:

DECLARE
  v_ts_id NUMBER;
  tbspname VARCHAR2(200);

  CURSOR all_tablespaces IS 
    select distinct tablespace_name 
    from dba_tablespaces
    where tablespace_name not in ('UNDO','TEMP');

 BEGIN

   FOR v_ts_name IN all_tablespaces LOOP

     tbspname := UPPER(v_ts_name.tablespace_name);

     SELECT ts# into v_ts_id 
     FROM v$tablespace 
     where name = tbspname;

   END LOOP;

   OPEN :1 FOR SELECT v_ts_id, tbspname FROM DUAL;  
 END;

As result I get one row. Makes sense, because the cursor just selects the last values stored in v_ts_id and tbspname.

But how can I get the cursor to select all rows? I don't want to create a table to store the values.

The following works on SQL*Plus

set serverout on
 set verify off
 set lines 200
 set pages 2000

DECLARE
  v_ts_id number;
  tbspname varchar2(200);

  CURSOR all_tablespaces IS 
    select distinct tablespace_name 
    from dba_tablespaces 
    where tablespace_name not in ('UNDO','TEMP');

 BEGIN

 FOR v_ts_name IN all_tablespaces LOOP

   tbspname := UPPER(v_ts_name.tablespace_name);

   SELECT ts# into v_ts_id 
   FROM v$tablespace
   where name = tbspname;

   DBMS_OUTPUT.PUT_LINE(v_ts_id ||','||tbspname);

  END LOOP;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Tablespace usage information not found in AWR');
END;
/

Part of the RESULT looks like this:

5,USERS
1,SYSAUX
0,SYSTEM

I want the same result like in the second example returned by the Cursor in my code?

I think the loop is irreplaceable because some more data will be processed later for every tablespace.

Answer 1

The simple approach would be to simply do a join

begin
  open :1 
   for select vt.ts#, dt.tablespace_name
         from v$tablespace vt
              join dba_tablespaces dt
                on vt.tablespace_name = dt.tablespace_name
        where dt.tablespace_name not in ('UNDO','TEMP');
end;

If you really must do a loop, something like this should work

create type my_ts_obj as object (
  ts# integer,
  tablespace_name varchar2(30)
);

create type my_ts_nt is table of my_ts_obj;

declare
  l_ts my_ts_nt := new my_ts_nt();
  l_ts# integer;
begin
  for dt in (select distinct tablespace_name 
               from dba_tablespaces 
              where tablespace_name not in ('UNDO','TEMP'))
  loop
    select vt.ts#
      into l_ts#
      from v$tablespace vt
     where vt.tablespace_name = dt.tablespace_name;

    l_ts.extend();
    l_ts( l_ts.count ) := new my_ts_obj( l_ts#, dt.tablespace_name );
  end loop;

  open :1
   for select *
         from table( l_ts );
end;