ORA-01000 maximum open cursors exceeded - Delphi - Oracle 
Author Message
 ORA-01000 maximum open cursors exceeded - Delphi - Oracle

Hi
I'm working with:
 Delphi 5.0 Enterprise Edition
 Oracle8 Enterprise Edition Release 8.0.4.0.0

I've been reading a lot of messages about
 "ORA-01000 maximum open cursors exceeded" without
finding a solution to my problem.

I use
 CLOSE_CACHED_OPEN_CURSORS = TRUE
 OPEN_CURSORS = 50

I've proved with a higher OPEN_CURSORS without succeed.

I hope someone can help my on this.
Thank you very much in advance.

Here's the code.

unit U1PBAMAXCUR;
interface
uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, DBTables, StdCtrls;
type
  TForm1 = class(TForm)
    Button1: TButton;
    Memo1: TMemo;  {which cursors are still alive?}
    Database1: TDatabase;
    Edit1: TEdit;  {how many times must call the stored procedure ?}
    Edit2: TEdit;  {how many cursors remains alive before rollback?}
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
    procedure ShowCursors;
  public
    { Public declarations }
  end;

 { ************************ THE PROBLEM ***************************** }
 { ******** Edit1.Text > OPEN_CURSORS leads me to ORA-01000 ********* }
 { ****************************************************************** }        

var
  Form1: TForm1;
implementation
{$R *.DFM}
{----------------------------------------------------------------}
procedure TForm1.Button1Click(Sender: TObject);
Var
 SP:TStoredProc;
 pC:TParam;
 i:integer;
begin
 { **************  CLOSE_CACHED_OPEN_CURSORS = TRUE **************  }
   **************  OPEN_CURSORS = 50                **************  }

 { TYPE TCursor IS REF CURSOR;
   ...
   PROCEDURE Cur( c IN OUT Pkg.TCursor )
   IS
    cAux Pkg.TCursor;
   begin
    open cAux for Select * from dual;
    c := cAux;
   end;
 }
 try
  Database1.Open;
  Database1.StartTransaction;
  for i:= 1 to StrToInt(Edit1.Text) do
  begin
   SP := TStoredProc.Create(nil);
   SP.DatabaseName := Database1.DatabaseName;
   SP.StoredProcName := 'PBA.PKG.CUR';
   pC := SP.Params.CreateParam(ftCursor ,'C',ptInputOutput);
   SP.Params.AddParam( pC );
   SP.Prepare;
   try
      SP.Open;
      SP.Close;
      SP.Unprepare;
      SP.Params.RemoveParam( pC );
   finally
      SP.Free;
      pC.Free;
   end;
  end;
  ShowCursors; { ******* WHY ALL CURSORS ARE STILL ALIVE? ******* }
  { ** HOW CAN I CLOSE THEM WITHOUT DOING COMMIT OR ROLLBACK? *** }
 finally
  Database1.Rollback;
  Database1.Close; {NOW, THEY'RE NOT ALIVE. MAYBE TOO LATE.}
 end;
end;
{----------------------------------------------------------------}
procedure TForm1.ShowCursors;
var Q:TQuery;
begin
  Q := TQuery.Create(nil);
  Q.DatabaseName := Database1.DatabaseName;
  Q.SQL.Add('select SQL_TEXT from sys.v_$open_cursor');
  try
   Q.Open;
   Q.First;
   MEMO1.Lines.Clear;
   while not Q.EOF do
   begin
    MEMO1.Lines.Add(Q.Fields[0].AsString);
    Q.next;
   end;
   Edit2.Text := IntToStr(Q.RecordCount);
   Q.Close;
  finally
   Q.Free;
  end;
end;
{----------------------------------------------------------------}
end.



Wed, 25 Feb 2004 23:00:24 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. ORACLE/D3 Maximum open cursors exceeded problem

2. Maximum Open Cursors Exceeded

3. Maximum open cursors exceeded

4. Oracle Errors ORA-03113 and ORA-01041

5. Oracle and Delphi -Ora-12203

6. oracle 8: ORA-01410 invalid rowid

7. Oracle/BDE Error ORA-03121

8. Maximum record size with ORACLE and 32 bit BDE

9. Delphi and ORACLE REF CURSOR

10. Retriving 2 cursors with StoredProc in Delphi 5.0 from Oracle 8 SQL server

11. Retriving 2 cursors from StoredProc with Delphi 5.0 from Oracle 8

12. Delphi and Oracle cursors

 

 
Powered by phpBB® Forum Software