Dynamic SQL and REF CURSORS ?

Hi,

I am trying to return a ref cursor from a stored procedure by defining a dynamic SQL statement, the problem is that I cannot quite figure out how to get the data into the ref cursor. Below is a very simplistic example, please excuse any typo's. The documentation on this topic only shows how to execute the query and put the variables into local PLSQL variables, not ref cursors.

Does anyone have any ideas ?
Regards
Nicholas Tombs

Example (simplistic case) :-

type MyReturnRecordType is

        TableDefinition%RowType;

procedure GetSelectedRecords(SearchValue INTEGER, zCursor IN OUT MyReturnRecordType) is
  queryString VARCHAR2(1024);
  nRows NUMBER;
  iCursor NUMBER;
begin

• Old statement
• open zCursor for
• select * from TableDefinition where ID = SearchValue;
• New Statement queryString := 'select * from TableDefinition where ID = SearchValue';
  • fill in the variable here

   iCursor := dbms_sql.open_cursor;
  nRows := dbms_sql.parse(iCursor,queryString,DBMS_SQL.V7);   nRows := dbms_sql.execute(icursor);

• do a loop here using dbms_sql.Fetch command to return each row --

  dbms_sql.close_cursor(iCursor);

end; Received on Thu Feb 18 1999 - 14:06:54 CST