Nicholas Tombs schrieb:
>
> 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.
Oracle doesn't too. There's no way to do this (at least with V7 maybe
v8i changes all ?).
< 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;
HTH
Matthias
--
grema_at_t-online.de
Received on Fri Feb 19 1999 - 03:41:17 CST
