Cursor Mode Property
Note:
In Release 5.0 and later, cursor mode is handled automatically by Oracle Forms.
This property is now obsolete, and should not be used. In particular, cursor
mode should never be set to Close. The following information is provided only
for historical and maintenance purposes.
Description
Defines the cursor state across transactions. The cursor refers to the memory
work area in which SQL statements are executed. This property is useful for
applications running against a non-ORACLE data source.
The following settings are valid for the Cursor_Mode property:
Setting
|
Description
|
Open (the default)
|
Specifies that cursors should remain open across transactions.
|
Close
|
Specifies that cursors should be closed when a commit is issued.
|
Applies to form
Set programmatically
Refer to Built-in
GET_FORM_PROPERTY
SET_FORM_PROPERTY
OPEN_AT_COMMIT
Usage Notes
- Because ORACLE allows the database state to be maintained across transactions,
Oracle Forms allows cursors to remain open across COMMIT operations. This
reduces overhead for subsequent execution of the same SQL statement because
the cursor does not need to be re-opened and the SQL statement does not always
need to be re-parsed.
- Some non-ORACLE databases do not allow database state to be maintained across
transactions. Therefore, you can specify the CLOSE_AT_COMMIT parameter of
the Cursor_Mode option to satisfy those requirements.
- Closing cursors at commit time and re-opening them at execute time can degrade
performance in three areas:
- during the COMMIT operation
- during future execution of other SQL statements against the same records
- during execution of queries
- Oracle Forms does not explicitly close cursors following commit processing
if you set the property to CLOSE_AT_COMMIT. This setting is primarily a hint
to Oracle Forms that the cursor state can be undefined after a commit.
Oracle Forms maintains a transaction ID during all types of transaction
processing. For instance, Oracle Forms increments the transaction ID each
time it opens a cursor, performs a commit, or performs a rollback.
When Oracle Forms attempts to re-execute a cursor, it checks the transaction
ID. If it is not the current transaction ID, then Oracle Forms opens, parses,
and executes a new cursor. Only the last transaction ID is maintained.
- If you query, change data, then commit, Oracle Forms increments the transaction
ID. Subsequent fetches do not re-open and execute the cursor, for the following
reasons:
- Oracle Forms does not attempt to handle read consistency issues, nor
does it handle re-positioning in the cursor.
- Oracle Forms expects ORACLE or the connect to return an end-of-fetch
error when trying to fetch from an implicitly closed cursor.
- On a subsequent execution of the query, Oracle Forms opens a new cursor.
- When using this property in conjunction with transactional triggers, you,
the designer, must manage your cursors. For example, you might want to close
any open queries on the block whenever you perform a commit.