How to build a dynamic PLSQL query to fetch records?

Question

I am trying to create a stored procedure in Oracle and make a dynamic query work to get a bunch of records. I have read many examples but so far I can't get this to work unless I do 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)
IS
BEGIN
  OPEN cur_typ FOR 'select * from complex_query';   
  --CLOSE cur_typ;
END;

And I am executing it this way:

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

This way I get the header names and the records from the query, but I am not closing the cursor that is fetching the results. If I close it then I get nothing at all. I guess leaving it open could cause some kind of memory leak problem at some point.

I have seen similar cases in Oracle documentation where they do something like this:

sql_stmt := 'SELECT * FROM emp';
   OPEN emp_cv FOR sql_stmt;
   LOOP
      FETCH emp_cv INTO emp_rec;
      EXIT WHEN emp_cv%NOTFOUND;
      -- process record
   END LOOP;
   CLOSE emp_cv;

But I have no clue what goes on the "process record" part of the code which would allow to get the whole set of records at the end, plus that my record has a complex structure that doesn't fit with a fixed set of fields as in a table.

Can you please show me the proper way to do this?.

Thanks a lot.

Answer 1

ok CodeRoller, here is my sample code for a unspecified ref cursor:

Code of the Function which returns the ref cursor:

create or replace function test_ref_cursor(pi_sql_statement in varchar2) return  SYS_REFCURSOR is
  result_cursor SYS_REFCURSOR;
begin

  open result_cursor for pi_sql_statement;

  return result_cursor;

end;

Now, in the next step I use this function to get data from v$parameter:

declare
  type t_my_cursor is ref cursor;
  my_cursor t_my_cursor;

  l_rec v$parameter%rowtype;
begin

  my_cursor := test_ref_cursor('select * from v$parameter');

  loop
     fetch my_cursor into 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, to close the ref-cursor!