tOracleInput

tOracleInput properties

Component family	Databases/Oracle
Function	tOracleInput reads a database and extracts fields based on a query.
Purpose	tOracleInput executes a DB 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.
Basic settings	Property type	Either Built-in or Repository. Since version 5.6, both the Built-In mode and the Repository mode are available in any of the Talend solutions.
		Built-in: No property data stored centrally.
		Repository: Select the repository file in which the properties are stored. The fields that follow are completed automatically using the data retrieved.
		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 to be processed and passed on to the next component. The schema is either Built-in or stored remotely in the Repository. Since version 5.6, both the Built-In mode and the Repository mode are available in any of the Talend solutions. 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. Warning 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. Warning 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	tStatCatcher Statistics	Select this check box to collect log data at the component level.
	Use cursor	When selected, helps to decide the row set to work with at a time and thus optimize performance.
	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.
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 more information on Dynamic settings and context variables, see Talend Studio User Guide.
Global Variables	NB_LINE: the number of rows processed. This is an After variable and it returns an integer. QUERY: the SQL 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	This component covers all possible SQL queries for Oracle databases.
Log4j	The activity of this component can be logged using the log4j feature. For more information on this feature, see Talend Studio User Guide. For more information on the log4j logging levels, see the Apache documentation at http://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/Level.html.
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. For details, see https://help.talend.com/display/KB/How+to+install+external+modules+in+the+Talend+products or the section describing how to configure the Studio in the Talend Installation and Upgrade Guide.

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.

Save 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:

Document get from Talend https://help.talend.com

Thank you for watching.

Docs 5.x

0 Comments

Inline Feedbacks

View all comments

tOracleInput – Docs for ESB 5.x

tOracleInput

tOracleInput properties

Note

Warning