TOraQuery vs TOraSQL and ftCursor output params

[jdorlon]

Hello,

ODAC Version: 11.2.4.
Delphi Version: RAD Studio 10.1 Berlin
Oracle Version 19c client and server
(But I don't think any of this is version specific)

The code below works as expected. qryParam shows 2 rows returned by the cursor.

Code: Select all

procedure TForm1.FormCreate(Sender: TObject);
var
  sess: TOraSession;
  qry: TOraSQL;
  qryParam: TOraQuery;
begin
  sess := TOraSession.Create(nil);
  sess.HomeName := 'OraClient19Home1_32bit';
  sess.Username := 'jdorlon';
  sess.Password := 'jdorlon';
  sess.Server := 'azure_19c_plug';
  sess.LoginPrompt := False;
  sess.connected := true;

  qry := TOraSQL.Create(nil);
  qry.Session := Sess;

  qry.sql.add('CREATE OR REPLACE PROCEDURE DUAL_REFCURS(cur OUT SYS_REFCURSOR) IS');
  qry.sql.add('BEGIN');
  qry.sql.add('  OPEN cur');
  qry.sql.add('  FOR');
  qry.sql.add('  select 1 as data from dual');
  qry.sql.add('  union all');
  qry.sql.add('  select 2 from dual;');
  qry.sql.add('END;');
  qry.Execute;
  qry.SQL.Clear;

  qry.sql.add('begin');
  qry.sql.add('  DUAL_REFCURS(:cur);');
  qry.sql.add('end;');
  qry.ParamByName('cur').dataType := ftCursor;
  qry.ParamByName('cur').paramType := ptOutput;
  qry.Execute;

  qryParam := TOraQuery.Create(nil);
  qryParam.Session := sess;
  qryParam.Cursor := qry.ParamByName('cur').AsCursor;
  qryParam.Open;
  if qryParam.eof then
    memo1.Lines.Add('qryParam returned no rows')
  else
    memo1.Lines.Add('qryParam returned ' + IntToStr(qryParam.RecordCount) + ' rows');

  qryParam.Free;
  qry.free;
  sess.free;
end;

However, if I change the code above so that qry is a TOraQuery instead of TOraSQL, then qryParam is EOF after I open this.

How can I make the code above work with a TOraQuery?

Thank you

John

[MaximG]

We reproduced the issue according to your description and are currently busy with analysis of the causes leading to the malfunction of our product. We hope for an early resolution of the problem. We will let you the results as soon as we get them.