Using a cursor variable as a TOracleQuery
Because a cursor variable is equivalent to a TOracleQuery with a select statement, DOA implements the cursor variable type as a TOracleQuery. To use a cursor variable, you need at least two TOracleQuery components: one with a PL/SQL block to call the procedure that opens the cursor, and one for the cursor itself:
with Query1 do
SQL.Add(' employee.opencursor(:p_empcursor, :p_order)');
with CursorQuery do
while not Eof do
The packaged procedure employee.opencursor might look like this:
type t_empcursor is ref cursor return emp%rowtype;
procedure getcursor(p_empcursor in out t_empcursor, p_order in varchar2) is
if p_order = 'ename' then
open p_empcursor for select * from emp order by ename;
elsif p_order = 'empno'
open p_empcursor for select * from emp order by empno;
open p_empcursor for select * from emp;
In this example, Query1 calls the packaged function employee.opencursor to open a cursor to select all employees in a certain order. The CursorQuery is assigned to the p_empcursor variable. You need to use the SetComplexVariable method for this. Next, all rows are fetched and the employee names are displayed in a memo.
Using a cursor variable in a TOracleDataSet
To create a TOracleDataset based on a cursor variable, enter a PL/SQL block with a call to the procedure that opens a cursor in the SQL property, for example:
If you declare the :p_empcursor variable as otCursor, the dataset will detect the cursor variable, and will retrieve the records from the cursor after executing the PL/SQL block when the dataset is opened or refreshed.
To make the results of a cursor variable in a dataset updateable, you must include the rowid in the cursor, just like with the select statement of a normal dataset. To do this, you can define a cursor type and procedure in a package as follows:
cursor empcursor is select emp.*, rowid from emp;
type t_empcursor is ref cursor return empcursor%rowtype;
procedure opencursor(p_empcusor in out t_empcursor, p_order in varchar2);
Now that the rowid is included, the dataset can use it to lock, refresh, update or delete records. You also need to specify the updating table in the UpdatingTable property of the dataset, in this case: emp. Normally the dataset would determine the updating table by inspecting the select statement in the SQL property, but this is invisible in case of a cursor variable.