TOraQuery vs TOraSQL and ftCursor output params
Posted: Wed 19 Aug 2020 21:37
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.
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
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;
How can I make the code above work with a TOraQuery?
Thank you
John