oracle - How to build a dynamic PLSQL query to fetch records? -

i trying create stored procedure in oracle , make dynamic query work bunch of records. have read many examples far can't work unless this:

create or replace procedure givemeresultset(     v_par1  in char,     v_par2  in char,     v_par3  in char,     v_par4  in varchar2,     v_par5  in varchar2,     v_par6  in varchar2,     cur_typ out sys_refcursor) begin   open cur_typ 'select * complex_query';      --close cur_typ; end; 

and executing way:

var c refcursor; execute givemeresultset(null,null,null,null,null,null,:c); print c; 

this way header names , records query, not closing cursor fetching results. if close nothing @ all. guess leaving open cause kind of memory leak problem @ point.

i have seen similar cases in oracle documentation this:

sql_stmt := 'select * emp';    open emp_cv sql_stmt;    loop       fetch emp_cv emp_rec;       exit when emp_cv%notfound;       -- process record    end loop;    close emp_cv; 

but have no clue goes on "process record" part of code allow whole set of records @ end, plus record has complex structure doesn't fit fixed set of fields in table.

can please show me proper way this?.

thanks lot.

ok coderoller, here sample code unspecified ref cursor:

code of function returns ref cursor:

create or replace function test_ref_cursor(pi_sql_statement in varchar2) return  sys_refcursor   result_cursor sys_refcursor; begin    open result_cursor pi_sql_statement;    return result_cursor;  end; 

now, in next step use function data v$parameter:

declare   type t_my_cursor ref cursor;   my_cursor t_my_cursor;    l_rec v$parameter%rowtype; begin    my_cursor := test_ref_cursor('select * v$parameter');    loop      fetch my_cursor l_rec;      exit when my_cursor%notfound;        dbms_output.put_line(l_rec.name || ' = ' || l_rec.value);     end loop;     close my_cursor;  end; 

take care, close ref-cursor!