Page 1 of 1
Fetch from cursor as parameter
Posted: Mon 08 Apr 2013 17:55
by 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?
Re: Fetch from cursor as parameter
Posted: Tue 09 Apr 2013 11:37
by AlexP
Hello,
Please provide the script of your procedure, and check if the query used in the procedure returns the result.
Re: Fetch from cursor as parameter
Posted: Wed 10 Apr 2013 19:07
by 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.
Re: Fetch from cursor as parameter
Posted: Thu 11 Apr 2013 14:29
by 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)
Re: Fetch from cursor as parameter
Posted: Thu 11 Apr 2013 15:08
by sinys
Delphi XE2 upd4, ODAC 8.6.12, Oracle 11 (OCI)
See attached file
Re: Fetch from cursor as parameter
Posted: Fri 12 Apr 2013 13:58
by 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.
Re: Fetch from cursor as parameter
Posted: Mon 22 Apr 2013 07:58
by sinys
Our customers are waiting this fix.
How I can discover opportunity fetching from cursor for my example?
Re: Fetch from cursor as parameter
Posted: Tue 23 Apr 2013 12:00
by 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
Re: Fetch from cursor as parameter
Posted: Tue 23 Apr 2013 12:36
by 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?
Re: Fetch from cursor as parameter
Posted: Tue 23 Apr 2013 14:49
by 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.
Re: Fetch from cursor as parameter
Posted: Wed 24 Apr 2013 13:03
by 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.
Re: Fetch from cursor as parameter
Posted: Thu 25 Apr 2013 12:16
by sinys
I found an undocumented parameter CursorState, I think it should come in handy for programmers.
Re: Fetch from cursor as parameter
Posted: Sat 27 Apr 2013 11:16
by AlexP
Hello,
Yes, you can use this property to define the cursor status, we will add description of this property to our help