Cursors WITH HOLD
Informix®
Informix closes opened cursors automatically when a
transaction ends, unless the
WITH HOLD
option is used in the
DECLARE
instruction:DECLARE c1 CURSOR WITH HOLD FOR SELECT ...
OPEN c1
BEGIN WORK
FETCH c1 ...
COMMIT WORK
FETCH c1 ...
CLOSE c1
ORACLE
With ORACLE, opened cursors using SELECT
statements without a FOR
UPDATE
clause are not closed when a transaction ends: All ORACLE cursors are WITH
HOLD
cursors unless the FOR UPDATE
clause is used in the
SELECT
statement.
Solution
BDL cursors declared WITH HOLD
remain open even
after terminating a transaction with a COMMIT WORK
or ROLLBACK
WORK
.
For consistency with other database brands, database cursors that are
not declared WITH HOLD
are automatically closed, when a COMMIT
WORK
or ROLLBACK WORK
is performed.
Important: Opening a
WITH HOLD
cursor declared with a SELECT
FOR UPDATE
results in an SQL error; in the same conditions, this does not normally appear
with Informix. Review the program logic in order to find
another way to set locks.