Re: err. 004729 Cannot find inactive database cursor. Program too large.

[Jerry Keith Neal <jkneal@INDIANA.EDU>]

  Thanks for the suggestions. I have tried increasing the queries in the M
  file with no results. You are correct about oracle not supporting
temptables. This has been our major obstacle in converting to oracle. To
overcome this we create these as permanment tables in the users schema
that get droped in the nightly build. I would like to know more about the
error. The sqr users manual says to reduce the complexity of the report
for a fix which is not an option for us at this point. When sqr runs a
cursor does it keep it open to the end of the report? If so , would there
be any way to help close some of these? We have tried putting commits
after each sql statement but this has not corrected the problem. We would
like to reslove the problem without modifying the code too much, but if
needed I will consider trying to combine some of the sql statements or the
use of stored procedures.

  thanks again,
  Jerry

On Fri, 17 Mar 2000, Arjan Hoornstra wrote:

> That's what you get from downgrading from Sybase to
> Oracle... but seriously, I think there maybe some
> possiblities:
> - use -M option to increase number of queries
> - combine seperate procedures by increasing the number
> of joined tables in a query
> - use stored procedures rather than sqr procedures for
> insert/update/delete stuff
> - use temptables for storage... oh wait, Oracle
> doesn't have a tempdb, right? create some tables then
> for intermediate storage
>
>
>
>
> --- Chris Shelton <cshelton@INDIANA.EDU> wrote:
> > I'm working with Jerry in trying to resolve this
> > problem, and wanted to
> > provide some additional details.  The 4729 error
> > comes from SQR, and has
> > this description provided in the manual:
> >
> > The program has too many concurrent database
> > cursors.  Reduce the
> > complexity of the program.
> >
> > We are in the process of converting many SQR reports
> > from sybase to
> > oracle.  This same report has worked perfectly
> > against our sybase ASE
> > 11.5.1 database for a couple of years now.  After
> > modifying all sybase
> > specific SQL statements so that they work with
> > oracle 8.1.5, we receive
> > this error during the compilation step in the
> > report.
> >
> > We have tried several possible resolutions to this
> > problem, including:
> > - adding commit's after each insert statement in
> > begin-sql paragraphs,
> > - increasing the value of the open_cursors database
> > parameter from 300 to
> > 1000, and
> > - increasing the amount of stack space available to
> > OS processes.
> >
> > None of these changes have made any difference with
> > this error.  This is
> > one of our more complex SQR reports, with about 9000
> > lines of code.
> >
> > Thanks for any suggestions that might help to
> > resolve this problem.
> >
> > Chris Shelton
> > Indiana University
> >
> > On Fri, 17 Mar 2000, Neal, Jerry wrote:
> >
> > > Hello,
> > >
> > >  I am getting err. 004729 when trying to run an
> > sqr report. I have 110 sql
> > > statements in the report. I get the error right
> > away
> > > with no other error messages. Any help would be
> > greatly appreciated.
> > >
> > > The version: SQR/4.3.4/IBM RS6000/AIX 4.2.1/Oracle
> > 7.3.4
> > >
> >
>
> __________________________________________________
> Do You Yahoo!?
> Talk to your friends online with Yahoo! Messenger.
> http://im.yahoo.com
>
>