The Oracle Database connector allows you to execute SQL commands and stored procedures remotely in Oracle database.
Warning
Before executing Oracle connector, you have to install ODP.NET provider on the machine, where the agent will run:
- Download file
ODP.NET_Managed_ODAC122cR1.zip
from url https://www.oracle.com/database/technologies/odac-downloads.html. - Unpack downloaded zip file.
- Open command prompt as administrator.
- Run
install_odpm.bat {DIRECTORY} both true
, where the{DIRECTORY}
(e.g.c:\oracle
) is the ODP.NET installation directory. - Check also if the folder
c:\Windows\Microsoft.NET\assembly\GAC_MSIL\Oracle.ManagedDataAccess
exists. It should contain folder that starts withv4.0_4.122.1.0
and should containoracle.manageddataaccess.dll
library.
Possible uses of the Multi input step include facilitating branch synchronization and enabling the acceptance of multiple inputs within integration processes. By incorporating this feature, you can streamline complex integration tasks and achieve better scalability, parallel processing, and system responsiveness. For more information, refer directly to the article dedicated to the Multi-Input Step
Accessing input data
If the input schema is selected, the input data will be predefined in the temporary table #InputData
(see Predefined Variables). You can use entire table in your statements or target concrete value by using placeholder ${input}
:
Placeholder | Type | Description |
---|---|---|
${inputData} |
temporary table | Temporary table filled with input data. This table will be deleted after the connector run is over. |
${input[<row number>].<column name>} |
value | Takes the value from <row number> th row (zero-based index) and column named <column name> . |
Connector is able to process complex data columns on input schema. Such data are represented as blob column and filled with serialized JSON.
Using placeholders
Assume the connector input data in the following format, followed by placeholders
, which can be used for direct reference to the input data table:
Row number | ID | Login |
---|---|---|
0 |
1 ${input[0].ID} |
Alice ${input[0].Login} |
1 |
2 ${input[1].ID} |
Bob ${input[1].Login} |
Returning all input data
select * from #InputData
ID | Login |
---|---|
1 | Alice |
2 | Bob |
Referring to a specific value from the input data
Selected columns must be always wrapped with double quotes
and must respect case sensitive names
.
select
${input[0].ID} as ID,
"Login" as Login
from #InputData
Returns the list as in the previous example, except that the value from the first row and column of the ID
input table is always used as ID
(${input[0].ID} = 1
):
ID | Login |
---|---|
1 | Alice |
1 | Bob |
Predefined variables
Variable | Data type | Description |
---|---|---|
#InputData |
temporary table | Temporary table filled with input data. This table will be deleted when the connector is finished running. |
:TaskRunID |
number | ID of the currently executing TaskRun . |
:EndpointTokenID |
number | ID of the currently used EndpointToken . |
:EndpointTokenName |
varchar2(4000) | Name of the currently used EndpointToken . |
:DataCheckpoint |
clob | Datacheckpoint value. |
:DataCheckpoint |
clob | Datacheckpoint value. |
LocalVariable |
temporary table | Store data specific to a single task run. |
Returning output data
If the output scheme is selected, the result of the last select
statement, which is not assigned to any variable, is used as the step output. The structure of the query result must match the structure of the output schema.
Selected columns must be always wrapped with double quotes
and must respect case sensitive names
.
If there are some complex input schemas returned to output, the result will contain column data serialized as JSON string.
Example
-- NOT the last select in the statement, data will NOT be sent to the output.
select 0 as ID, Login from #InputData;
-- IS the last valid select in the statement, data WILL be sent to the output.
select 1 as ID, Login from #InputData;
-- Is the last select, but its result is assigned to a variable. Therefore, the data will NOT be sent to the output.
declare maxUserID int = (select max(ID) from DXTask);
-- Using variable as parameter
select ID as TaskRunID, :DataCheckpoint as DataCheckpoint from InputData where ID != :TaskRunID
select :TaskRunID as TaskRunID
-- Select specific local variables
select "ID", "Name" from LocalVariable;
-- Select all local variables
select * from LocalVariable;
Logging
Direct logging from SQL connector into task run log is not supported.
Configuration
Oracle Connection string
Connection string
Input data can be obtained with input data placeholders.
${input.DbName}
will be replaced by value in DbName of the very first row.
Windows Authentication
Data Source=myOracleDB;User Id=/;
Specifying username and password
Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;Integrated Security=no;
Omiting tnsnames.ora
SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));uid=myUsername;pwd=myPassword;
Using Connection Pooling
Data Source=myOracleDB;User Id=myUsername;Password=myPassword;Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;Incr Pool Size=5;Decr Pool Size=2;
Proxy Authentication
Data Source=myOracleDB;User Id=myUsername;Password=myPassword;Proxy User Id=pUserId;Proxy Password=pPassword;
For more examples of connection strings, see official documentation.
Oracle Statement
Statement
Oracle statement to be executed. If the input schema is selected, the input data will be predefined in the temporary table #InputData
.
You can use entire table in your statements or target concrete value by using following placeholders:
Placeholder | Type | Description |
---|---|---|
${inputData} |
temporary table | Temporary table filled with input data. This table will be deleted after the connector run is over. |
${input[<row number>].<column name>} |
value | Takes the value from <row number> th row (zero-based index) and column named <column name> . |
:TaskRunID |
number | ID of the currently executing TaskRun . |
:EndpointTokenID |
number | ID of the currently used EndpointToken . |
:EndpointTokenName |
varchar2(4000) | Name of the currently used EndpointToken . |
:DataCheckpoint |
clob | Datacheckpoint value. |
LocalVariable |
temporary table | Store data specific to a single task run. |
If the output scheme is selected, the result of the last select
statement, which is not assigned to any variable, is used as the step output. The structure of the query result must match the structure of the output schema.
Data checkpoint column
The data checkpoint column is a column (field), from which the platform takes the last row value after each executed task run and stores it as a Data checkpoint. The data checkpoint value can be used in the SQL statements to control, which data should be processed in the next run. You can refer to the value using the predefined variable :DataCheckpoint. Example of use: processing data in cycles, where every cycle processes only a subset of the entire set due to the total size. If you use e.g. record ID as a data checkpoint column, the platform will store after each cycle the last processed ID from the data subset processed by the task run. If your statement is written in a way that will evaluate the value in data checkpoint against the IDs of the records in the data set, you can ensure this way, that only not processed records will be considered in the next task run.
Oracle DB Schema configuration
DB Schema
A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL.
Input & Output Schema
Input
Data schema is optional
The connector does not expect a specific schema. The required data structure can be achieved by correct configuration. Although the selected connector doesn't require a schema generally, the individual integration task step may need to match the output data structure of the preceding task step and use a data schema selected from the repository or create a new input schema.
Output
Data schema is optional
The connector does not expect a specific schema. The required data structure can be achieved by correct configuration. Although the selected connector doesn't require a schema generally, the individual integration task step may need to match the output data structure of the preceding task step and use a data schema selected from the repository or create a new input schema.
Release notes
3.5.0
- Added support for Local variables.
3.4.1
- Added support for multi input.
3.3.2
- Connection and commands timeout is respecting task step timeout.
3.3.1
- Fixed processing sensitive errors.
3.3.0
- @EndpointTokenID, @EndpointTokenName and @DataCheckpoint variables available to use.
- Independence of
Connection string
andSQL Statement
configurations.
3.2.4
- Updated package binaries because of changes in another included connector.
3.2.0
- Implemantation of debug script generating.
3.1.6
- Updated package binaries because of changes in another included connector.
3.1.2
- Updated package binaries because of changes in another included connector.
3.1.1
- Fix processing base64, json and complex schema input data larger than 4000 characters.
3.0.13
- Updated package binaries because of changes in another included connector.
3.0.9
- Updated package binaries because of changes in another included connector.
3.0.8
- Fix processing apostrophe char on input data (column type: string, Json, Base64, Complex schema).
3.0.5
- Fix replacing input placeholders in connection string configuration.
3.0.4
- Fixed processing complex input schema.
3.0.3
- Fixed shared nuget package versions.
3.0.2
- Fixed right processing of nullable properties.