Oracle

UI Bakery allows you to easily connect to an Oracle database with no need for additional layers (APIs or 3rd-party services).

Prerequisites

If your database is hosted behind the Firewall, add the following IP address to the Firewall whitelist:

52.176.109.125

Configuration

To connect your Oracle database, follow the below steps:

  1. go to Data sources page - click Connect

  2. Select Oracle from the list of the available data sources

  3. Specify your credentials and click Test connection to ensure, that UI Bakery can connect to your data source:

4. When the connection is successful, click Connect Data source.

UI Bakery will extract the database structure and store column types. This data will be used to suggest the proper table columns and form field types.

If you need to be able to override the default data source database with a custom value in an action, you can enable the setting Allow override database name in action:

Once enabled, when configuring the action, you will be able to specify the custom database. In case a database is not specified, the default data source database will be used.

Usage

Once the connection between UI Bakery and Oracle database is established, you can interact with the data.

  1. Add a new action, choose the data source and select the SQL query action type. Specify your query and click Execute action to launch it.

Note that pre-defined actions, such as Load Table, Create Row, etc. are not supported for Oracle datasource.

2. Check the Result section to make sure the data is loaded.

Working with Stored Procedures

Let's assume that you have a stored procedure that performs a calculation and returns the result through an OUT parameter. For example, the following stored procedure adds two numbers and returns the result as an OUT parameter:

CREATE OR REPLACE PROCEDURE sum_numbers(
    num1 IN INT,
    num2 IN INT,
    result OUT INT
)
IS
BEGIN
    result := num1 + num2;
END;
/

Then to call this stored procedure from UI Bakery, use the following query:

BEGIN 
    sum_numbers(5, 7, {{ {dir: 3003} }});
END;

Note the {{ {dir: 3003} }} substitution. This indicates to Oracle that this parameter is an output parameter and should return a value. This expression should be passed for all OUT parameters of the stored procedure.

Last updated