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.
|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.
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.
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.
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.
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).