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.
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.
Manipulating PL/SQL collections is a lot easier since 10g, which gave us some SET operators we can use with them.
As you know, to employ the TABLE() function means we have to use a SQL type...
SQL> create or replace type nums_nt as table of number
2 /
Type created.
SQL>
The following block populates a collection with some numbers, which it uses in a FOR loop. Then it executes a another query to populate a second collection. The second collection is added to the first collection using the MULTISET UNION syntax. Unlike the the SQL UNION operator, this implementation does not winnow duplicates (we can use MULTISET UNION DISTINCT for that). The code finishes off by looping through the first collection again, to prove that it contains both sets of numbers.
SQL> set serveroutput on
SQL>
SQL> declare
2 master_nos nums_nt;
3 fresh_nos nums_nt;
4 begin
5
6 dbms_output.put_line ('get some numbers, print some names');
7
8 select id
9 bulk collect into master_nos
10 from t23
11 where name not in ( select upper(name) from t_doctors )
12 and name not in ( select upper(name) from t_kids );
13
14 for r in ( select t23.name
15 from t23
16 join ( select * from table(master_nos)) sq
17 on t23.id = sq.column_value
18 )
19 loop
20 dbms_output.put_line (r.name);
21 end loop;
22
23 dbms_output.put_line ('get more numbers, print all names');
24
25 select id
26 bulk collect into fresh_nos
27 from t23
28 where name in ( select upper(name) from t_doctors );
29
30 master_nos := master_nos
31 MULTISET UNION
32 fresh_nos;
33
34 for r in ( select t23.name
35 from t23
36 join ( select * from table(master_nos)) sq
37 on t23.id = sq.column_value
38 )
39 loop
40 dbms_output.put_line (r.name);
41 end loop;
42
43 end;
44 /
get some numbers, print some names
CAT
PINNER BLINN
LORAX
MR KNOX
FOX IN SOCKS
get more numbers, print all names
CAT
PINNER BLINN
LORAX
MR KNOX
FOX IN SOCKS
DR SINATRA
DR FONZ
PL/SQL procedure successfully completed.
SQL>