Executing a PL/SQL Stored Procedure from SQL*Plus

PL/SQL Stored Procedure Executing from SQL*PlusProblem

You want to execute a stored procedure from SQL*Plus.

 

Solution

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.

Вас заинтересует / Intresting for you:

Describe the Basic PL/SQL Prog...
Describe the Basic PL/SQL Prog... 375 views Андрей Волков Wed, 12 Sep 2018, 15:23:39
PL/SQL package: Collecting Rel...
PL/SQL package: Collecting Rel... 145 views sepia Sat, 01 Dec 2018, 10:54:57
Why Oracle DBAs learn PL/SQL a...
Why Oracle DBAs learn PL/SQL a... 372 views Андрей Волков Wed, 12 Sep 2018, 14:43:12
Translating PL/SQL to JavaScri...
Translating PL/SQL to JavaScri... 378 views Гвен Sun, 03 Jun 2018, 11:54:40

Comments on Executing a PL/SQL Stored Procedure from SQL*Plus

Be the first to comment
Please login to comment