Oracle data source functionality

Record identification for Oracle

To support modification or deletion of records, this data source inspects the table schema for a primary key that can be used to uniquely identify each record. If that is not available, the table essentially becomes read-only. Attempting to refresh/reload a record after it was initially loaded, or saving changes after modifying or deleting, will fail with errors. Also, inserting new records will fail with errors.

Optimistic locking for Oracle

When optimistic locking is operating, there will be a time stamp on each record to indicate the last time it was modified. When saving changes to a record, the time stamp on the record in the database is compared to the time stamp from the last time the record was fetched. If they are different, an error will be raised. This avoids inadvertently overwriting changes that have occurred since the record was fetched.

Oracle does not provide a generic time stamp for all records. Therefore, optimistic locking is not provided for collections coming from this data source unless a time stamp column is configured.

Triggers in the data base must maintain this column so that it is updated every time the record is changed.

To indicate which column to use as the time stamp, specify the TimeStampColumn attribute on the collection definition.

Supported statements for Oracle

You can load, insert, modify and delete data from tables as long as they have a primary key. Optimistic locking will be available as long as a TimeStampColumn is available.

Data can be loaded from views. However, if they do not have a primary index, inserts, updates and deletes do not work.

You can execute stored procedures and scalar functions with InvokeMethod and get the return value and output parameters

Transactions for Oracle

All data handling and method invocations are performed within a transaction. If an error occurs, the transaction will be rolled back.

You must use the RAISE_APPLICATION_ERROR procedure or otherwise have a runtime error to cause the transaction to rollback.

BLOB handling for Oracle

Oracle supports the large object data types, BLOB, CLOB and BFILE. These appear as properties with a type of IPFDocumentI. (This is the same data type that you get from the Mongoose or MSSQL data source for images and files).

You are free to fetch and use these columns like any other column. However, for performance, they are not actually loaded into memory when fetched. Instead, they are streamed from the database only when their data is actually called for.

For example, if a table contains a set of videos. They will be streamed directly from the database to the video player in the browser without ever being loaded into memory. This avoids out-of-memory situations that can come from staging large data in memory before use.