Configuring the Oracle connector as a source for reading data

To configure the connector to read rows in an Oracle table or view, you must specify the source table or view or define a complete SELECT statement or PL/SQL block.

About this task

If you specify a SELECT statement, the connector runs the statement only once and sends all of the rows that are returned for that statement to the output link.

If you specify a PL/SQL block, the connector runs the PL/SQL block only once and returns the output bind variables that are specified in the block. A single record is sent to the output link. A PL/SQL block is useful for running a stored procedure that takes no input parameters but that returns values through one or more output parameters.

Procedure

From the job design canvas, double-click the Oracle Connector stage.
Select the output link to edit. When you edit the output link, you set up the Oracle Connector stage to be the source.
Set Read mode to Select or PL/SQL.
If you set Read mode to Select, use one of these methods to specify the source of the data:
- Set Generate SQL at runtime to Yes, and then enter the name of the table or view in the Table name property. Use the syntax schema_name.table_name, where schema_name is the owner of the table. If you do not specify schema_name, the connector uses the schema that belongs to the user who is currently connected.
- Set Generate SQL at runtime to No, and then specify the SELECT statement in the Select statement property.
- Set Generate SQL at runtime to No, and then enter the fully qualified file name of the file that contains the SQL statement in the Select statement property. If you enter a file name, you must also set Read select statement from file to Yes.
- Click the Select statement property, and then next to the property, click Build to start the SQL Builder. To construct the SQL statement, drag table and column definitions that are stored in the repository and choose options for configuring clauses in the SQL statement.
If you set Read mode to PL/SQL, use one of these methods to specify the source of the data:
- Enter the PL/SQL block manually in the PL/SQL block property.
- Enter the fully qualified file name of the file that contains the PL/SQL block in the PL/SQL block property. If you enter a file name, you must also set Read PL/SQL block from file to Yes.
The PL/SQL block that you specify must begin with the keyword DECLARE or BEGIN and must end with the keyword END, and you must enter a semicolon after the END keyword.
Click OK, and then save the job.