Wednesday, January 04, 2006

12 Selects of christmas (the third instalment)

A couple of things (other than tables and views) that you can select from :

7. Selecting from a collection

You can (and should) create your own type for this :

create type tab_varchar2_64 as table of varchar2(64);

If you can't then you may be able to get by with using SYS.DBMS_DEBUG_VC2COLL

This will turn a list of values into rows.

SELECT column_value
from table(tab_varchar2_64('1','Fred','Amber'));

8. Selecting from a (Pipelined) function

Again, you should use your own collection for the return type.

create or replace function f_generate_dummy_name
return sys.DBMS_DEBUG_VC2COLL pipelined is
type tab_char is varray(26) of varchar2(1);
t_vowel tab_char := tab_char('A','E','I','O','U');
t_other tab_char := tab_char('B','C','D','F','G','H','J','K','L','M','N',
for one in 1..t_other.count loop
for two in 1..t_vowel.count loop
for three in 1..t_other.count loop
pipe row (t_other(three)||t_vowel(two)||t_other(one));
end loop;
end loop;
end loop;
select * from table(f_generate_dummy_name);

No comments: