Creating a dynamic cursor within a PL/SQL function

[Alexander Danmayer]

We have a problem concerning the usage of dynamically created cursors within a package.

Within the package we parse a dynamic SQL Cursor, the function looks like this

function GetDynCursor(SelectStr in varchar2, DynaCursor in out number) return number
is
begin
DynaCursor:= dbms_sql.open_cursor; -- opening and creating Cursor
DBMS_SQL.PARSE(DynaCursor, SelectStr ,DBMS_SQL.NATIVE); -- Parsing dynamic select string
return 1; -- Means ok
exception
when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
return -3; -- Error occured
end;

This works fine if we use it within PL/SQL and the DBMS_SQL.FETCH_ROWS (Cursor) function.

I want to know, if it is possible to transfer this Cursor-Pointer to the component TOracleDataSet in order to retrieve the rows.

Maybe there also another solution. The goal is to create a dynamic cursor within a PL/SQL-Package and then give this cursor to the TOracleDataSet component and show it in a DBGrid.

 

[Marco Kalter]

Using a cursor for dynamic SQL in combination with Direct Oracle Access is relatively straightforward, but you can't use the DBMS_SQL package. This package hides the actual cursor, so you can't fetch rows from it in a client application after opening it.

You can take a look at the PkgApply demo project that comes with Direct Oracle Access for a nice example of a cursor variable. It includes a package that has a procedure to open a cursor for employees, and accepts the department number and sort column as a parameter. This is maybe not as dynamic as what you had in mind, but you can even pass the entire SQL text if you want.

Your packaged procedure could look something like this:

-- In the package spec
type TDynaCursor is ref cursor;

-- In the package body
procedure GetDynCursor(
  SelectStr in varchar2,
  DynaCursor in out TDynaCursor) is
begin
  open cursor DynaCursor for SelectStr;
end;

------------------
Marco Kalter
Allround Automations