Maximum Open Cursors Exceeded

[ycherrs]

Hi, I need you help pleaseeee..

Im doing a certain system that needs to connect to oracle database.
I named it to billcare.
I get connected using dataserver ODBC utilities.
In my script , i included the following syntax. Its purpose is merely to compute the total taxamt. Data is available in oracle database and I need it to store in progress schema.
At startup runtime, everything was fine and I did get the total tax.
However,in the middle of execution , it prompted me this error.

NA000: [Microsoft][ODBC driver for ORACLE]ORA-01000:maximum open cursors execeeded.

How could i avoid this kind of error during cursor execution? Please help....
Thanks very much for the support.

ASSIGN c_sql = "select sum(tax_amt) from billcare.ipinvcd
WHERE billcare.ipinvcd.yr = '" + c_yr +
"' AND billcare.ipinvcd.bil_period = '" + c_period +
"' AND billcare.ipinvcd.acct_n_ = '" + subsacct.acctnum +
"' AND billcare.ipinvcd.contract_n_ = '" + subsacct.contnum + "'" .

RUN STORED-PROC send-sql-statement handle2 = PROC-HANDLE NO-ERROR
( c_sql ).

FOR EACH proc-text-buffer WHERE PROC-HANDLE = handle2:
DISPLAY proc-text.
ASSIGN d_taxamt = DECIMAL(proc-text).
IF ERROR-STATUS:ERROR THEN
DO:
MESSAGE "Error cursor @seq20:" + ERROR-STAT:GET-MESSAGE(ERROR-STAT:NUM-MESSAGES)
VIEW-AS ALERT-BOX.
RETURN NO-APPLY.
END.

CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle2.

 

[vinod_home]

Hi,

Found this using google... But you would have to search in metalink for more info...

According to Metalink note:1020427.102 the cursors remain in
v$open_cursor even if they are closed by the application.
The question is: How does Oracle keep track on which cursors are actually open (=not closed) and which are kept for caching??

The v$sesstat (statistic#=3, opened cursors current) should show current number of opened cursors - but it seems as it shows how many distinct cursors that were opened for this session (until it reaches the maximum=OPEN_CURSORS). Of what use is this statistic then??

Check what your settings are for max_cursors ..