October 2005
Intermediate to advanced
454 pages
14h 44m
English
It’s a little-known fact that table functions can accept collections as well as cursors as parameters . Here’s a simple example.
CREATE OR REPLACE FUNCTION give_me_a_collection ( p_col number_t )
RETURN number_t IS
v_ret_val number_t := number_t( );
BEGIN
v_ret_val.EXTEND(p_col.COUNT);
FOR counter IN v_ret_val.FIRST..v_ret_val.LAST LOOP
v_ret_val(counter) := p_col(counter);
END LOOP;
RETURN(v_ret_val);
END;And here’s one way to execute it in a SELECT.
SQL>SELECT *2FROM TABLE(give_me_a_collection(number_t(1,2,3)));COLUMN_VALUE ------------ 1 2 3