Fetch from cursor as parameter

[sinys]

OraQuery1.SQL.Text := 'begin scott.checkpoints.getpersonallist(p_cur => :p_cur); end;';
OraQuery1.Execute;

OraQuery1.ParamByName('p_cur').AsCursor.CanFetch; // return false and it strangely

OraQuery2.Cursor := OraQuery1.ParamByName('p_cur').AsCursor;
OraQuery2.Open; // Show empty grid (with correct fields, but without records)

What's wrong?

[AlexP]

Hello,

Please provide the script of your procedure, and check if the query used in the procedure returns the result.

[sinys]

Code: Select all

create or replace package scott.test is
  TYPE cur IS REF CURSOR;
  procedure emps(P_cur out cur);

end test;
/
create or replace package body scott.test is

  procedure emps(P_cur out cur) is   
  begin
    open p_cur for select * from scott.emp;
  end;
  
end test;
/

Code: Select all

procedure TForm2.Button1Click(Sender: TObject);
begin
  OraQuery1.SQL.Text := 'begin test.emps(:p); end;';
  OraQuery1.ParamByName('p').ParamType := ptOutput;
  OraQuery1.ParamByName('p').DataType := ftCursor;
  OraQuery1.Execute;

  if OraQuery1.ParamByName('p').AsCursor.CanFetch then // return false and it strangely
    ShowMessage('yes!');

  OraQuery2.Cursor := OraQuery1.ParamByName('p').AsCursor;
  OraQuery2.Open; // Show empty grid (with correct fields, but without records)
end;

OraStoredProc1 working correct, but I want use OraQuery for this situation.

[AlexP]

Hello,

We have checked your sample - it works correctly: data is displayed in the grid after opening OraQuery2. The CanFetch method will return True in case if not all data was fetched from the client, otherwise, the method will return False. Please specify the IDE, ODAC, and Oracle versions. We checked this situation on Delphi 7, ODAC 8.6.12, Oracle 11 (OCI and Direct)

[sinys]

Delphi XE2 upd4, ODAC 8.6.12, Oracle 11 (OCI)

See attached file

[AlexP]

Hello,

Thank you for the information, we have reproduced the problem with the cursor and will try to fix it as soon as possible.
As I wrote you before, the CanFetch method will return True only in case when the FetchAll property is set to False and the number of records in selection is greater than the FetchRows property value.

[sinys]

Our customers are waiting this fix.
How I can discover opportunity fetching from cursor for my example?

[AlexP]

Hello,

We have investigated the problem in more details, and have found that such behaviour is due to the Oracle restriction on work with cursors. Oracle doesn't allow repeatedly retrieve data from an already opened cursor, and since, when running the Execute method of the first Query, the cursor is opened and data is fetched, then, when attempting to open the same cursor again, data is not returned and you get only the structure of the cursor. To solve the problem, you should work with the cursor in the first Query, not assigning the parameter to the other DataSet

[sinys]

if I have 2 parameters Cursor type like this:

Code: Select all

create or replace package body scott.test is
  procedure emps(P_cur out cur, P_cur2 out cur) is   
  begin
    open p_cur for select * from scott.emp;
    open p_cur2 for select * from scott.dept;
  end;
end test;

Code: Select all

  OraQuery1.SQL.Text := 'begin scott.test.emps(:p, :p2); end;';
  OraQuery1.ParamByName('p').ParamType := ptOutput;
  OraQuery1.ParamByName('p').DataType := ftCursor;
  OraQuery1.ParamByName('p2').ParamType := ptOutput;
  OraQuery1.ParamByName('p2').DataType := ftCursor;
  OraQuery1.Execute;

  OraDataSource1.DataSet := OraQuery1.ParamByName('p2').AsDataSet; // It dosen't work!

  OraDataSource1.DataSet := OraQuery1; // It's work, but It showing first cursor result.

How I can choose result from second cursor?

[sinys]

>How I can choose result from second cursor?
I understand, like my example above!
And same situation for OraStoredProc.
The first cursor we should get from self like dataset, and the second cursor from parameter.
But do not you think this behavior is odd?

TOraSQL works properly without these oddities.

[AlexP]

Hello,

OraQuery and OraStoredProc are TDataSet descendants, i.e., they can work with datasets. Therefore, for convenience, the first parameter, which is a cursor, is opened, and the dataset is generated. For next cursors additional datasets are required. TOraSQL doesn't generate datasets, but runs queries only, therefore, if there is a cursor in its parameters, you can work with it only via an additional dataset.

[sinys]

I found an undocumented parameter CursorState, I think it should come in handy for programmers.

[AlexP]

Hello,

Yes, you can use this property to define the cursor status, we will add description of this property to our help