RETURN_DATA_TYPE Statement—Specify JDBC Data Type for a Return Value

C-LANG Statements › RETURN_DATA_TYPE Statement—Specify JDBC Data Type for a Return Value

RETURN_DATA_TYPE Statement—Specify JDBC Data Type for a Return Value

The RETURN_DATA_TYPE statement specifies the JDBC data type for the value returned by a stored function.

Supported Job Type

This statement is optional for the Database Stored Procedure job type.

Syntax

This statement has the following format:

RETURN_DATA_TYPE return_type

return_type

Specifies the data type to be returned from the stored procedure.

The following table lists the supported data types for different databases. The data types listed in the database columns are defined in the procedure definition within the database. In the job definition, use the corresponding JDBC data type from the first column.

JDBC Data Type	Valid Input Format	Oracle	Microsoft SQL Server	DB2
CHAR	Plain text	CHAR	CHAR	CHAR
VARCHAR	Plain text	VARCHAR2(x) VARCHAR(x)	VARCHAR(x)	VARCHAR(x)
LONGVARCHAR		Not supported	Not supported	Not supported
NUMERIC	Number	NUMBER, NUMERIC	NUMERIC	NUMERIC
DECIMAL	Number	DECIMAL	DECIMAL	DECIMAL
BIT		Not supported	BIT	Not supported
BOOLEAN		Not supported	Not supported	Not supported
TINYINT		Not supported	TINYINT	Not supported
SMALLINT	Number	SMALLINT	SMALLINT	SMALLINT
INTEGER	Number	INTEGER	INTEGER	INTEGER
BIGINT	Number	Not supported	BIGINT	BIGINT
REAL	Number [dot Number]	REAL	REAL	REAL
FLOAT	Number [dot Number]	FLOAT	FLOAT	FLOAT
DOUBLE				DOUBLE
DATE	yyyy-mm-dd	DATE		DATE
DATE	String (for example, SYSDATE)
TIME	hh:mm:ss			TIME
TIMESTAMP	yyyy-mm-dd hh:mm:ss.fffffffff	TIMESTAMP		TIMESTAMP

Notes:

You can run stored functions on Oracle or SQL Server using Stored Procedure jobs. The agent does not support stored functions (also named user-defined functions) on DB2.
RETURN is an alias of RETURN_DATA_TYPE.

Example: Run Stored Function that Returns Type DECIMAL

In the following example, the stored function COUNT returns type DECIMAL.

AGENT CYBDB1
STORED_PROCEDURE COUNT
RETURN_DATA_TYPE DECIMAL