Oracle database with time stamp with time zone

You can create a transport that connects to an Oracle database. When you specify Oracle as the database type, a Time Zone parameter becomes available. This setting is used to work with the Oracle TIMESTAMP WITH TIME ZONE data type.

Once the transport is created, you can create a transport map as follows:

• For inserting data into a table with a column with TIMESTAMP WITH TIME ZONE
• For selecting data from a table with a column with TIMESTAMP WITH TIME ZONE
• For calling a stored procedure with a TIMESTAMP WITH TIME ZONE for Input and Output parameters

Assumptions

The following is assumed:

• You are familiar with Structured Query Language (SQL) and relational databases.
• The Oracle database must be version 10g or later.
• The database must exist and be populated with tables.
• You have reviewed the information in Creating the database transport

Creating the transport

Follow these steps to create a transport that can connect to an Oracle database version 10g or later.

1. From the Workbench left pane, expand the node that you want to associate the new transport with.
2. Expand Enterprise, right-click the Transports icon to display its pop-up menu, and then click New.
   The Transport window appears.
3. In the Name box, type a unique name for the transport.
4. Click the Type down arrow, and then select DB.
   The Parameters tab changes to accommodate the definition of a database transport.
   
       
5. From the Parameters tab, use the DB Type down-arrow, and then select Oracle.
   The Time Zone parameter becomes available.
6. Use the Time Zone down-arrow to select the appropriate time zone. You can choose a specific time zone from the list or use the Use platform setting option which will enable the runtime time zone setting. This time zone specification will be used when sending values for the TIMESTAMP WITH TIME ZONE column to the database.
7. Fill in the remaining parameters as appropriate, and then click Save. 

Using the Oracle database transport, you can create a transport map that can be used in conjunction with a table that has a TIMESTAMP WITH TIME ZONE column. The following provides three example transport maps.

Example 1: Creating a transport map for inserting data into TIMESTAMP WITH TIME ZONE column

The following describes how to create a transport map that inserts data into a table with a TIMESTAMP WITH TIME ZONE column.

1. From the Workbench left pane, expand the node that contains the Oracle transport that has the time zone support.
2. Expand Enterprise, right-click the Transport Map icon to display its pop-up menu, and then click New.
   The Transport Map window appears.
3. Name the transport map as appropriate, and then set the Version Mismatch option to either Pass or Fail.
4. Use the Transport Type down-arrow to select DB.
   This will filter the list of pre-defined transports displayed in the Transport Name list to only database transports.
5. Use the Transport Name down-arrow to select the transport that has the time zone support.
   
6. From under To Enterprise, use the Schema down-arrow to select the appropriate schema.
7. Use the Table down-arrow to select the table with a TIMESTAMP WITH TIME ZONE column, and then select Insert.
   The tab at the bottom of the To Enterprise section shows as Insert with the current table column names.
   
   Notice the Insert tab and the TIMESTAMP WITH TIME ZONE column. At this point the Variable column on the Insert tab is empty.
8. Click Map Table to automatically create logical variables. The new variables are added to the Variable column on the Insert tab. In addition, using Map Table with Insert automatically populates the Input tab with logical variables.
   
   The default TIMESTAMP data type is used. You can change the data type to STRING. In this case when mapping a STRING variable to this Input parameter in the trigger, you should format it as 'yyyy-MM-dd HH:mm:ss.SSS'.
9. Save the transport map. 
10. You can specify the transport map using the Transaction action in the trigger. When the Insert operation executes the transport handler will convert the TIMESTAMP value that is mapped to the TIMESTAMP WITH TIME ZONE column using the time zone specification in the transport. Note that the trigger logic always works with UTC timestamps. 

Example 2: Creating a transport map for selecting data from a TIMESTAMP WITH TIME ZONE column

The following example describes how to create a transport map for selecting data from a table with a TIMESTAMP WITH TIME ZONE column.

1. From the Workbench left pane, expand the node that contains the Oracle transport that has the time zone support.
2. Expand Enterprise, right-click the Transport Map icon to display its pop-up menu, and then click New.The Transport Map window appears.
3. Name the transport map as appropriate, and then set the Version Mismatch option to either Pass or Fail.
4. Use the Transport Type down-arrow to select DB.This will filter the list of pre-defined transports displayed in the Transport Name list to only database transports.
5. Use the Transport Name down-arrow to select the appropriate transport.
6. From under To Enterprise, use the Schema down-arrow to select the schema. 
7. Select the table with a TIMESTAMP WITH TIME ZONE column.
8. Use the Action down-arrow, and then select Select.
9. In the Max. Rows box, for this example accept the default 1. The value indicates that the select can return up to that value as the number of rows of data.The tab at the bottom of the To Enterprise section shows as Select with the current table column names.
   
10. To automatically generate variables that will return the selected data click the Map Table button. You will see variables generated on the output tab.
    
    The default TIMESTAMP data type is used. You can changes the data type to STRING in which case the STRING data returned to the trigger will be formatted as 'yyyy-MM-dd HH:mm:ss:SSS timezone'. Example is '2012-09-26 15:15:53:985 America/New_York'.
11. You can optionally build a Where clause that uses the TIMESTAMP WITH TIME ZONE column. You can map an input variable to be used in the Where clause expression. To do this create an input variable in the Input tab on the Transport Map window, and then map the variable to columns used in a Where clause as follows:
    
    If the input logical variable is of type STRING, the trigger will format as 'yyyy-MM-dd HH:mm:ss.SSS'.
12. Save the transport map.
13. You will specify the transport map using the Transaction action in the trigger. 
    When the trigger event occurs, the Select operation will retrieve data from the database using criteria based on the Where clause.

Example 3: Creating a transport map for stored procedure with TIMESTAMP WITH TIME ZONE column

The following example shows how to create a transport map that invokes a stored procedure with input and output parameters of type TIMESTAMP WITH TIME ZONE.

1. From the Workbench left pane, expand the node that contains the Oracle transport that has the time zone support.
2. Expand Enterprise, right-click the Transport Map icon to display its pop-up menu, and then click New.
   The Transport Map window appears.
3. Name the transport map as appropriate, and then set the Version Mismatch option to either Pass or Fail.
4. Use the Transport Type down-arrow to select DB.
5. Use the Transport Name down-arrow to select the appropriate transport.
6. Using the Action down arrow, select Stored Procedure.
7. Using the Procedure down-arrow, select the stored procedure you want to use as the target of the operation.
   
   The Stored Procedure tab becomes populated with information about the stored procedure.
8. Click Map Table to automatically create the logical variables.
   
   The Input tab consists of logical variables to map a value to an IN parameter of the stored procedure. The Output tab consists of logical variables that are the destination of data returned from the Out parameter of the stored procedure.

The default TIMESTAMP data type is used. You have the option to change the data type to STRING. 

Data type conversions for input and output parameters

The following table shows how timestamp data provided by the trigger to the transaction action (In) or timestamp data provided by the transaction action to the trigger (Out) is handled.

Limitations

Currently, the following SQL operations are not supported.

• Stored procedure INOUT parameter of type TIMESTAMP WITH TIME ZONE.
• Select with Update of TIMESTAMP WITH TIME ZONE parameter.