Cursor in stored procedure

[Milan Bacik]

After migration from version 6.xx to 8.6.12 I have problem with one stored procedure. There is cursor type parameter in this procedure.

PROCEDURE cdbGetCustomerSet (
my_cur IN OUT genericcurtyp,
my_result_code OUT NUMBER,
my_result_msg OUT VARCHAR2,
my_substr_on IN NUMBER,
my_custid IN NUMBER,
my_firstname IN VARCHAR2,
my_lastname IN VARCHAR2,
my_birthday IN DATE,
my_firmname IN VARCHAR2,
my_firmico IN VARCHAR2,
my_firmdic IN VARCHAR2,
my_street IN VARCHAR2,
my_city IN VARCHAR2,
my_pan IN VARCHAR2
)

GetCrsrSetCDB->StoredProcName = "DMSCDB.CDBGETCUSTOMERSET";
GetCrsrSetCDB->Params->CreateParam(ftCursor, "MY_CUR", ptInputOutput);
GetCrsrSetCDB->Params->CreateParam(ftInteger, "MY_RESULT_CODE", ptOutput);
GetCrsrSetCDB->Params->CreateParam(ftString, "MY_RESULT_MSG", ptOutput);
GetCrsrSetCDB->Params->CreateParam(ftInteger, "MY_SUBSTR_ON", ptInput);
GetCrsrSetCDB->Params->CreateParam(ftInteger, "MY_CUSTID", ptInput);
GetCrsrSetCDB->Params->CreateParam(ftString, "MY_FIRSTNAME", ptInput);
GetCrsrSetCDB->Params->CreateParam(ftString, "MY_LASTNAME", ptInput);
GetCrsrSetCDB->Params->CreateParam(ftDate, "MY_BIRTHDAY", ptInput);
GetCrsrSetCDB->Params->CreateParam(ftString, "MY_FIRMNAME", ptInput);
GetCrsrSetCDB->Params->CreateParam(ftString, "MY_FIRMICO", ptInput);
GetCrsrSetCDB->Params->CreateParam(ftString, "MY_FIRMDIC", ptInput);
GetCrsrSetCDB->Params->CreateParam(ftString, "MY_STREET", ptInput);
GetCrsrSetCDB->Params->CreateParam(ftString, "MY_CITY", ptInput);
GetCrsrSetCDB->Params->CreateParam(ftString, "MY_PAN", ptInput);

GetCrsrSetCDB->Params->ParamByName("MY_FIRMNAME")->Value = ...

GetCrsrSetCDB->Active = true;

in 6. version this works fine but in 8.6.12 DBMonitors shows that all input parameters are NULL and procedure dosn't work.

I tried
GetCrsrSetCDB->Execute();
Input parameteres are OK this tim, but I get ODAC exception "SQL statement dosn't return rows".

[AlexP]

hello,

We cannot reproduce the problem - parameters are transferred to the procedure correctly, independently on how the procedure is invoked. Please send the scripts for creating DB objects and a Delphi sample reproducing the problem to alexp*devart*com .

[AlexP]

Hello,

We have received your sample, but the problem cannot be reproduced. We have checked this kind of behavior in both OCI and Direct modes. Please try another Oracle client version.

[Milan Bacik]

Tried with instantclient-basic-nt-11.2.0.3.0 and get same result:

begin
cursortestpkg.cursortest(:MY_CUR, :MY_RESULT_CODE, :MY_RESULT_MSG,
:MY_ROW_COUNT);
end;


Active=true

MY_RESULT_CODE = <NULL>
MY_RESULT_MSG = <NULL>
MY_ROW_COUNT = <NULL> <---- this is problem
Oracle error occurred: 936 (ORA-00936: chybí výraz
ORA-06512: na "DMS2XX.CURSORTESTPKG", line 18
ORA-06512: na line 2)
-----------------------------
Execute

MY_RESULT_CODE = 0
MY_RESULT_MSG = 'OK'
MY_ROW_COUNT = 13

And get Exception "SQL statement dosn't return rows".

Any sugestions for workaround? We use this way because it was the only working way in the past(probably on the BDE yet) to show temporary data in grid and tranfer them in normal data after users decision.

[AlexP]

hello,

We have checked this behavior on the Oracle server version 11.2.0.1.0 and client version instantclient-basic-nt-11.2.0.3.0, and we couldn't to reproduce the error. Both operations are executed with no errors, and both IN and OUT procedure parameters are displayed in the monitor. Please specify the versions of your Oracle server and OS

[Milan Bacik]

Oracle server version(tested against both with same error):

64bit 11.1.0.7 Patch Bundle 41 (Patch 12695278), Windows 2008 R2
32bit 11.1.0.7 Patch Bundle 11 (Patch 8451592), Windows 2003 R2

[AlexP]

Hello,

We still cannot reproduce the problem. We haven't such Oracle version you have specified, we have checked your application on the version 11.1.0.6.0. Try fully uninstall ODAC and all dcu, bpl and pas files, and reinstall it.

[Milan Bacik]

Tried build from diferent PC with ODAC 6.90.0.64 and it works on my PC where build with 8.6.12 dosn't work. So I tried downgrade to ODAC 7.20 by just uninstaling and instaling ODAC and recompiling aplication and it works. I have also tried build with 8.6.12 on yet diferent PC and it is buged too.