Board index » delphi » How to close ORA-cursor-handle in StoredProc returning dataset as a result

How to close ORA-cursor-handle in StoredProc returning dataset as a result

Hello,

I am using Delphi 4 with Oracle 8 in a multi-tier application and have
stored procedures to open datasets in my program. I have made a test
application where I permanently close and open a dataset. After 47 loop
I get an exception ORA-01000 'maximum cursor exceeded...'. BDE does not
close the cursor behind the scenes when I close my clientdataset.

On the app-server-side I use a stored procedure with a referenced cursor
as in out parameter as suggested in this newsgroup. Below is the snippet
from the client application:

  for i:=0 to 100 do
  begin
    with DataModule do
    begin
      cds.Close;  // --> ClientDataSet with a stored proc as a provider
      cds.Open;  //        stored proc has a referenced cursor as
parameter
    end;
  end;

I hope that somebody can help !

Varol Denizoglu

 

Re:How to close ORA-cursor-handle in StoredProc returning dataset as a result


Varol:

I stumbled across this in the KNOWN ISSUES on the web site while trying to
get ref cursors to work.

Quote
>>    Area: oracle 8\stored procedures
>>    Reference Number: 516 (Published: 10/12/98)
>>    Status: Open
>>    Date Reported: 8/25/98
>>    Severity: Serious
>>    Type: Unknown
>>    Problem:

>>    You can't execute a stored procedure in a package
>>    that returns a REF CURSOR using the Oracle 8
>>    driver.

Sounds like you have found some solution.  Would you care to share the
Oracle SQL code used to create the stored procedure?

TIA

Quote
Varol Denizoglu wrote in message

<379B0091.E14B9...@hrz1.hrz.tu-darmstadt.de>...
Quote
>Hello,

>I am using Delphi 4 with Oracle 8 in a multi-tier application and have
>stored procedures to open datasets...
> After 47 loops I get an exception...>Varol Denizoglu

Re:How to close ORA-cursor-handle in StoredProc returning dataset as a result


Hi Don,

just take a look at: http://www.borland.com/devsupport/sqllinks/pub-6.html

depending on your delphi version and bde you need some update packs. (I use
Delphi 4)

Varol

Quote
Don Siders wrote:
> Varol:

> I stumbled across this in the KNOWN ISSUES on the web site while trying to
> get ref cursors to work.

> >>    Area: oracle 8\stored procedures
> >>    Reference Number: 516 (Published: 10/12/98)
> >>    Status: Open
> >>    Date Reported: 8/25/98
> >>    Severity: Serious
> >>    Type: Unknown
> >>    Problem:

> >>    You can't execute a stored procedure in a package
> >>    that returns a REF CURSOR using the Oracle 8
> >>    driver.

> Sounds like you have found some solution.  Would you care to share the
> Oracle SQL code used to create the stored procedure?

> TIA

> Varol Denizoglu wrote in message
> <379B0091.E14B9...@hrz1.hrz.tu-darmstadt.de>...
> >Hello,

> >I am using Delphi 4 with Oracle 8 in a multi-tier application and have
> >stored procedures to open datasets...
> > After 47 loops I get an exception...>Varol Denizoglu

Re:How to close ORA-cursor-handle in StoredProc returning dataset as a result


Thanks Varol.

Quote
>just take a look at: http://www.borland.com/devsupport/sqllinks/pub-6.html

I've seen (and tried) this example.  It does not work for Oracle 8.0.4,
D3.02 CS, BDE 5.01.  Returns ORA-001036 Invalid Name/Number.

The SP works fine in SQL*Plus, but BDE will not digest the result set.

Quote
>(I use Delphi 4)

I'd like to move to D4, but our PHBs decided to wait for D5.

Thanks again...

Don

Re:How to close ORA-cursor-handle in StoredProc returning dataset as a result


Hi Don,

I had similar error messages just like you until I made the update patches 2
und 3 on Delphi 4. Then suddenly it worked, but I don't know how to make it run
on Delphi 3.

Varol

Quote
Don Siders wrote:
> Thanks Varol.

> >just take a look at: http://www.borland.com/devsupport/sqllinks/pub-6.html

> I've seen (and tried) this example.  It does not work for Oracle 8.0.4,
> D3.02 CS, BDE 5.01.  Returns ORA-001036 Invalid Name/Number.

> The SP works fine in SQL*Plus, but BDE will not digest the result set.

> >(I use Delphi 4)

> I'd like to move to D4, but our PHBs decided to wait for D5.

> Thanks again...

> Don

Re:How to close ORA-cursor-handle in StoredProc returning dataset as a result


Quote
Varol Denizoglu wrote in message <37A84AE1.14E03...@xgroup.de>...
>Hi Don,

>I had similar error messages just like you until I made the update patches
2
>und 3 on Delphi 4. Then suddenly it worked, but I don't know how to make it
run
>on Delphi 3.

Did Patches 2 or 3 have a new BDE?  I currently use BDE 5.01.  My BDE
version Info is:

IDAPI32.DLL     5.0.0.38 (2)
IDSQL32.DLL    5.0.0.38 (0)
SQLORA32.DLL 5.0.0.38 (1)
IDPROV32.DLL  4.5.1.4 (0)
DBCLIENT.DLL  5.0.1.32 (0)
...

Does this appear to be the latest BDE?

Don

Re:How to close ORA-cursor-handle in StoredProc returning dataset as a result


Hi Don,

I have the same BDE-Version (5.01) just like you, but I use
DLL32: SQLORA8.DLL and
VENDOR INIT: OCI.DDL

I am not sure about the version numbers of the other dlls. Where did you get
this information ?

Varol

Quote
Don Siders wrote:
> Varol Denizoglu wrote in message <37A84AE1.14E03...@xgroup.de>...
> >Hi Don,

> >I had similar error messages just like you until I made the update patches
> 2
> >und 3 on Delphi 4. Then suddenly it worked, but I don't know how to make it
> run
> >on Delphi 3.

> Did Patches 2 or 3 have a new BDE?  I currently use BDE 5.01.  My BDE
> version Info is:

> IDAPI32.DLL     5.0.0.38 (2)
> IDSQL32.DLL    5.0.0.38 (0)
> SQLORA32.DLL 5.0.0.38 (1)
> IDPROV32.DLL  4.5.1.4 (0)
> DBCLIENT.DLL  5.0.1.32 (0)
> ...

> Does this appear to be the latest BDE?

> Don

Other Threads