Back to Index

 

Overriding the NLS Date format when working with Oracle databases

When writing to an Oracle database object, BlueSky Integration Studio uses the Oracle NLS Date format by default.  This means that if your Oracle database instance has a NLS Date format of 'DD-MON-YYYY', then BlueSky Integration Studio expects to send dates to Oracle in this way as well, otherwise Oracle will not recognize the date value as a valid date.

The problem with this is, sometimes you need to override the NLS date format and send dates in a different format.  For instance, many Oracle systems don't include the time component of the date in the NLS date format by default.

To override the NLS Date format in a BlueSky Integration Studio job, we will employ the PreDML property of any of the database type objects that work with Oracle (ODBC, OleDb, ODP.NET).

In the PreDML property, enter the line of code below which will be sent to Oracle.  The NLS Date format will be different just for THIS SESSION only.  It will not affect other users accessing the system.  We are simply telling Oracle: During this specific connection, use a different NLS date format.

Example:

alter session set nls_date_format ='DD-MON-RR HH:MI:SS';

This is just an example, you need to put in the date format that you would like to use for your specific needs.

Now, in your BlueSky Integration Studio job, you must format the date columns to match the Oracle format specified.  For instance, for the example above the VB.NET equivalent would be:  dd-MMM-yyyy hh:mm:ss

 


© 2003 - 2007 Relational Solutions, Inc. - All rights reserved