Skip to content

Using DataStar With Oracle

DataStar has full support for Oracle since version 2.

Oracle 18c introduced the concept of a private temporary table, a memory-based temporary table that is dropped at the end of the transaction. You can use private temporary table with DataStar provided your tables definitions do not contain BLOB or CLOB columns as these are not currently supported by Oracle. If you are on an older version of Oracle, or you need BLOB/CLOB support then you need to use Global Temporary Table (GTT) which is a permanent metadata object that holds rows in temporary segments on a transaction-specific or session-specific basis.

Kerberos Support

DataStar Client

To use Kerberos with the DataStar Client please follow the steps below:

  1. Install MIT Kerberos on the client (http://web.mit.edu/kerberos/dist/) - this is required by ODP.NET when using Kerberos.

  2. Configure your krb5.conf file (in this example we have placed it in folder C:\ProgramData\Kerbeross\krb5.conf)

  3. Setup the sqlnet.ora file with the following settings:

SQLNET.AUTHENTICATION_SERVICES=(KERBEROS5)
SQLNET.KERBEROS5_CONF=C:/ProgramData/Kerbeross/krb5.conf
SQLNET.KERBEROS5_CONF_MIT=TRUE
SQLNET.KERBEROS5_CC_NAME=MSLSA:

  1. In DataStar configure the following on the Database Connection:

  2. Set the DataSource to your data source

  3. Set the Authentication to “Windows Authentication”
  4. Set the TnsAdmin location to the folder containing the sqlnet.ora file (as per step 3)

DataStar Tools

  1. Configure your krb5.conf file (in this example we have placed it in folder C:\ProgramData\Kerbeross\krb5.conf)

  2. Setup the sqlnet.ora file with the following settings (the example below is using the windows credential cache, this can be set to any valid credential cache if running on a different operating system):

SQLNET.AUTHENTICATION_SERVICES=(KERBEROS5)
SQLNET.KERBEROS5_CONF=C:/ProgramData/Kerbeross/krb5.conf
SQLNET.KERBEROS5_CONF_MIT=TRUE
SQLNET.KERBEROS5_CC_NAME=MSLSA:
  1. In DataStar.Tools configure the following (note the connection string User Id should be a forward slash)
Flag Description Example Value
-cs Connection String "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dev.oracle-database.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));User Id=/;"
-ta TnsAdmin (path to the sqlnet.ora) "C:/datastar"

Private Temporary Tables

The PRIVATE_TEMP_TABLE_PREFIX initialisation parameter, which defaults to "ORA$PTT_", defines the prefix that must be used in the name when creating the private temporary table. You can specify this prefix to enable private temporary table on a per component basis in the template using the attribute below.

Attribute
PttTablePrefix The PRIVATE_TEMP_TABLE_PREFIX initialisation parameter, which defaults to "ORA$PTT", defines the prefix that must be used in the name when creating a private temporary table. To enable private temporary tables set the "PttTablePrefix" attribute to the PRIVATE_TEMP_TABLE_PREFIX value (for example "ORA$PTT_").

NOTE: DataStar in some cases you can still use private temporary tables with CLOB columns. DataStar supports an override type so you can effectively treat the CLOB as a VARCHAR2 for the purposes of the script. For example in the template you can specify a column <Column Name="MY_CLOB_COLUMN" DataType="VARCHAR2" Length="22000"> so that the private temporary table uses a VARCHAR2 and casts to a CLOB on the underlying table. This can be useful provided that the data can fit in the maximum varchar length supported by your database.

Global Temporary Tables

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