Database input for the Batch Processor

The Batch Processor supports the reading of input data from database tables and the writing of the results to database. However, the following limitations should be noted:

Supported Databases

Database connectivity is implemented using JDBC and so any database that provides JDBC drivers can be used with the Batch Processor. Oracle Policy Automation supports the following databases, for which the OPA types are described in the tables that follow:

Oracle

OPA type Compatible Oracle (11g) Database data types Formatting Notes
boolean number, any text type

For number and char data types: 0 = false, 1 = true.

Custom format available, see "boolean-format" in XML file configuration.

date date, Timestamp, any text type

When reading or writing date attributes, time and time zones are ignored.

Oracle Policy Automation date attributes will have time removed when they are read in. For text fields, the format must be: "yyyy-mm-dd" (see Value formats for csv input).

datetime date, Timestamp, any text type

Datetime attribute will be resolved to seconds (milliseconds or smaller will be truncated). For text fields, the format must be: "yyyy-mm-dd HH:mm:ss" (see Value formats for csv input).

text

any text field (char, text, nchar, nvarchar and so on)

 
time of day date, Timestamp, any text type

Time of day only supports a string value formatted as "HH:mm:ss" (see Value formats for csv input).

number and currency any number (float, decimal, bigint, int, smallint and so on) Number fields are rounded to accuracy of 15 decimal places.

SQL Server

OPA Type Compatible SQL Server (2008 R2) data types Formatting Notes
boolean bit, number, time, any text type

Bit data type supported.

For number and char data types: 0 = false 1 = true.

Custom format available, see "boolean-format" in Data mapping in the XML configuration file. 

date date, datetime smalldatetime, datetime2, any text type Any datetime field read in as an Oracle Policy Automation Date will have the time component removed. "yyyy-mm-dd" (see Value formats for csv input).
datetime datetime, smalldatetime, datetime2, any text type

For all datetime data types, milliseconds will not be read in. Oracle Policy Automation does not support milliseconds in date or time values. "yyyy-mm-dd HH:mm:ss" (see Value formats for csv input).

Note: the datetimeoffset data type is not supported in this release.

text all text fields (char varchar, varchar2 and so on)  
time of day time, any text types

Time of day only supports a string value formatted as "HH:mm:ss" (see Value formats for csv input).

For time data type, milliseconds will not be read in. Oracle Policy Automation does not support milliseconds in date or time values

number and currency

any number, money, any text type

Number fields are rounded to accuracy of 15 decimal places.

See also:

Value formats for database output