Get a table and other out parameters and close the cursor

[kisa]

Hello!

I'd like to get a table and other out parameter(s) from the OraStoredProcedure.

For example:

Code: Select all

create or replace package SelTeszt is
 
TYPE tRecData IS RECORD (
  fiokkod           varchar2(4),
  szamlaszam        varchar2(12)
  ) ;
 
TYPE tTblRecData is TABLE of tRecData;
 

PROCEDURE Get_Data(rcData OUT SYS_REFCURSOR, i_out out number);
 

end SelTeszt;
/
create or replace package body SelTeszt is
 
PROCEDURE Get_Data(rcData OUT SYS_REFCURSOR, i_out out number) as
  rData tRecData;
  aData tTblRecData;
begin
  i_out := 100;
  aData := tTblRecData();
 

  rData.fiokkod:='0300';
  rData.szamlaszam:='012345678';
  aData.Extend;
  aData(aData.Last):= rData;
 

  rData.fiokkod:='0400';
  rData.szamlaszam:='987654321';
  aData.Extend;
  aData(aData.Last):= rData;
 

  open rcData for SELECT fiokkod, szamlaszam FROM table(aData);
end;
end;
/

The odac component working well, but after using the table, the cursor remains open.
In OraStoredProcedure the AutoClose option is set True;
How can I close the unused cursor, without session reconnect or do I have to use a different method to get the parameters?

versions:
os: windows 10
oracle: 12
delphi: 10.2
odac: 10.0.1

Thank You for your help.
Yours sincerely:
KisA

[MaximG]

We have checked the work of ODAC according to the description you provided. To close unused cursors after completion of working with a stored procedure, execute a random query to DB. This behavior does not depend on the work of our components. The thing is that when using OCI to reduce the total number of the packages that are sent to server the information about the necessity of deletion of the unused cursors will be sent during the next query to DB.