How to Execute a Stored Procedure?

Execute or Exec is a SQL*Plus command that execute the package or a stored procedures. To demo the execute or exec command, we will first create a simple procedure, compile it and then execute it. You can either use a SQL command or PL/SQL block to execute the named block also called the stored procedures or packages.

Pre-requisite:  The user needs a privilege to create and execute procedures before beginning the exercise.

--Stored procedure my_stored_proc

SET serveroutput ON;

CREATE OR replace PROCEDURE scott.My_stored_proc

IS
BEGIN
  dbms_output.Put_line ('HELLO');
END;

/

The procedure my_stored_proc is compiled successfully. There are no syntax error and this procedure is ready to compiled. We will compiled the procedure below using all the options that I know.

Execute:

–Using SQL
--Execute
EXECUTE scott.my_stored_proc;
---Exec

EXEC scott.my_stored_proc;

PL/SQL

–-USING PL/SQL
BEGIN
  scott.my_stored_proc;

END;

/