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.

Is there a reason that you don't want to just open a cursor for a query that joins `v$tablespace` to `dba_tablespaces`? Are you actually doing additional processing in the loop that isn't shown here which doesn't work in a single SQL statement? — Justin Cave, Nov 12 '19 at 16:56
There are additional variables that get filled with data from some other tables. The result will have more data than just the number and the tablespacename. The Cursor shall select about 10 variables. I just want to get the core to work or know if it is even possible this way. — unixora, Nov 12 '19 at 17:08
But can you just add those tables as additional joins? If you start with a core that just joins the tables, is that OK? Or do you really need a core that involves loops that are doing your own joins? You can create an object and then a collection, populate those in your anonymous PL/SQL block, and then open a cursor that selects from that collection. But that's going to involve creating new types in the database (which may be a privileges issue) and will probably make the code much longer and more complicated than a join. — Justin Cave, Nov 12 '19 at 17:17
Yes, there are privilege issues. I already tried this. I will look if i can join all tables and post an update. Thanks — unixora, Nov 12 '19 at 17:29

score 0 · Answer 1 · answered Nov 12 '19 at 17:28

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;

The second one goes in the direction, and i think it should work but as expected i get an ORA-01031: insufficient privileges. A Join may be possible but would be very complicated because I collect some data from "dba_hist_tbspc_space_usage" and "dba_tablespaces" for every tablespace and store the values in variables. Furthermore I do some arithmetic operations with the variables. Every variable already get filled by a join on multiple positions. — unixora, Nov 13 '19 at 10:16
@unixora - That all sounds quite reasonable to do in a single SQL statement-- you're just joining a bunch of tables together on the tablespace name. Which statement throws the error? I would guess that you'd have to ask your DBA to give you permission to create types. — Justin Cave, Nov 13 '19 at 14:51

PL/SQL - Return cursor that selects data from loop

1 Answers1