Calling a stored procedure or function

author
Shicong Hong
EnrichVersion
6.4
6.3
6.2
6.1
EnrichProdName
Talend Data Services Platform
Talend Real-Time Big Data Platform
Talend MDM Platform
Talend ESB
Talend Data Fabric
task
Design and Development > Third-party systems > Database components
Data Governance > Third-party systems > Database components
Data Quality and Preparation > Third-party systems > Database components
EnrichPlatform
Talend Studio

Calling a stored procedure or function

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

This procedure was written with:

  • 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 verified this procedure to be compatible with:

  • 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
ProcedureCalling 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]
Calling a function with input parameters

The following is a function with input parameters.

The function requires user_id as an input parameter and selects the corresponding name from a table called person .

create or replace function getName (user_id in int) return varchar2is
v_name  varchar2(11);
begin
   select name into v_name from person where id= user_id;
   return (v_name);
end;

Here are the records of the person table used in this example:

id;name
1;Shong
2;Elise
3;Dave
4;Mike
5;Pedro

Use a tOracleSP component to call the function. Use a tFixedFlowInput component to generate a fixed data flow. This component passes the value of the input parameter to the function. Use a tLogRow component to output the results to the console.

  • In the tOracleSP component, add another column called Name to the schema. This column is used to store the return value.
  • Enter the function name in the SP Name field. This example, it is getName.
  • Check the Is function box, then select the Name column from the Return result in list (for storing the return value).
  • In the Parameters table add one row and select user_id in the Schema_Column column. Select IN in the Type column as an input parameter.

If the input parameter values come from a file, database, or other data sources, you must use a corresponding input component, such as tFileInputDelimited or tMysqlInput instead of tFixedFlowInput.

Execute the job. The following text is output to the console:

Starting job SPDemo at 21:03 09/07/2012. 
[statistics] connecting to socket on port 3829
[statistics] connected
.-------+----.
| tLogRow_1  |
|=------+---=|
|user_id|name|
|=------+---=|
|3      |Dave|
'-------+----'
 
[statistics] disconnected
Job SPDemo ended at 21:03 09/07/2012. [exit code=0]
Calling a stored procedure without parameters

The following is a stored procedure with no parameters:

create or replace procedure p1is
begin
update person set name='Shong1' where id=1;
end;

Use tOracleInput_1 to query the name and output it to the console with tJavaRow_1. Then use tOracleSP_1 to call the stored procedure and update the table. Use tOracleInput_2 to query the name and print it to the console with tJavaRow_2, after updating the table.

  • Enter the stored procedure name in the SP Name field of the tOracleSP_1. In this example the name is p1.
  • You do not need to define the schema of tOracleSP_1, as there are no parameters in this stored procedure.

Execute the job. The following text is output to the console.

Starting job SPDemo at 13:38 10/07/2012.
 
[statistics] connecting to socket on port 3608
[statistics] connected
Before update: Shong
After update: Shong1
[statistics] disconnected
Job SPDemo ended at 13:38 10/07/2012. [exit code=0]

This example shows that calling a stored procedure without parameters in Talend Studio is a fairly simple process. You need to use a txxxSP component such tOracleSP, configure the database connection parameters, and enter the stored procedure name in the SP Name field.

Calling a stored procedure with parameters

The following is a stored procedure with an input parameter and an output parameter:

create or replace procedure p2 (user_id in integer, user_name out varchar2)is
begin
select name into user_name from person where id= user_id;
end;

Use a tOracleSP component to call the stored procedure. Use a tFixedFlowInput component to generate a fixed data flow (it passes the values of input parameters to the stored procedure). Use a tLogRow component to output the results to the console.

  • Add a column called Name to the schema of the tOracleSP component. The column is used to store the return value.
  • Enter the stored procedure name in the SP Name field. In this example the name is p2.
  • In the Parameters table add a row and select user_id in Schema_Column column. Select IN in the Type column as input parameter. Add a second row and select Name in Schema_Column column, select Out in Type column as output parameter.

Execute the job. The following text is output to the console :

Starting job ForBo at 18:17 10/07/2012.
 
[statistics] connecting to socket on port 3933
[statistics] connected
4|Mike
[statistics] disconnected
Job ForBo ended at 18:17 10/07/2012. [exit code=0]