Overview
Components such as tOracleSP or tMysqlSP are used to call database functions and stored procedures. These components are frequently used in ETL jobs.
This article uses simple examples to illustrate how to use tOracleSP to call an Oracle function and an Oracle stored procedure. The following examples are included:
-
Calling a function without input parameters
-
Calling a function with input parameters
-
Calling a stored procedure without parameters
-
Calling a stored procedure with parameters
This article applies to tOracleSP, tMysqlSP, tMSSQLSP, and all such txxxSP components.
Environment
- Talend Open Studio for Data Integration 5.0.0
- Sun JDK build 1.6.0_26-b03
- Windows XP SP3
- Oracle Database 10g Enterprise Edition Release 10.1.0.2
- Talend Open Studio for Data Integration releases: 4.2.3, 4.2.4, 5.0.0, 5.0.1, 5.0.2, 5.1.1
Procedure
Calling a function without input parameters
The following is a function with no input parameters:
|
create or replace function f1 return Date is
cDate Date;
begin
select sysdate into cDate from dual;
return (cDate);
end;
|
Use a tOracleSP component to call the function, and use a tLogRow component (with table model) to print the return value on the console, as follows:
-
On tOracleSP, add a column called currentDate on the schema, this column is used to store the return value.
- Enter the function name in the SP Name field. In this example, it is f1.
-
Check the Is function box, then select the currentDate column (defined on the schema of component) from Return result in list for storing the return value.
Execute the job. The following text is output to the console:
|
Starting job SPDemo at 16:39 09/07/2012.
[statistics] connecting to socket on port 3595
[statistics] connected
.-----------.
| tLogRow_1 |
|=---------=|
|currentDate|
|=---------=|
|09-07-2012 |
'-----------'
[statistics] disconnected
Job SPDemo ended at 16:39 09/07/2012. [exit code=0]
|
