EXECUTE CLPPlus command

The EXECUTE CPPPlus command runs a procedure in the currently connected database. It is also used to define variables and run single-line PL/SQL statements. For a Db2® database connection, when you run this command, a Db2CALL statement is issued.

Invocation

You must run this command from the CLPPlus interface.

Authorization

You must ensure that the user ID that is used to run the EXECUTE CPPPlus command has one of the following privileges:

EXECUTE privilege on the procedure
DATAACCESS authority

If a matching procedure exists that the authorization ID of the statement is not authorized to run, an error is returned (SQLSTATE 42501).

Required connection

You must be connected to a database.

Command syntax

argument

Command parameters

procedure-name

Specifies the procedure to call. The procedure must be cataloged. For Db2 data servers, you can qualify the name with a schema name, a module name, or both a schema name and a module name. The procedure to run is chosen by the procedure resolution algorithm. For Db2databases, the execution of the EXECUTE command, including procedure resolution, is the same as the Db2CALL statement.

PL/SQL-statement

Specifies the PL/SQL statement to run.

variable-definition

Specifies the definition of a variable.

argument

parameter-name

For Db2 data servers only, specifies the name of the parameter to which a value is assigned. If you assign a value to a parameter by name, all subsequent value assignments must also be by name.

All named parameters that you use to run the procedure must exist in the procedure definition.

Parameter names must be unique.

You cannot use named parameters to run uncataloged procedures.

value

Specifies the value that is associated with a parameter. The nth unnamed value corresponds to the nth parameter defined in the CREATE PROCEDURE statement for the procedure. Named values correspond to the same named parameter, regardless of the order in which you specify them.

expression

Passes a user-specified list of parameter values to the procedure that is called.

NULL

Passes NULL as the parameter value.

DEFAULT

If a default value is defined in the CREATE PROCEDURE statement, the specified default is passed as the parameter value. If no default value is specified, the NULL value is passed as the parameter value.

For Db2 databases, you must specify a value for each parameter that is not defined to have a default value (SQLSTATE 428HF). Also, for Db2 databases, each value must be compatible with the corresponding parameter in the procedure definition, as follows:

IN parameter
- The value must be assignable to the parameter.
- The assignment of a string argument uses the storage assignment rules.
OUT parameter
- The value must be a single variable or parameter marker (SQLSTATE 42886).
- The value must be assignable to the parameter.
- The assignment of a string value uses the retrieval assignment rules.
Note: You cannot display the following output data type values in the CLPPlus interface: row, array, associative array, and Boolean.
INOUT parameter
- The value must be a single variable or parameter marker (SQLSTATE 42886).
- The value must be assignable to the parameter.
- The assignment of a string value uses the storage assignment rules on invocation and the retrieval assignment rules on return.

Examples

The CREATE PROCEDURE statement creates a procedure that is called save_tester_details_PROC. The EXECUTE command runs this procedure.

>   SQL> CREATE PROCEDURE save_tester_details_PROC
          (tno, IN integer, tname IN varchar, tadd IN varchar)
          AS
           BEGIN
            INSERT INTO tester1 VALUES
            (tno, tname, tadd);
           END;
    /

>   The SQL command completed successfully.

>   SQL> EXECUTE save_tester_details_PROC(1, 'John Smith', 'Address1');
>   DB250000I: The SQL command completed successfully.

The EXECUTE command spans multiple lines and the block terminator / is used to submit the command for processing. The block terminator / must be used at the end of a command, which spans multiple lines.
```
    SQL> exec dbms_output.put_line('test serveroutput') 
    2   /
    test serveroutput
    DB250000I: The command completed successfully.
```

The EXECUTE command runs a single PL/SQL statement.

    SQL> Exec BEGIN dbms_output.put_line('TEST EXEC'); END 
         2
         /
    DB250000I: The command completed successfully.

The EXECUTE command defines a variable.

    SQL> Variable bindvar varchar(20)
    SQL> Execute :bindvar := 'value' ;