CREATE OR REPLACE TYPE names_nt IS TABLE OF VARCHAR2 ( 1000 ); Type created.
CREATE OR REPLACE FUNCTION lotsa_names (
base_name_in IN VARCHAR2
, count_in IN INTEGER
)
RETURN names_nt
IS
retval names_nt := names_nt ( );
BEGIN
retval.EXTEND ( count_in );
FOR indx IN 1 .. count_in
LOOP
retval ( indx ) := base_name_in || ' ' || indx;
END LOOP;
RETURN retval;
END lotsa_names; Function created.
Traditional Table Function Invocation
SELECT COLUMN_VALUE
FROM TABLE ( lotsa_names ( 'Steven', 100 )) names | COLUMN_VALUE | Steven 1 | Steven 2 | Steven 3 | Steven 4 | Steven 5 | Steven 6 | Steven 7 | Steven 8 | Steven 9 | Steven 10 | Steven 11 | Steven 12 | Steven 13 | Steven 14 | Steven 15 | Steven 16 | Steven 17 | Steven 18 | Steven 19 | Steven 20 | Steven 21 | Steven 22 | Steven 23 | Steven 24 | Steven 25 | Steven 26 | Steven 27 | Steven 28 | Steven 29 | Steven 30 | Steven 31 | Steven 32 | Steven 33 | Steven 34 | Steven 35 | Steven 36 | Steven 37 | Steven 38 | Steven 39 | Steven 40 | Steven 41 | Steven 42 | Steven 43 | Steven 44 | Steven 45 | Steven 46 | Steven 47 | Steven 48 | Steven 49 | Steven 50 |
|---|
Ta Da! No Need to Use TABLE Anymore
SELECT COLUMN_VALUE
FROM lotsa_names ( 'Steven', 100 ) names | COLUMN_VALUE | Steven 1 | Steven 2 | Steven 3 | Steven 4 | Steven 5 | Steven 6 | Steven 7 | Steven 8 | Steven 9 | Steven 10 | Steven 11 | Steven 12 | Steven 13 | Steven 14 | Steven 15 | Steven 16 | Steven 17 | Steven 18 | Steven 19 | Steven 20 | Steven 21 | Steven 22 | Steven 23 | Steven 24 | Steven 25 | Steven 26 | Steven 27 | Steven 28 | Steven 29 | Steven 30 | Steven 31 | Steven 32 | Steven 33 | Steven 34 | Steven 35 | Steven 36 | Steven 37 | Steven 38 | Steven 39 | Steven 40 | Steven 41 | Steven 42 | Steven 43 | Steven 44 | Steven 45 | Steven 46 | Steven 47 | Steven 48 | Steven 49 | Steven 50 |
|---|