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.

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.

PL/SQL-statement

Specifies the PL/SQL statement to run.

variable-definition

Specifies the definition of a variable.

argument

parameter-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.

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' ;