tOracleInput

Reads an Oracle database and extracts fields based on a query.

tOracleInput executes a database query
with a strictly defined order which must correspond to the schema definition. Then it
passes on the field list to the next component via a Main row link.

Depending on the Talend solution you
are using, this component can be used in one, some or all of the following Job
frameworks:

Standard: see tOracleInput Standard properties.

The component in this framework is generally available.
MapReduce: see tOracleInput MapReduce properties.

The component in this framework is available only if you have subscribed to one
of the
Talend
solutions with Big Data.
Spark Batch: see tOracleInput properties for Apache Spark Batch.

This component also allows you to connect and read data from a RDS Oracle database.

The component in this framework is available only if you have subscribed to one
of the
Talend
solutions with Big Data.

tOracleInput Standard properties

These properties are used to configure tOracleInput running in the Standard Job framework.

The Standard
tOracleInput component belongs to the Databases family.

The component in this framework is generally available.

Basic settings

Property type	Either Built-In or Repository. Built-In: No property data stored centrally. Repository: Select the repository file where the properties are stored.
	Click this icon to open a database connection wizard and store the database connection parameters you set in the component Basic settings view. For more information about setting up and storing database connection parameters, see Talend Studio User Guide.
Use an existing connection	Select this check box and in the Component List click the relevant connection component to reuse the connection details you already defined. Note: When a Job contains the parent Job and the child Job, if you need to share an existing connection between the two levels, for example, to share the connection created by the parent Job with the child Job, you have to: In the parent level, register the database connection to be shared in the Basic settings view of the connection component which creates that very database connection. In the child level, use a dedicated connection component to read that registered database connection. For an example about how to share a database connection across Job levels, see Talend Studio User Guide.
Connection type	Drop-down list of available drivers: Oracle OCI: Select this connection type to use Oracle Call Interface with a set of C-language software APIs that provide an interface to the Oracle database. Oracle Custom: Select this connection type to access a clustered database. Oracle Service Name: Select this connection type to use the TNS alias that you give when you connect to the remote database. WALLET: Select this connection type to store credentials in an Oracle wallet. Oracle SID: Select this connection type to uniquely identify a particular database on a system.
DB Version	Select the Oracle version in use.
Host	Database server IP address.
Port	Listening port number of DB server.
Database	Name of the database.
Oracle schema	Oracle schema name.
Username and Password	DB user authentication data. To enter the password, click the […] button next to the password field, and then in the pop-up dialog box enter the password between double quotes and click OK to save the settings.
Schema and Edit Schema	A schema is a row description. It defines the number of fields (columns) to be processed and passed on to the next component. The schema is either Built-In or stored remotely in the Repository. Click Edit schema to make changes to the schema. If the current schema is of the Repository type, three options are available: View schema: choose this option to view the schema only. Change to built-in property: choose this option to change the schema to Built-in for local changes. Update repository connection: choose this option to change the schema stored in the repository and decide whether to propagate the changes to all the Jobs upon completion. If you just want to propagate the changes to the current Job, you can select No upon completion and choose this schema metadata again in the [Repository Content] window. This component offers the advantage of the dynamic schema feature. This allows you to retrieve unknown columns from source files or to copy batches of columns from a source without mapping each column individually. For further information about dynamic schemas, see Talend Studio User Guide. This dynamic schema feature is designed for the purpose of retrieving unknown columns of a table and is recommended to be used for this purpose only; it is not recommended for the use of creating tables.
	Built-In: You create and store the schema locally for this component only. Related topic: see Talend Studio User Guide.
	Repository: You have already created the schema and stored it in the Repository. You can reuse it in various projects and Job designs. Related topic: see Talend Studio User Guide.
Table name	Database table name.
Query type and Query	Enter your DB query paying particularly attention to properly sequence the fields in order to match the schema definition. If using the dynamic schema feature, the SELECT query must include the * wildcard, to retrieve all of the columns from the table selected.
Specify a data source alias	Select this check box and specify the alias of a data source created on the Talend Runtime side to use the shared connection pool defined in the data source configuration. This option works only when you deploy and run your Job in Talend Runtime . For a related use case, see Scenario: Deploying your Job on Talend Runtime to retrieve data from a MySQL database. If you use the component’s own DB configuration, your data source connection will be closed at the end of the component. To prevent this from happening, use a shared DB connection with the data source alias specified. This check box is not available when the Use an existing connection check box is selected.

Advanced settings

Additional JDBC parameters	Specify additional connection properties for the database connection you are creating. The properties are separated by semicolon and each property is a key-value pair, for example, encryption=1;clientname=Talend. This field is not available if the Use an existing connection check box is selected.
tStatCatcher Statistics	Select this check box to collect log data at the component level.
Use cursor	Select this check box and in the Cursor size field displayed, specify the number of rows to fetch in one go from the database. The performance can be improved by tuning this fetch size to an appropriate value.
Trim all the String/Char columns	Select this check box to remove leading and trailing whitespace from all the String/Char columns.
Trim column	Remove leading and trailing whitespace from defined columns.
No null values	Check this box to improve the performance if there are no null values.

Global Variables

Global Variables	NB_LINE: the number of rows processed. This is an After variable and it returns an integer. QUERY: the query statement being processed. This is a Flow variable and it returns a string. ERROR_MESSAGE: the error message generated by the component when an error occurs. This is an After variable and it returns a string. This variable functions only if the Die on error check box is cleared, if the component has this check box. A Flow variable functions during the execution of a component while an After variable functions after the execution of the component. To fill up a field or expression with a variable, press Ctrl + Space to access the variable list and choose the variable to use from it. For further information about variables, see Talend Studio User Guide.

NB_LINE: the number of rows processed. This is an After
variable and it returns an integer.

QUERY: the query statement being processed. This is a Flow
variable and it returns a string.

ERROR_MESSAGE: the error message generated by the
component when an error occurs. This is an After variable and it returns a string. This
variable functions only if the Die on error check box is
cleared, if the component has this check box.

A Flow variable functions during the execution of a component while an After variable
functions after the execution of the component.

To fill up a field or expression with a variable, press Ctrl +
Space to access the variable list and choose the variable to use from it.

For further information about variables, see
Talend Studio

User Guide.

Usage

Usage rule	This component covers all possible SQL queries for Oracle databases.
Dynamic settings	Click the [+] button to add a row in the table and fill the Code field with a context variable to choose your database connection dynamically from multiple connections planned in your Job. This feature is useful when you need to access database tables having the same data structure but in different databases, especially when you are working in an environment where you cannot change your Job settings, for example, when your Job has to be deployed and executed independent of Talend Studio . The Dynamic settings table is available only when the Use an existing connection check box is selected in the Basic settings view. Once a dynamic parameter is defined, the Component List box in the Basic settings view becomes unusable. For examples on using dynamic parameters, see Scenario: Reading data from databases through context-based dynamic connections and Scenario: Reading data from different MySQL databases using dynamically loaded connection parameters. For more information on Dynamic settings and context variables, see Talend Studio User Guide.
Limitation	Due to license incompatibility, one or more JARs required to use this component are not provided. You can install the missing JARs for this particular component by clicking the Install button on the Component tab view. You can also find out and add all missing JARs easily on the Modules tab in the Integration perspective of your studio. You can find more details about how to install external modules in Talend Help Center (https://help.talend.com).

Usage rule

This component covers all possible SQL queries for Oracle
databases.

Dynamic settings

Click the [+] button to add a
row in the table and fill the Code field
with a context variable to choose your database connection dynamically from
multiple connections planned in your Job. This feature is useful when you
need to access database tables having the same data structure but in
different databases, especially when you are working in an environment where
you cannot change your Job settings, for example, when your Job has to be
deployed and executed independent of
Talend Studio
.

The Dynamic settings table is
available only when the Use an existing
connection check box is selected in the Basic settings view. Once a dynamic parameter is
defined, the Component List box in the
Basic settings view becomes unusable.

For examples on using dynamic parameters, see Scenario: Reading data from databases through context-based dynamic connections and Scenario: Reading data from different MySQL databases using dynamically loaded connection parameters. For more information on Dynamic
settings and context variables, see
Talend Studio User Guide.

Limitation

Due to license incompatibility, one or more JARs required to use this component are not
provided. You can install the missing JARs for this particular component by clicking the
Install button on the Component tab view. You can also find out and add all missing JARs easily on the
Modules tab in the
Integration
perspective of your
studio. You can find more details about how to install external modules in Talend Help Center (https://help.talend.com).

Scenario 1: Using context parameters when reading a table from an Oracle database

In this scenario, we will read a table from an Oracle database using a context parameter to
refer to the table name.

Dropping and linking the components

Create a new Job and add the following components by typing their names in the design
workspace or dropping them from the Palette: a tOracleInput
component and a tLogRow component.
Connect tOracleInput to tLogRow using a Row > Main
link.

Configuring the components

Double-click tOracleInput to open its
Basic Settings view in the Component tab.
Select a connection type from the Connection
Type drop-down list. In this example, it is Oracle SID.

Select the version of the Oracle database to be used from the DB Version drop-down list. In this example, it is
Oracle 12-7.

In the Host field, enter the Oracle
database server’s IP address. In this example, it is 192.168.31.32.

In the Database field, enter the database
name. In this example, it is TALEND.

In the Oracle schema field, enter the
Oracle schema name. In this example, it is TALEND.

In the Username and Password fields, enter the authentication details.
Click the […] button next to Edit schema to open the schema editor.
Click the [+] button to add four
columns: ID and AGE of the integer type, NAME and SEX of the
string type.

Click OK to close the schema editor and
accept the propagation prompted by the pop-up dialog box.
Put the cursor in the Table Name field
and press F5 for context parameter setting.
The dialog box [New Context Parameter] pops
up.

For more information about context settings, see
Talend Studio User Guide.
In the Name field, enter the context
parameter name. In this example, it is TABLE.

In the Default value field, enter the
name of the Oracle database table to be queried. In this example, it is
PERSON.
Click Finish to validate the setting.

The context parameter context.TABLE
automatically appears in the Table Name
field.
In the Query Type list, select Built-In. Then, click Guess
Query to get the query statement.

"SELECT "+context.TABLE+"."ID", "+context.TABLE+".NAME, "+context.TABLE+".SEX, "+context.TABLE+".AGE FROM "+context.TABLE

1
2
3
4
5
6

"SELECT
  "+context.TABLE+"."ID",
  "+context.TABLE+".NAME,
  "+context.TABLE+".SEX,
  "+context.TABLE+".AGE
FROM "+context.TABLE
Double-click tLogRow to open its
Basic settings view in the Component tab.
In the Mode area, select Table (print values in cells of a table) for a
better display of the results.

Saving and executing the Job

Press Ctrl + S to save the Job.
Press F6 to run the Job.

As shown above, the data in the Oracle database table PERSON is displayed on the console.

Related scenarios

For related scenarios, see: