REF CURSOR

[valeriv]

How get multiple ref cursors variables from Oracle Package procedure in Delphi, Can I use only " IN " direction.
About like
---------
TYPE t_cursor is REF CURSOR;
procedure proc (
id_in IN NUMBER,
cur1 OUT t_Cursor, /*without IN */
cur2 OUT t_Cursor,
cur3 OUT t_Cursor,
cur4 OUT t_Cursor,
);
-------------------
in OraOleDB with ADODB it is possible...

 

[valeriv]

Sorry? Can I use only "OUT" direction

 

[Marco Kalter]

This is only possible when using a TOracleQuery. You can assign 4 other TOracleQuery instances to the 4 cursor parameters (through SetComplexVariable), call the procedure, and fetch the results.

You can also set TOracleDataSet.ExternalCursor to one of the four TOracleQuery instances if you need TDataSet compatibility for the 4 result sets.

 

[valeriv]

Thanks. Show me code, please. I'm novice in Delphi.

 

[Marco Kalter]

begin
  with OpenCursorQuery do
  begin
    Clear;
    SQL.Add('begin');
    SQL.Add('  employee.opencursor(:p_empcursor, :p_order);');
    SQL.Add('end;');
    DeclareVariable('p_empcursor', otCursor);
    DeclareVariable('p_order', otString);
    SetComplexVariable('p_empcursor', CursorQuery);
    SetVariable('p_order', 'ename');
    Execute;
  end;
  MyDataSet.ExternalCursor := CursorQuery;
  MyDataSet.Active := True;
end;

See also the "Cursor variables" paragraph in the User's Guide and Help file.

 

[valeriv]

Thanks.
This is Code exactly from User's Guide... Where in this code 4 retriving cursors? What is and so on.. See on my PROC on top this topic. I don't understand.
Sorry me poor English.
How can I get cur1,cur2,cur3,cur4 Records (.. or DatSets or DataRows or Recordsets or Array or any other named term for looping and manipulate any field (column)) and etc.
Help me, Please. It's very important for me in my now and always future workplace.

 

[Marco Kalter]

Here is an example with 2 cursors:

begin
  with OpenCursorQuery do
  begin
    Clear;
    SQL.Add('begin');
    SQL.Add('  employee.opencursor(:p_empcursor1, :p_empcursor2, :p_order);');
    SQL.Add('end;');
    DeclareVariable('p_empcursor1', otCursor);
    DeclareVariable('p_empcursor2', otCursor);
    DeclareVariable('p_order', otString);
    SetComplexVariable('p_empcursor1', CursorQuery1);
    SetComplexVariable('p_empcursor2', CursorQuery2);
    SetVariable('p_order', 'ename');
    Execute;
  end;
  MyDataSet1.ExternalCursor := CursorQuery1;
  MyDataSet1.Active := True;
  MyDataSet2.ExternalCursor := CursorQuery2;
  MyDataSet2.Active := True;
end;

OpenCursorQuery, CursorQuery1 and CursorQuery2 and TOracleQuery instances that you can create at design-time or run-time, and that need to be linked to the same session.

MyDataSet1 and MyDataSet2 are TOracleDataSet instances.

 

[valeriv]

Thank you very much! I've understood

 

[valeriv]

oops! No propery MyDataSet1.ExternalCursor in ver.3.4.6.4.
What i nedd do with
MyDataSet1.ExternalCursor := CursorQuery1;
MyDataSet1.Active := True;
MyDataSet2.ExternalCursor := CursorQuery2;
MyDataSet2.Active := True;

 

[Marco Kalter]

The ExternalCursor property is new in 4.0, so if you need TDataSet compatibility for the cursor result set you will need to upgrade.

 

[igorilia]

REF cursor for ver 3... is easy.
It is example for DOA ver 3... :

tech_pkg.getConsColumns - returns Ref cursor

You create 2 cursors : qCursor and CursorQuery.
qCursor -
with qCursor do begin
Close; CursorQuery.Close;
Clear;
SQL.Add('begin');
SQL.Add(' tech_pkg.getConsColumnsp_Colcursor, _const_name);');
SQL.Add('end;');
DeclareVariable('p_Colcursor', otCursor);
DeclareVariable('p_const_name', otString);
SetComplexVariable('p_Colcursor', CursorQuery); // Binding queries
SetVariable('p_const_name', ConstraintName);
Execute;
end;

-- then simple Open result cursor.

with CursorQuery do begin
Execute;
while not Eof do begin
if Length(dfs) > 0 then
dfs := dfs + ';' + FieldAsString(0)
else
dfs := FieldAsString(0);
if Length(mfs) > 0 then
mfs := mfs + ';' + FieldAsString(1)
else
mfs := FieldAsString(1);
if Length(sKeyWhere) > 0 then sKeyWhere := sKeyWhere + ' AND ';
sKeyWhere := sKeyWhere + dfs + '= ' + FieldAsString(0);
if Length(sUpd) > 0 then sUpd := sUpd + ',';
sUpd := sUpd + dfs + '= :' + FieldAsString(0);
if Length(sw) > 0 then
sw := sw + ' AND ';
sw := sw + FieldAsString(0) + '= :' +FieldAsString(0);
qtable.DeclareVariable(FieldAsString(0), otInteger);
Next;
end;
Close;
qCursor.Close;
if Length(sw) > 0 then
qtable.SQL.Add('WHERE '+ sw);

qTable.MasterFields := mfs;
qTable.DetailFields := dfs;
end;