#pipe row

[tjmoracle]

Table Functions: 9i Feature Worth Revisiting (Part 2)

[tjmoracle]

Table Functions: 9i Feature Worth Revisiting (Part 1)

[tsworman]

Bulk Collecting and pipe lining rows.

I'm not sure what to call the following code other than elegant. It sits inside a loop over a cursor, inside a pipelined function. The result of this is cast to a table and then used inside another procedure (follow me?). The queries being built and run are dynamic and produce a result based off a dynamic data set. Doing this ensures that as tables and their indexes change, we won't need to change our code to find new or updated rows. 

v_query := 'select distinct' || v_new_p1.come_column || ' from some_schema.' || v_new_p1.some_table|| ' where ' || v_new_p1.index_column '=''' || v_new_p1.index_value || ''''; execute immediate v_query bulk collect into t_temp_values; for v_temp_values in t_temp_values.first .. t_temp_values.last loop                    pipe row(v_temp_values); end loop;

I never thought I'd need to use execute immediate, bulk collect, a custom type and pipelined function combined to produce something. They are all neat features on their own. Together it seems a bit crazy.