Skip to main content
Version: 2023 R3

Oracle database

The source allows you to retrieve data from the Oracle database.

drex_DataSource_Oracle_screen.png

1. ID​

The data source identifier (if the value is smaller than "0", the source has not been saved in the database yet).

2. Name​

Name of the data source entered by the user.

3. Superior source​

Each data source can contain subordinate sources which are related to a company within the system. If a superior source is referenced from a workflow instance which belongs to a specific company, and that company has a subordinate source associated with it, the query will reference this subordinate source instead. If a superior source has been defined for the source, you need to enter the name of the company for which it is defined.

4. Business entity​

A business entity to which the defined source belongs. If the source has a defined business entity, entering the superior source is required.

5. Data source type​

The type of the current data source. The field specifies the location from which data is retrieved.

6. Connection​

The drop-down list is used to define the authorization required to access the database.

7. Description​

Description of the data source defined by the user.

8. Template​

Selection of the data source template results in the data source returning column types specified for the given template.

Information about required columns can be found in the context help of every data source template (after choosing a template that is different than the default one, a context help icon appears next to the menu).

9. Data source contains personal data​

The checkbox designates the current data source as a source of personal data for other processes.
A relation between the Personal data dictionary and the process which uses such a dictionary as a source of data requires additional configuration in every from field marked as containing personal data.
Once configured, such a relationship allows the system to trace from which instance of a Personal data dictionary the form field obtained its value. Thanks to this, actions that delete or modify (anonymize) personal data are able to locate all the places that obtained data from a specific Personal data dictionary instance.

When a data source is designated as a Personal data dictionary, it is required to specify a column in this data source which is used as a unique personal identifier in this source. It is recommended that this column contains unique, numerical database identifiers.

10. Personal data identifier column​

The field allows you to specify a unique identifier in the form of a database column.

11. Testing environment​

The field allows you to select an environment in context of which data source test is executed.

12. SQL query​

An SQL query can be executed in various contexts, such as data retrieval, connection testing, column list retrieval, and searches performed by picker fields. To handle multiple contexts, the query is additionally processed ("wrapped" in an outer query). Moreover, if the input query contains the "ORDER BY" clause, the clause is moved outside of the outer query.

As a result, there are certain limitations to the query syntax, such as:

  • the query should return the data in the following format: "SELECT column1, column2, … FROM table_name";
  • it is not possible to execute the procedures stored inside of the query;
  • it is not possible to use the "ROW_NUMBER()" function;
  • the columns must be named (e.g. "SELECT column1, CAST(25.65 AS int) AS column2 FROM table_name");
  • when using the "ORDER BY" clause, it is necessary to use the defined column aliases [e.g. "SELECT column1 AS Id, CAST(25.65 AS int) AS column2 FROM table_name ORDER BY ID"];
  • it is not possible to use clause "WITH query AS (SELECT column1, column2, …) …".

In the case of more complex queries, the recommended approach is to define a view or a table-valued function and use it to return the data.

13. Test​

The button executes a data source test. A query is executed and test results are returned. When an error occurs, a message is displayed.

info

To learn more about personal data, read the ARTICLE available in the WEBCON Community website.