Create Procedure   «Prev  Next»

Lesson 4 Procedures that call procedures or functions
ObjectiveCapabilities and restrictions for calling other procedures

Procedures that call Procedures or Functions

Question: Can you call a procedure in another stored procedure?
List the capabilities and restrictions for calling other procedures. You learned to create a procedure in the previous lesson. This lesson shows you how to combine the procedure and the function. You can use a function within a procedure or a procedure within a function. If you are using a function in a procedure, remember that if the function appears in a query or some other SQL command, the same restrictions apply to the function as if it were called in plain SQL. In other words, you cannot use a function that returns a Boolean value in a query, even if the query appears inside a procedure. The following table shows the capabilities and restrictions for calling functions and procedures. The two right columns show the capabilities and restrictions of the combination listed in the first column.

Function Procuedure Call Restrictions

In Oracle PL/SQL, you can call a function within a procedure without any major restrictions, as long as the function is deterministic and its execution does not cause any side effects that would violate the context in which it is being called. This means the function should return the same output for a given set of input parameters and should not modify the database state.
On the other hand, calling a procedure within a function is subject to certain restrictions due to the fundamental differences between procedures and functions in PL/SQL. Functions are primarily intended for returning a value, whereas procedures are designed to perform a series of actions, possibly modifying the database state. As such, if a procedure has any side effects, such as modifying the database state, it might not be allowed in contexts where functions are expected to be pure (e.g., in a SELECT statement).
In summary, while you can call a function within a procedure in Oracle PL/SQL without any major restrictions, calling a procedure within a function is subject to certain restrictions, particularly if the procedure has side effects that could violate the context in which it is being called.


 CapabilityRestriction
Procedure calls functionA procedure can call any function.If function returns a Boolean, you cannot use it in an SQL command inside the procedure.
Procedure calls a procedureA procedure can call any procedure.If the called procedure updates data that is also modified by the calling procedure, a deadlock can occur in which one procedure's action prevents the other procedure from successfully completing.
Function calls a function A function can call any function. None.
Function calls a procedure A function can call a procedure only if the procedure does not modify data. When a function calls a procedure, the procedure must not modify database data.

Sub-programs

It is important to plan the connections between your sub-programs so that you do not receive unexpected errors caused by a restricted sub-program calling a sub-program that is unrestricted.
The next lesson teaches you to add a function to a procedure.

Procedure Calling Functions

The following PL/SQL is an example of a procedure calling functions. 
SQL> set serveroutput on
SQL> declare
  2   procedure p1 (
  3    i1 in out number
  4   )
  5   as
  6   begin
  7    i1 := 200;
  8   end p1;
  9   function f1 
 10   return number
 11   is
 12    l1 number;
 13   begin
 14    p1(l1);
 15    return l1;
 16   end f1;
 17  begin
 18   dbms_output.put_line(f1);
 19  end;
 20  /
200

PL/SQL procedure successfully completed.

SQL> set serveroutput off

SEMrush Software