how to append values to a oracle type

Question

how can I append (insert) 3 or 4 different values to an oracle type and then later open it up for a cursor.

For example (pseudo):

insert into mytype select 1 from dual;
insert into mytype select 3 from dual;
insert into mytype select 5 from dual;

open cursor_1 for select * from table(mytype);

Is this possible to do in pl/sql?

I know this is trivial and can be combined into one query but my real need is to have different queries and keep appending the results to mytype.

Answer 1

Assuming you mean you have a custom SQL type (presumably a nested table type), and a PL/SQL variable of that type: I don't believe you can INSERT into it, and I don't think you can SELECT into it in a way that would append to the collection.

You can select into a scalar variable, then append it to the collection procedurally.

SQL> create type mytype as table of integer;
  2  /

Type created.

SQL> set serveroutput on
SQL> l
  1  declare
  2    mytable  mytype := mytype();
  3    cursor_1 sys_refcursor;
  4    x  integer;
  5    procedure append_to_table( t IN OUT mytype, y IN INTEGER)
  6      is
  7      begin
  8        t.extend();
  9        t(t.COUNT) := y;
 10      end append_to_table;
 11  begin
 12    select 1 into x from dual;
 13    append_to_table( mytable, x );
 14    select 3 into x from dual;
 15    append_to_table( mytable, x );
 16    select 5 into x from dual;
 17    append_to_table( mytable, x );
 18    open cursor_1 for select * from table(cast(mytable as mytype));
 19    fetch cursor_1 into x;
 20    dbms_output.put_line(x);
 21    fetch cursor_1 into x;
 22    dbms_output.put_line(x);
 23    fetch cursor_1 into x;
 24    dbms_output.put_line(x);
 25    close cursor_1;
 26* end;
SQL> /
1
3
5

PL/SQL procedure successfully completed.