code listing 2: get_title_or_text procedure 1 create or replace procedure get_title_or_text ( 2 title_like_in in varchar2 3 ,return_title_in in boolean 4 ,joke_count_out out pls_integer 5 ,jokes_out out sys_refcursor 6 ) 7 is 8 c_from_where varchar2 (100) := from jokes where title like :your_title . 9 l_colname all_tab_columns.column_name%type := text . 10 l_query varchar2 (32767). 11 begin 12 if return_title_in 13 then 14 l_colname := title . 15 end if. 16 17 l_query := 0select || l_colname || c_from_where. 18 19 open jokes_out for l_query using title_like_in. 20 21 execute immediate 0select count(*) || c_from_where 22 into joke_count_out 23 using title_like_in. 24 end get_title_or_text.
here is an explanation of the interesting parts of the get_title_or_text procedure in listing 2. line 8. because the get_title_or_text procedure is executing two dynamic queries, differing only in the 0select list, store the rest of those queries (the from and where clauses) in a reusable string. lines 12-17. construct the dynamic query to retrieve all titles or text with a matching title. line 19. associate the result set with the cursor variable, for the specified title filter. lines 21-23. compute the number of rows identified by the query. i can now call this procedure, return a result set, move that data into a collection, and then use the collection in a forall statement, as shown in listing 3. code listing 3: from results to collection to forall 1 declare 2 l_count pls_integer. 3 l_jokes sys_refcursor. 4 5 type jokes_tt is table of jokes.text%type. 6 7 l_joke_array jokes_tt := jokes_tt (). 8 begin 9 get_title_or_text (title_like_in => %insect% 10 ,return_title_in => false 11 ,joke_count_out => l_count 12 ,jokes_out => l_jokes 13 ). 14 dbms_output.put_line ( number of jokes found = || l_count). 15 16 fetch l_jokes 17 bulk collect into l_joke_array. 18 19 close l_jokes. 20 21 forall indx in l_joke_array.first .. l_joke_array.last 22 insert into joke_archive