Talend: troaclerow vs toracleinput

Question

It seems like both toracleinput and toracelrow can execute a select statement, can someone tell when to use toracleinput component, when to use toraclerow component in talend and what is the difference between them?

thanks M

Answer 1

The major difference between toracleinput and toraclerow is,

• In toracleinput, you can run only SELECT statement. Whereas in toraclerow, you can run any DDL and DML queries

• In toracleinput, you can have only one SELECT statement. Whereas in toraclerow, you can have multiple queries (If you are having select statement followed by delete or any other CRUD statement, then select will not return any resultset).

• You can use toracleinput, when source DB and Target DB are different servers. But in toraclerow, the source and the Target should be the in the Same DB and same Server

Consider the below tables

Employee Table:

+-------+-----------+-----------+-----------+
|   ID  |   Name    |   Salary  |   DeptId  |
+-------+-----------+-----------+-----------+
|   1   |   Name1   |   10000   |   1       |
|   2   |   Name2   |   20000   |   2       |
|   3   |   Name3   |   30000   |   3       |
|   4   |   Name4   |   40000   |   1       |
|   5   |   Name5   |   50000   |   2       |
+-------+-----------+-----------+-----------+

Also consider table

Dept table

+-------+-----------+
|   ID  |   Name    |
+-------+-----------+
|   1   |   Dept1   |
|   2   |   Dept2   |
|   3   |   Dept3   |
+-------+-----------+

In talend, I wanted to select data and join these table and insert into another table named dim_employee which has below format

+-------+-----------+-----------+-----------+-----------+
|   ID  |   Name    |   Salary  |   DeptId  | DeptName  |
+-------+-----------+-----------+-----------+-----------+

In Talend, I can do this in two ways,

1. Using input component,

2. Using Row Component

Using Row component, I can directly write the INSERT INTO SELECT.

Note: When comparing the performance, using Row component is much faster than using Input component in this scenario.