Oracle

Oracle Database is a powerful, reliable, and flexible enterprise-level relational database management system (RDBMS). This technical documentation provides a comprehensive guide to integrating Oracle with Nected. You will learn how to configure the Oracle integration, query the database via datasets, trigger Oracle operations as rule actions, and troubleshoot common issues. By the end of this guide, you will know to seamlessly connect Oracle with Nected and leverage its capabilities for your applications.

Setting Up Oracle Integration

You need to configure an Oracle integration to establish a connection between Nected and Oracle for seamless data interaction. This section outlines the step-by-step process for setting up the integration effectively.

1. Adding Integration: Select ORACLE from the integrations page. This choice initiates the process of setting up an integration for ORACLE.

2. Configuring Connection Settings

   Configuring connection settings is a critical step in establishing the connection between Nected and your Oracle database. The following parameters need to be defined:

   • Environment Type: Choose whether you are configuring the integration for a "Staging" or "Production" environment. The selection of the environment type determines the context in which the connector will operate. By default, the environment type is set to “Staging”.

   • Integration Name: Assign a unique and meaningful name for the integration. It is essential that this name is distinct throughout the Nected platform and does not contain any spaces.

   • Host (Database IP Address): Specify the IP address or hostname of the Oracle database to which you want to connect.

   • Port Number: Enter the port number on which Nected should communicate with the Oracle database. The default port for Oracle is 1521.

   • Username: Provide the username of the authorized user who has the necessary privileges to connect to the database and perform read and write operations.

   • Password (optional, encrypted): You may enter the password for the authorized user. Passwords are encrypted to ensure maximum security for your database. If the database user does not have a password, you can leave this field blank.

   • Database / Service Name: Provide the Service Name or Database of the Oracle database.

   • Additional Params: allow you to add custom key-value pairs to fine-tune the functionality and behavior of your Oracle connection.

   Note: If using Nected cloud, Nected IP address (43.205.43.45) must be added to the allow-list on your database server to secure your connections. Not required in on-premise setup

Next Steps After Integration

1. Testing the Connection

   After configuring the connection settings, it's crucial to test the connection to ensure that Nected can successfully communicate with the Oracle database. This test verifies that the provided information is accurate and the connection is established without issues.

2. Publishing

   Before publishing an integration, it's essential to first conduct a connection test. This ensures that everything functions correctly and meets the required standards. Once the connection test is successful, you'll be able to proceed with publishing the integration. Depending on your needs, you can choose to publish in a "Staging" environment, typically used for development or testing, or in a "Production" environment for live deployment.

You need to publish staging and production connectors in their respective environments. If a connector is published only in staging, then it cannot be used in the production environment, and any call to a rule in the production environment will fail, giving a "connector not published in production" error.

Querying Oracle Database via DataSet

Nected provides a powerful feature that allows you to query and interact with your Oracle database through datasets. This section will guide you through the process of adding a new dataset, writing SQL code for querying the Oracle database and performing various operations, including creating rows and executing join operations.

Adding a New DataSet:

Before you can query the Oracle database, you need to add a new dataset to your Nected environment. Follow these steps:

1. Navigate to Datasets: Click on "Datasets" in the left navigation panel to access the Datasets page.

2. Create a Dataset: Click the "+ Create Dataset" button on the Datasets page. Choose the database connector associated with the dataset you wish to create. Note that you can create multiple datasets for one connector.

3. Dataset Information: Complete the dataset information form, including details such as the dataset name, dataset type (staging or production), the source for dataset parameters.

Finally, write the specific database query to retrieve data from your connected database. For example, to connect the complete database within your dataset, write:

SELECT * FROM table_name; -- Change the table_name with your database table_name

Query Types:

In addition to basic querying, you can perform various operations on the data retrieved from the Oracle database. Here are some common tasks you can carry out:

1. Aggregations: You can use aggregations to summarize and analyze your financial data. For instance, to calculate the total deposit amount for a specific day:

   
   SELECT
       transaction_date,
       SUM(amount) AS total_deposit
   FROM
       financial_transactions
   WHERE
       transaction_type = 'Deposit'
   GROUP BY
       transaction_date;
   

   This query aggregates the total deposit amount for each distinct transaction date.

2. Filtering: Filtering is essential for retrieving specific subets of data. To find all transactions above a certain amount, you can use:

   
   SELECT
       transaction_date,
       amount,
       description
   FROM
       financial_transactions
   WHERE
       amount > 1500;
   

   This query filters and selects transactions with amounts greater than $1000.

3. Sorting: Sorting data can be useful for visualizing trends or identifying the most significant transactions. To sort transactions by amount in descending order:

   This query returns transactions sorted by the amount in descending order.

4. Grouping and Aggregating: Combining grouping and aggregation is helpful for summarizing data based on certain criteria. To find the total transaction amount by transaction type:

   
   SELECT
       transaction_type,
       SUM(amount) AS total_amount
   FROM
       financial_transactions
   GROUP BY
       transaction_type;
   

   This query groups transactions by type and calculates the total amount for each type.

5. Joins: If your database has multiple related tables, joins can be used to combine data from different sources. For instance, to link transactions with account details:

   
   SELECT
       t.transaction_id,
       t.transaction_date,
       t.amount,
       a.account_name
   FROM
       financial_transactions t
   JOIN
       accounts a
   ON
       t.account_id = a.account_id;
   

   This query joins the financial_transactions table with an accounts table to retrieve transactions along with their corresponding account names.

These SQL operations and queries can help you extract valuable insights and generate reports from your financial data stored in the financial_transactions table. The principles and techniques demonstrated here can be adapted and expanded upon to suit specific analysis and reporting needs in the fintech industry or any database scenario.

Tip: Always ensure that your SQL code is well-tested in the "Staging" environment before deploying it to the "Production" environment to prevent potential data integrity issues and unexpected consequences.

Next Steps After Adding Datasets

1. Integration to Rule or Workflow: Once you've established your dataset, you can integrate it directly into rules and workflows. This integration allows you to create various conditions that define your business logic. To learn how to integrate a published dataset into rules, refer to the Rule documentation. For workflows, refer to the database node documentation.
2. Querying: Once you've integrated the dataset into your rule or workflow, you can apply queries according to your specific requirements. Customize your queries by referring to supported and unsupported query types listed below. This customization allows you to tailor your data interactions precisely to your business needs, ensuring efficient and effective utilization of your integrated dataset.

Supported Queries for Creating a Dataset:

When creating a dataset in Nected, you have the flexibility to retrieve and filter data using a variety of SQL queries. These queries help you shape your dataset to meet your analytical needs. Below are the supported queries for creating a dataset:

Query Type	Description	Example
Select Queries	Retrieve data from one or more tables, with various filtering and sorting options.	SELECT customer_name, order_date FROM customers JOIN orders ON customers.customer_id = orders.customer_id;
Aggregation Queries	Perform data analysis with aggregation functions like SUM, COUNT, GROUP BY, etc.	SELECT product_id, SUM(sale_amount) AS total_sales FROM sales GROUP BY product_id;
Join Queries	Combine data from multiple tables using JOIN operations for more complex queries.	SELECT customers.customer_name, orders.order_date FROM customers JOIN orders ON customers.customer_id = orders.customer_id;
Subqueries	Use subqueries to nest one query inside another, often in the WHERE or FROM clauses.	SELECT product_name FROM products WHERE product_id IN (SELECT product_id FROM sales WHERE sale_amount > 1000);
Union Queries	Merge the results of two or more SELECT queries into a single result set.	SELECT customer_name FROM customers UNION SELECT supplier_name FROM suppliers;
Intersection Queries	Retrieve common records from two or more SELECT queries.	SELECT product_id FROM products WHERE price > 50 INTERSECT SELECT product_id FROM sales;
Difference Queries	Find records that exist in one SELECT query but not in another.	SELECT product_id FROM products WHERE price > 50 EXCEPT SELECT product_id FROM sales;

These queries allow you to create diverse and tailored datasets for your specific analytical requirements.

Non-Supported Queries for Creating a Dataset:

Certain types of queries are not supported when creating a dataset in Nected. These include:

Query Type	Description
DDL Statements	Data Definition Language statements like CREATE TABLE, ALTER TABLE, DROP TABLE, etc.
DML Statements	Data Manipulation Language statements like INSERT, UPDATE, DELETE without SELECT.
Transactional Control	Statements like COMMIT, ROLLBACK, SAVEPOINT.
Procedural Statements	Stored procedures, functions, and triggers are not supported in direct dataset queries.

Triggering Oracle Operations as Rule Actions:

Nected provides the capability to trigger Oracle operations as rule actions. This feature enables you to automate database operations based on specific conditions and events. Here is how you can define rules and trigger Oracle operations:

1. Inserting New Financial Transactions:

1. Static Query

   To insert new financial transactions into the Oracle database as a rule action, you can use the INSERT INTO statement with static values. For example, if a rule is triggered to log a new deposit:

   INSERT INTO financial_transactions (transaction_date, transaction_time, account_id, transaction_type, amount, currency_code, description, status)
   VALUES ('2023-10-21', '09:30:00', 106, 'Deposit', 1500.00, 'USD', 'Client Payment', 'Completed');
   

   This query adds a new deposit transaction to the database with the specified details.

2. Tokenized Query Using Output Data, Custom Input and/or Data from Dataset

   To make the query dynamic and utilize data from the rule's output or custom input or dataset, you can use tokenized queries with token attributes. For example, if you want to insert a new transaction with attributes from either of those:

   INSERT INTO financial_transactions (transaction_date, transaction_time, account_id, transaction_type, amount, currency_code, description, status)
   VALUES ('{{.dataSet.transaction_date}}', '{{.dataSet.transaction_time}}', {{.customInput.account_id}}, '{{.dataSet.transaction_type}}', {{.outputData.amount}}, '{{.outputData.currency_code}}', '{{.outputData.description}}', 'Completed');
   

   This query fetches the transaction details from the rule's output data, custom input and details from dataset attributes.

2. Updating Transaction Status:

1. Static Query

   You can use the UPDATE statement with static values as a rule action to modify the status of specific transactions. For instance, if a rule action is set to change the status of a transaction:

   Copy

   UPDATE financial_transactions
   SET status = 'Completed'
   WHERE transaction_id = 21;
   

   This query updates the status of a transaction with a specific transaction_id to 'Completed'.

2. Tokenized Query Using Output Data, Custom Input and/or Data from Dataset

   To make the query dynamic and utilize data from the rule's output or custom input or dataset, you can use tokenized queries with token attributes. For example, if you want to update the status of a transaction based on either of these:

   Copy

   UPDATE financial_transactions
   SET status = '{{.outputData.new_status}}'
   WHERE transaction_id = {{.customInput.transaction_id}};
   

   This query adjusts the status of a transaction using data from the rule's output, custom inputs. If needed, you can also use token to extract data from dataset attributes.

These SQL queries demonstrate how you can use rule actions with both static and tokenized queries to perform data modification operations within the Oracle database in response to specific triggers or conditions. Rule actions offer flexibility in automating data updates, ensuring data accuracy, and enhancing the functionality of your fintech application or database.

Supported Queries for Writing an Action

Nected allows you to perform various actions on your data, such as inserting, updating, and merging records, all while ensuring data integrity. Here are the supported queries for writing an action:

Query Type	Description	Example
Insert Queries	Add new records to a table.	INSERT INTO products (product_name, price) VALUES ('New Product', 50);
Update Queries	Modify existing records in a table.	UPDATE customers SET email = '[e-mail]' WHERE customer_id = 123;
Merge Queries	Combine data from source table(s) into a target table, typically used for data synchronization.	MERGE INTO target_table USING source_table ON target_table.id = source_table.id WHEN MATCHED THEN UPDATE SET target_table.value = source_table.value WHEN NOT MATCHED THEN INSERT (id, value) VALUES (source_table.id, source_table.value);
Transactional Queries	Execute a sequence of queries within a transaction to ensure consistency.	BEGIN; INSERT INTO orders (order_id, customer_id, order_date) VALUES (789, 123, '2023-11-15'); UPDATE customers SET total_orders = total_orders + 1 WHERE customer_id = 123; COMMIT;

Non-Supported Queries for Writing an Action

When it comes to writing actions in Nected, there are limitations on the types of queries and operations that can be performed to ensure data consistency and security. Here are the queries that are not supported for writing an action:

Query Type	Description	Example
Truncate Table	Truncating a table to remove all records is not supported as actions.	TRUNCATE TABLE orders;
Create, Alter, Drop Queries	Queries for creating, altering, or dropping tables and database schema are not supported as actions.	CREATE TABLE new_table (column1 INT, column2 VARCHAR);
Indexing Queries	Creating or managing indexes on tables is not supported as actions.	CREATE INDEX idx_customer_name ON customers (customer_name);
Stored Procedures	Creating stored procedures and functions within the database is not supported as actions.	CREATE PROCEDURE calculate_total_sales() BEGIN SELECT SUM(sale_amount) FROM sales; END;
Grant and Revoke Permissions	Managing user privileges and permissions on database objects is not supported as actions.	GRANT SELECT ON products TO user1;