Oracle Writer

Writing to Oracle Databases

Important: The out of the box Oracle Writer only works with the 32 Bit version of NFT, and only on Windows Operating Systems!

In order to write to an Oracle Database, use the following target definition:

<Target config="[configuration]">oracle://[target table/view]</Target>

The table or view into which the records are to be inserted has to exist upfront. The Oracle Writer does not create tables, it relies on them being present before anything is inserted.

Neither does the Oracle Writer support upserting (Salesforce-like); records are always just inserted into the table. On the feature list for future version may be upserting, and/or at least deleting values from the database.

As with the Oracle Reader, configuration can be read either directly from the configuration string, or from a configuration file containing the connection string to the Oracle Database.

The field definitions must in their names match 100% with the field names in the Oracle target table/view. By leveraging the config tag of the field definitions, special behaviour of the Oracle Writer can be achieved (see below).

Example:

<?xml version="1.0" encoding="utf-8"?>
<Transformation>
  <Source config="delim=';'>file://mytable.csv</Source>
  <Target config="@oracle_config.txt">oracle://MYUSER.MYTABLE</Target>

  <Fields>
    <Field name="ID">$Id</Field>
    <Field name="FIRSTNAME">$FirstName</Field>
    <Field name="LASTNAME">$LastName</Field>
    <Field name="FULLNAME">$FirstName + " " + $LastName</Field>
    <Field name="CHANGEDDATE" config="date">$ChangedDate</Field>
  </Fields>
</Transformation>

In this example, data from the mytable.csv file is read and inserted into the MYUSER.MYTABLE in the Oracle Database specified via the connection string in the oracle_config.txt text file (see below).

Data is written into the fields with the names ID, FIRSTNAME, LASTNAME, FULLNAME and CHANGEDDATE. The field CHANGEDDATE is a Date/Time field, and is marked accordingly, so that the Oracle Writer can correctly write a date/time value into the data base (see below for more information on field configuration).

Configuring Fields

The following special treatments are supported by the Oracle Writer as to what is accepted inside the config attribute of the <Field> definitions:

• string: No special treatment (this is also the default). The value is passed as a string.
• int: The value is treated as an integer number
• double: The value is treated as a floating point number with double precision
• date: The value is treated as a date/time value

Writing date values

In case you pass on the configuration string date to a field, NFT will parse the string to a date/time representation. If the string is empty, a value of 01-01-01 00:00:00 will be inserted into the target table. If the string cannot be parsed to a date/time representation, the processing will be cancelled and the transaction rollbacked.

For valid string representations of date/time, see the following page: https://msdn.microsoft.com/en-us/library/system.datetime.parse(v=vs.100).aspx

The date time format yyyy-MM-dd HH:mm:ss will always be interpreted correctly. Example: 2015-07-17 21:41:22.

Using a connection string directly

Pass the connection string directly into the configuration of the target tag:

<Source config="Data Source=MyOracleDB;Integrated Security=yes">oracle://MYUSER.MYTABLE</Target>

Parameters can be used inside the connection string, if needed.

### Using a connection string inside a text file

In order to read the connection string from a file, use the following syntax (using the @ character):

<Target config="@oracle_connection.txt">oracle://MYUSER.MYTABLE</Target>

Sample content of the oracle_connection.txt file, also using a parameter (§env§):

Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=§env§)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));
User Id=myUsername;Password=myPassword;

Sample Connection Strings

See https://www.connectionstrings.com/oracle/