IBM PureData System for Analytics, Version 7.1

The CALL and EXEC[UTE [PROCEDURE]] commands

Use the CALL command, EXEC command, EXECUTE command, or EXECUTE PROCEDURE command to invoke a stored procedure on a IBM® Netezza® host.

The CALL, EXEC, EXECUTE, EXECUTE PROCEDURE commands are identical in their behavior, inputs, and outputs. The different commands provide compatibility with other procedural language invocation methods. You can also use the SELECT command to invoke a stored procedure.

Synopsis

CALL procedure_name(arguments)
EXEC procedure_name(arguments)
EXECUTE procedure_name(arguments)
EXECUTE PROCEDURE procedure_name(arguments)
SELECT procedure_name(arguments)

Inputs

The CALL and EXEC[UTE [PROCEDURE] commands take the following inputs:

Table 1. CALL and EXEC[UTE [PROCEDURE]] inputs
Input	Description
procedure_name	The name of the stored procedure that you want to invoke. If you specify only a procedure name, the system searches the current database and schema, and then search PATH to find the procedure. You can specify a fully qualified object name to execute a procedure defined in a different database or schema.
arguments	Specifies a list of constant or literal arguments to the procedure. The arguments might be results of functions when the functions take only constant or literal arguments as well.

Outputs

The CALL and EXEC[UTE [PROCEDURE] commands have the following outputs:

Table 2. CALL and EXEC[UTE [PROCEDURE]] outputs
Output	Description
ERROR: EXECUTE PROC: Permission denied.	This error indicates that the current user account does not have Execute permission for the stored procedure.
ERROR: Function '`NAME`(`ARGS`)' does not exist Unable to identify a function that satisfies the argument types You might need to add explicit typecasts	This message indicates that the user entered incorrect arguments for the stored procedure. A procedure of that name exists, but it is expecting different input arguments.

Description

Privileges required

To invoke a stored procedure, you must meet one of the following criteria:

You must have the Execute privilege on the PROCEDURE object.
You must have the Execute privilege on the specific procedure.
You must own the procedure.
You must be the database admin user or own the current database or the current schema on systems that support multiple schemas.

Usage

The following examples provide some sample usage:

MYDB.MYSCH(USER)=> CALL updateacct();
MYDB.MYSCH(USER)=> EXEC schema_two.myproc();
MYDB.MYSCH(USER)=> EXECUTE sales.dev.inventorysearch(umbrellas);
MYDB.MYSCH(USER)=> EXECUTE PROCEDURE updateacct();

You can also use the SELECT command to run a procedure; however, you cannot specify a FROM clause. For example:

MYDB.MYSCH(USER)=> SELECT updateacct();
MYDB.MYSCH(USER)=> SELECT inventorysearch(umbrellas);