Close Cursor in Server Express

Dear Chris,

We migrate from Oracle 10g to Oracle 11g, and we have problem with Opened Cursors.

In the versión 10g de Oracle at final the  commit close automatic the cursore, in the  version 11g dont do the same.

Our consult is there are an intruction like CLOSE ALL CURSOR in Server Expres using SQL Command ???.

Hi

From some Oracle documentation I find useful

docs.oracle.com/.../pco03dbc.htm

>>>>>>>

WITH HOLD Clause in DECLARE CURSOR Statements

Any cursor that has been declared with the clause WITH HOLD after the word CURSOR remains open after a COMMIT or a ROLLBACK. The following example shows how to use this clause:

    EXEC SQL

        DECLARE C1 CURSOR WITH HOLD

        FOR SELECT ENAME FROM EMP

        WHERE EMPNO BETWEEN 7600 AND 7700

    END-EXEC.

The cursor must not be declared for UPDATE. The WITH HOLD clause is used in DB2 to override the default, which is to close all cursors on commit. Pro*COBOL provides this clause in order to ease migrations of applications from DB2 to Oracle. When MODE=ANSI, Oracle uses the DB2 default, but all host variables must be declared in a Declare Section. To avoid having a Declare Section, use the precompiler option CLOSE_ON_COMMIT described next. See "DECLARE CURSOR (Embedded SQL Directive)".

CLOSE_ON_COMMIT Precompiler Option

The precompiler option CLOSE_ON_COMMIT is available to override the default behavior of MODE=ANSI (if you specify MODE=ANSI on the command line, any cursors not declared with the WITH HOLD clause are closed on commit):

CLOSE_ON_COMMIT = {YES | NO}

>>>>>>

A example putting oracle option into a example options file.

>>>>>>>>>

#

# lets produce a 64 bit executable using cobsql.

#

export COBMODE=64

#

examplecfg=$PWD/example.cfg

export examplecfg

cat >$examplecfg << EOF

PAGELEN=10000

MAXOPENCURSORS=250

MODE=ORACLE

CLOSE_ON_COMMIT=YES

DECLARE_SECTION=NO

END_OF_FETCH=100

HOLD_CURSOR=NO

RELEASE_CURSOR=NO

IRECLEN=250

ORECLEN=250

UNSAFE_NULL=NO

DBMS=V8

COMP5=NO

EOF

#

rm multiconn

#

cob -xgt -C anim -v -k multiconn.cbl -C"p(cobsql) makesyn sqldebug debugfile end-c verbose config==$examplecfg P(cp) sy endp endp list() listwidth=132" $ORACLE_HOME/precomp/lib/cobsqlintf.o -L$ORACLE_HOME/lib/ -lclntsh -Nnolitlink

#

/usr/bin/time ./multiconn

#

# end

#

>>>>>>>

Hi Pedro,

Tony's response is correct if you are using Oracle Pro*COBOL. If you are using OpenESQL and ODBC then the BEHAVIOR setting determines whether or not a COMMIT will close a cursor. If BEHAVIOR=MAINFRAME then cursors will automatically be closed otherwise they will remain open.

All cursors are automatically closed on a DISCONNECT or when the program terminates.