Integrating Decisions with External Oracle Database

Overview

Oracle Database is a multi-model database management system commonly used for running online transaction processing, data warehousing, and mixed database workloads. This tutorial demonstrates how to Integrate Decisions with Oracle Database.

Oracle Integration

This example, will demonstrate how to Integrate Decisions with Oracle Database. Once the table has been added, Specific tables and custom queries can be created in the connection folder.

1. Begin by Creating Database Connection. In the Portal, navigate to System > Integrations > Databases and click CREATE CONNECTION on the Folder Actions Panel.  2. First, define a desirable Connection Name. 

3. Then, from the Database Type drop down, pick ORACLE.

Next, there are option to specify properties to connect to Oracle Server. Which are:

4. Click TEST before saving. If the Test Status is Success, then click OK to save this Database Integration.

5. The database connection being created can be found in System > Integrations > Databases Folder.

Fetch Size

In v8.12 and higher the Steps Raw SQL and Run Query [QueryName] have a configuration under CONNECTION SETTINGS called Fetch Size.

This setting allows for control over how many records are within a data packet. Either Step will return all records, but the driver will only fetch the number of records entered at a time. There are performance implications to how this is set. A database with 800,000 records will likely timeout if the fetch size is too low or too high. A single fetch of 800,000 records or 800,000 fetches of a single record will cause performance issues. The recommended way to determine the best performance is to start with a value of 100 and then try 500, 1000, and finally 5000.

Parameterized Query Characters