Previous Topic Next topic Print topic


Additional Considerations for Oracle

Applications migrating from OpenESQL for ODBC, or from other database systems, should note the following.

Positioned updates and HOLD cursors

OpenESQL for OCI converts positioned updates and deletes to searched updates using Oracle ROWIDs. When a FOR UPDATE cursor is opened, Oracle locks rows up to the first COMMIT. Thereafter OpenESQL still performs positioned updates, but rows fetched after the first COMMIT are not locked.

Scrollable cursors

In OCI, scrollable cursors are always read only. In all other ways, OCI scrollable cursors behave like ODBC keyset cursors. OpenESQL for OCI converts all non-forward only cursors to OCI read only scrollable cursors.

Isolation levels

Oracle OCI does not support read uncommitted isolation and defaults to read committed, based on snapshot/read consistency. However, since Oracle uses row versioning, reads generally do not block. Oracle OCI also does not support repeatable read isolation, so OpenESQL for OCI converts repeatable read isolation to serializable.

EXEC SQL Connect statement processing

  • CONNECT WITH PROMPT is not supported
  • Type 6 connect (user supplied connection string) is not supported
  • Output connection strings are not supported

Dynamic SQL

For dynamic SQL DESCRIBE, OCI may return different data types for NUMBER columns with a scale of 0. Some ODBC drivers may describe these as DECIMAL or NUMERIC. OCI describes them as tinyint (scale <= 2), smallint (scale <= 4), int (scale <= 9), bigint (scale <=18) or decimal (scale > 18).

Previous Topic Next topic Print topic