Using DataStar With Oracle

DataStar has full support for Oracle since version 2, however before you can use DataStar with Oracle you need to setup some Global Temporary Tables which are used by the scripts during deployment in order to identify if any changes are necessary. It is also important to understand the way in which Oracle handles DDL scripts especially with the use of "EXECUTE IMMEDATE".

Global Temporary Tables

We recommend that you setup a separate user or schema to hold your the Global Temporary Tables (GTT), this way you can keep it separate from the schema that is version controlled via DataStar. You can also use this schema to hold the DataStar Audit tables which are used to track your deployments and hold your reversal metadata should you need to rollback a deployment.

The GTT can be exported from DataStar once you have defined your templates, we recommend that you make use of some special attributes in your template support the definition of the the GTT.

Attribute
GttSchema This is the user of schema where the Global Temporary Tables are going to be created.
GttTableSuffix The applies a suffix to the names of the tables, this can be useful for example if you had tables that were being used in multiple scripts and will ensure that each one is unique. We also recommend that you append a version tag such as "_V1" so that should you need to change the template you can do so without impacting existing scripts.
GttActionColumn It should not be necessary to set this value. The scripts will contain an "action" column which is used by DataStar internally. In the unlikely event that this column name conflicts with an existing column, you can alter the DataStar column name with this attribute. Only change this if you have a conflict.

We also recommend that each table is given an "short" alias, if this is omitted DataStar will generate one automatically however this could cause unnecessary changes in your GTT definitions if you later make a change to the template. To avoid this you should always set an alias for each table.

Exporting Global Temporary Tables

Assuming you have created the templates, the option to export the Global Temporary Tables can be found under "Configuration" tab, and then select the "Template List" button from the "Templates" ribbon. Two buttons are shown at the top "Export GTT" which will export the GTT definitions to a file, or "Create GTT" which will prompt for the database you wish to apply the Global Temporary Tables to.

Note: "Export GTT" and "Create GTT" buttons are disabled by default, they will automatically be enabled when you connect DataStar to an Oracle Database.

Large Columns

By default DataStar will split very large columns into chucks based on a default size (currently set to 4000 characters), if your database version/configuration can handle a size greater than this then you can specify a length greater than 4000 in the "Length" attribute on that particular column in the template and this will prevent the data being split in the generated script.

Database Constraints

The Oracle scripting engine has been designed to minimize any foreign key constraints by executing deletes in the opposite order to any inserts. There may still be occasions when it is necessary to defer the evaluation of constraints. This can be achieved by adding a scripts in your templates folder that is triggered before and after for example:

  <Deployment Weight="200">
    <Script Path="templates/scripts/constraints-disable.sql" Weight="-1"/>
    <Script Path="templates/scripts/constraints-enable.sql" Weight="+1"/>
  </Deployment> 

You can then use the SET CONSTRAINTS ALL DEFERRED; in the disable script and the SET CONSTRAINTS ALL IMMEDIATE; in the enable, When you issue the SET CONSTRAINTS statement, the SET CONSTRAINTS mode lasts for the duration of the transaction, or until another SET CONSTRAINTS statement resets the mode.

Object Scripts

IMPORTANT: Do not use the object DDL scripts with data scripts, they should always be deployed separately. DataStar generates the Object DDL scripts with "EXECUTE IMMEDIATE" which will apply a commit on leaving the execute immediate block, this would create data inconsistencies in the event of an error that needed to be rolled back. It also means that you cannot run the object scripts in a single transaction block and therefore cannot run in a rollback mode.

Data Scripts

Please be aware that when using DataStar with columns that require XML substitutions (see section on Xml Lookups) the first time the script is extracted any document formatting will be removed. This may result in DataStar applying an update when the script is first run against the same database target despite there being no functional changes, this is due to the removal of the XML formatting. This only occurs once when the formatting is removed, subsequent executions would show no updates (this same behaviour applies to SqlServer).