Cursor references support in MIDAS

[P. Maquet]

The DOA Package Wizard offers a nice support of server-side cursor references :

Lets say that you have a PL/SQL package procedure like :

procedure select_something(p_id in NUMBER, crs in out t_ref_cursor) is
begin
open crs for
select ... from ... where id = p_id;
end;

it wil be translated in the Delphi unit in :

procedure select_something(p_id : Double; var crs: TOracleQuery);

After calling "select_something", you simply have to execute the query (crs here) and loop through its data.

Perfect in a 2 tiers environment.

Now how to translate it in MIDAS as TDatasetProvider only supports TDataset descendants what TOracleQuery is not ?

The solution would be simple if one of these questions get a positive answer :

1

 

[F.Hinlopen]

In C++Builder, my only experiment with the package wizard ended with an obscure linker error, and I don't have MIDAS, but here's what I think:
1:
Not really, but anything that looks like or acts like a SELECT (like a cursor) should be in a TOracleDataSet in my opinion.
2:
The documentation explicitly states that this is possible, and the PkgApply demo has a nice example. Apparently you just can't do it with the package wizard.

Originally posted by P. Maquet:
The DOA Package Wizard offers a nice support of server-side cursor references :

Lets say that you have a PL/SQL package procedure like :

procedure select_something(p_id in NUMBER, crs in out t_ref_cursor) is
begin
open crs for
select ... from ... where id = p_id;
end;

it wil be translated in the Delphi unit in :

procedure select_something(p_id : Double; var crs: TOracleQuery);

After calling "select_something", you simply have to execute the query (crs here) and loop through its data.

Perfect in a 2 tiers environment.

Now how to translate it in MIDAS as TDatasetProvider only supports TDataset descendants what TOracleQuery is not ?

The solution would be simple if one of these questions get a positive answer :

1

 

[P. Maquet]

Thanks for your help, Frans.

Both solutions I found (but one is enough) unfortunately need some changes in DOA sources :

1

 

[Marco Kalter]

You cannot use an existing TOracleQuery for the result set of a TOracleDataSet. The SQL.Text of the TOracleDataSet has to contain the PL/SQL Block that calls the program unit that opens the cursor. The dataset must be able to (re)open the cursor when necessary.

------------------
Marco Kalter
Allround Automations