Limitations on Use of Direct SQL Against Siebel Databases

This topic explains when it is allowable to perform "Direct SQL" against Siebel Databases. It should be noted immediately that most direct SQL is forbidden and should not be considered an option. Following are some of the many reasons:

• Siebel Remote, Replication Manager, and Mobile require that all changes to data are captured in the Docking Transaction log (S_DOCK_TXN* tables). While the Object Managers and EIM ensure that this happens, a direct SQL statement against the database will not.

• The Siebel ERD is massive, with over 5,000 tables and 20,000 foreign keys out-of-the-box.

  • Due to the schema objects defined in the Repository, the Object Managers and EIM can ensure referential integrity for these foreign keys, which would be incredibly difficult to do manually.

  • Data is denormalized into various base and intersection tables–it would be very easy to miss (or do incorrectly) and cause data corruption.

• Many of the validation rules (such as "numeric value between 0 and 100") in Siebel CRM are maintained at the Business Object Layer–issuing direct SQL would bypass those validation rules, allowing invalid data into the database.

• The Siebel utilities that manage the coordination of the physical database schema and the logical Siebel Repository schema are intended to be "self healing"–for example, if there are columns, indexes, or other items in the physical layer that are not in the logical layer, they will be removed, so all schema changes must be made in the Siebel Repository.

• Oracle Tech Support cannot trace what happened to produce unusual data or diagnose why unwanted behavior is occurring when data was modified through direct SQL.

For more information, see the following topics:

• Definitions

• Allowed and Forbidden SQL Usage

• Exception Process