Calling Database Stored Procedures in Scheduled Tasks- FineDataLink Help Document

Last update: June 06, 2025

Overview

Version

FineDataLink Version	Functional Change
4.1.3	Allowed calling stored procedures if the data source you set in a scheduled task was a MySQL, Oracle, or SQL Server database.
4.1.11.2	Allowed calling void-returning stored procedures from HP Vertica and GaussDB 200 data sources using the SQL Script node. Allowed using a single query result set returned by a GaussDB 200 stored procedure as an input table in the Data Synchronization/Data Transformation/Parameter Assignment node. Returning cursor result sets was not supported by GaussDB 200.
4.1.12.2	Allowed calling void-returning stored procedures from IBM Db2 and SAP HANA data sources using the SQL Script node. Allowed returning query result sets through cursors for IBM Db2 stored procedures in Data Synchronization, Data Transformation, and Parameter Assignment. Allowed using a single query result set returned by an SAP HANA stored procedure as an input table in Data Synchronization/Data Transformation/Parameter Assignment. Returning cursor result sets was not supported by SAP HANA.
4.2.4.1	Allowed previewing result set data if Return Result Set was set to Query Result Set in Data Synchronization, DB Table Input in Data Transformation, and Parameter Assignment.

Application Scenario

You can use this function to call stored procedures to process data in the database with no value returned.
You can use this function to synchronize the returned result set as the input table to other databases when the called stored procedure contains a SELECT statement that returns a result set.
You can use this function to use the single returned query result set from a stored procedure as the input table.

Function Description

You can call database stored procedures in scheduled tasks, as shown in the following figure.

1.3.png

Usage Restriction

Supported Databases

FineDataLink Version	Database	Cursor Result Set	Query Result Set
4.1.3	MySQL	Not supported	Supported
	Oracle	Supported	Not supported
	SQL Server	Not supported	Supported
4.1.11.2	HP Vertica	Not supported	Not supported
4.1.11.2	GaussDB 200	Not supported	Supported (a single query result set)
4.1.12.2	IBM Db2	Not supported	Supported
4.1.12.2	SAP HANA	Not supported	Supported (a single query result set)

Note:

Calling SAP HANA stored procedures requires a specific driver. For details, see SAP HANA Data Connection.

Permission Description

The user account used to configure the data connection must have permission to call getProcedureColumns and Call methods.

Note:

The user account used to configure the data connection must have query and execution permission on the stored procedures to be called. The configuration method may vary across databases. For details, you can search online.

Nodes/Operators Supporting the Calling of Stored Procedures

The following table describes the nodes/operators supporting the calling of stored procedures.

Nodes/Operators Supporting the Calling of Stored Procedures	Description
SQL Script	You can call void-returning stored procedures.
Data Synchronization	You can use a result set returned by a stored procedure (excluding those in HP Vertica, DB2, and SAP HANA) as the input table).
DB Table Input in Data Transformation
Parameter Assignment

SQL Script

You can call void-returning stored procedures using the SQL Script node.

3.1.png

The following table describes the setting items.

Setting Item

Description

Configuration Method

Select Stored Procedure. The following configuration items will be displayed.

Stored Procedure

Select an existing void-returning stored procedure.

Parameter Configuration

Parameter Type/Parameter Name: They are automatically obtained.

Parameter Value: The system automatically fills in the default values of the stored procedure parameters. You can change the values and reference parameters. You can input the symbol $ for associative parameter selection/handwriting, and use existing parameters as input/output parameters. You can also select Null Value.

You can use ${Parameter name} to reference parameters. For details, see Parameter Configuration and Use.

Note:

1. IBM Db2 supports IN, OUT, and INOUT parameters. OUT parameters can be used to return output values or status values.

2. INOUT parameters in SAP HANA only support scalar values and need to be parameterized. Non-parameterized calling of stored procedures with INOUT parameters is not supported.

Note:

The order of parameters should correspond to that of the input and output parameters of the stored procedure. The order in which parameters are passed into the stored procedure is from top to bottom.
The parameter direction should be consistent with that of the parameters in the database stored procedure.

Data Synchronization/Data Transformation/Parameter Assignment

1. In the Data Synchronization node and the DB Table Input operator in the Data Transformation node, returning result sets from stored procedures as input tables is supported, as shown in the following figure.

3.2-1.png

2. In the Parameter Assignment node, the result sets returned by stored procedures can be set as parameters, as shown in the following figure.

3.2-2.png

The following table describes the setting items.

Setting Item	Description
Configuration Method	Select Stored Procedure. The following configuration items will be displayed.
Stored Procedure	Select an existing stored procedure that will return a result set.
Returned Result Set/Result Set Selection	1. After selecting a stored procedure in Stored Procedure, the result set type is automatically displayed in Returned Result Set as Query Result Set or Cursor Result Set. 2. For FineDataLink of V4.2.4.1 and later versions, when the result set type is Query Result Set, you can click Select in Result Set Selection to preview the result set data and select the result set. Note: If previewing a result set fails, you cannot select the result set.
Parameter Configuration	Note: 1. Only input parameters are displayed in the parameter list. Output parameters are not displayed currently. 2. Oracle and SQL Server stored procedures can return result sets through OUT parameters, which is of the cursor type. Cursor-type parameters are not displayed in the parameter list. 3. You can preview all types of parameters in GaussDB 200 stored procedures. However, during the configuration of Field Mapping in Data Destination and Mapping, only IN and INOUT parameters can be mapped. 4. IBM Db2 supports IN, OUT, and INOUT parameters. OUT parameters can be used to return output values or status values. 5. You need to use the DECLARE CURSOR WITH RETURN statement to declare cursors for IBM Db2 stored procedures, through which a single result set, multiple result sets, or a restricted number of result sets can be returned. IBM Db2 supports nested calling between stored procedures, and the called stored procedures can return parameters or result sets. 6. For SAP HANA, you can use cursors to call stored procedures to process and determine output data and read read-only data. Parameter Type/Parameter Name: They are automatically obtained. Parameter Value: The system automatically fills in the default values of the stored procedure parameters. You can change the values and reference parameters. You can input the symbol $ for associative parameter selection/handwriting, and use existing parameters as input/output parameters. You can also select Null Value. You can use ${Parameter name} to reference parameters. For details, see Parameter Configuration and Use. Note: The order of parameters should correspond to that of the input and output parameters of the stored procedure. The order in which parameters are passed into the stored procedure is from top to bottom. The parameter direction should be consistent with that of the parameters in the database stored procedure.