You want to execute a stored procedure from SQL*Plus.
Open SQL*Plus, and connect to the database schema that contains the procedure you are interested in executing. Execute the procedure by issuing the following command:
EXEC procedure_name([param1, param2,...]);
For instance, to execute the procedure that was created in this blog, you would issue the following command:
EXEC increase_wage(198, .03, 5000);
This would invoke the INCREASE_WAGE procedure, passing three parameters: EMPLOYEE_ID, a percentage of increase, and an upper salary bound.
You can also execute a stored procedure by creating a simple anonymous block that contains the procedure call, as depicted in the following code:
BEGIN procedure_name([param1, param2,…]); END;
Using this technique, invoking the stored procedure would resemble the following:
BEGIN increase_wage(198,.03,5000); END;
Both techniques work equally well, but the latter would be better to use if you wanted to execute more than one procedure or follow up with more PL/SQL statements. If you are running a single procedure from SQL*Plus, then using
EXEC is certainly a good choice.
How It Works
A stored procedure can be executed using the
EXEC keyword. You can also type
EXECUTE entirely. Both the long and shortened versions will work.
It is also possible to execute a procedure that is contained within other schemas, if the current user has execute privileges on that procedure. In such a scenario, use dot notation to qualify the procedure name. Here's an example:
EXEC different_schema.increase_wage(emp_rec.employee_id, pct_increase, upper_bound);
A procedure can also be invoked from within another procedure by simply typing the name and placing the parameters inside parentheses, if there are any. For instance, this blog.
CREATE OR REPLACE PROCEDURE grant_raises (pct_increase IN NUMBER, upper_bound IN NUMBER) AS CURSOR emp_cur is SELECT employee_id, first_name, last_name FROM employees; BEGIN -- loop through each record in the employees table FOR emp_rec IN emp_cur LOOP DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name); increase_wage(emp_rec.employee_id, pct_increase, upper_bound); END LOOP; END;
The procedure GRANT_RAISES applies an increase across the board to all employees. It loops through all employee records, and the INCREASE_WAGE procedure is called with each iteration. The procedure is called without the use of the
EXEC keyword since it is being invoked by another procedure rather than directly from the SQL*Plus command line.