Procedures that call Procedures or Functions in Oracle

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.

When working with Oracle PL/SQL, the capabilities and restrictions of calling procedures or functions in specific use cases depend on the context in which they are invoked. Here's a detailed explanation of the two scenarios you mentioned:
1) A Function Embedded Within a Procedure | Capabilities:

• Invocation: A function can be called from within a procedure. You can invoke the function either to assign its return value to a variable or directly use its value in expressions.
• Scope: The function can be local (declared within the procedure) or external (declared in the same or a different PL/SQL block, package, or schema).
• SQL Context: If the function is deterministic and does not perform actions that modify the database state, it can also be used in SQL statements within the procedure.
• Parameters: Functions can accept parameters, and these can be passed as literals, variables, or expressions.

Restrictions:

• Database Modifications: The function being called must not contain DML statements (e.g., INSERT, UPDATE, DELETE, or MERGE) if it is being used in SQL within the procedure.
• Autonomous Transactions: If the function executes as an autonomous transaction (using the PRAGMA AUTONOMOUS_TRANSACTION directive), its transaction scope is independent of the calling procedure, which may lead to complexities in maintaining transaction integrity.
• Side Effects: If the function causes side effects (e.g., modifying a global variable or performing I/O operations), it may impact the behavior of the procedure in ways that are not immediately apparent.

---

2) A Procedure Within a Function | Capabilities:

• Procedure Call: A function can call a procedure either directly or through intermediate PL/SQL blocks.
• Scope: Similar to the first case, the procedure being called can be local to the function or external.
• Reusable Logic: This allows encapsulating complex logic within procedures and invoking them from the function.

Restrictions:

• Database Modifications: If the function is being invoked in a SQL statement, the called procedure cannot perform DML operations. This is because a function used in SQL must be "deterministic," meaning its result depends only on its input parameters and not on any external state changes.
• Transaction Management: Procedures that include transaction control statements (COMMIT, ROLLBACK, SAVEPOINT) cannot be called from within a function. Oracle enforces this restriction to maintain the integrity of the SQL execution environment.
• Autonomous Transactions: Similar to functions, if the procedure operates as an autonomous transaction, its effects are independent of the function's transaction scope.
• Error Handling: If the procedure raises an exception, the function must handle it appropriately to avoid unexpected runtime errors.

Key Points for Both Use Cases

1. Transaction Scope:
   • Ensure that transaction consistency is maintained when calling procedures or functions that interact with the database.
   • A function invoked in a SQL statement cannot call a procedure that performs transactional operations.
2. Modularity and Maintainability:
   • Design procedures and functions to encapsulate specific, reusable logic.
   • Avoid embedding complex logic in functions that are used in SQL statements.
3. Performance Considerations:
   • Excessive calls to functions within procedures (or vice versa) may lead to performance overhead, especially in scenarios involving SQL execution.
4. Code Organization:
   • Use packages to group related procedures and functions. This improves code modularity, performance (due to reduced recompilation), and maintainability.

By adhering to these capabilities and restrictions, you can design robust and efficient PL/SQL programs that leverage the strengths of Oracle's procedural language while avoiding common pitfalls.

	Capability	Restriction
Procedure calls function	A procedure can call any function.	If function returns a Boolean, you cannot use it in an SQL command inside the procedure.
Procedure calls a procedure	A 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 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

The script demonstrates the use of a PL/SQL anonymous block that includes:

1. A procedure (p1) that modifies the value of an IN OUT parameter.
2. A function (f1) that calls the procedure and returns the modified value.
3. Anonymous PL/SQL execution block that calls the function and prints the result.

Step-by-Step Explanation

1. Setting serveroutput on

   SQL> set serveroutput on
       

   • Purpose: Enables output from the DBMS_OUTPUT.PUT_LINE command to be displayed in the SQL*Plus session.
2. Declaration Section

   declare
     procedure p1 (
       i1 in out number
     )
     as
     begin
       i1 := 200;
     end p1;
       

   • Purpose: The DECLARE section introduces a procedure p1 and a function f1.
   • Procedure p1:
     • Accepts an IN OUT parameter (i1) of type NUMBER.
     • Modifies the value of i1 and assigns it the value 200.
3. Function Definition

   function f1 
   return number
   is
     l1 number;
   begin
     p1(l1);
     return l1;
   end f1;
       

   • Purpose: Defines a function f1 that:
     • Declares a local variable l1 of type NUMBER.
     • Calls the procedure p1, passing l1 to it.
     • Key Behavior: Since l1 is uninitialized, it defaults to NULL. However, p1 assigns 200 to it.
     • Returns the modified value of l1 (now 200).
4. Execution Block

   begin
     dbms_output.put_line(f1);
   end;
       

   • Purpose: Calls the function f1, retrieves its result (200), and outputs it using the DBMS_OUTPUT.PUT_LINE procedure.
   • DBMS_OUTPUT.PUT_LINE is a built-in package for displaying text output in SQL*Plus.
5. Output

   200
   
   PL/SQL procedure successfully completed.
       

   • Result Explanation:
     • When the block executes:
     • The function f1 initializes l1 to NULL, calls p1(l1), which assigns 200 to l1, and returns this value.
     • The output (200) is displayed due to DBMS_OUTPUT.PUT_LINE.

Key Points to Note

1. Scope of Variables:
   • l1 is local to f1 and cannot be accessed outside the function.
   • The IN OUT parameter i1 in p1 ensures the value can be modified and accessed by the caller.
2. Initialization of l1:
   • Uninitialized variables in PL/SQL (like l1) default to NULL.
   • Passing NULL to IN OUT parameters is valid but must be handled in the procedure. In this case, p1 assigns a new value (200) to i1.
3. Value Assignment via IN OUT Parameter:
   • The p1 procedure demonstrates the use of IN OUT parameters to modify and return a value to the calling program.
4. Function Return Value:
   • The f1 function uses the output from p1 to derive its return value.

Possible Enhancements

1. Explicit Initialization:
   • Although l1 defaults to NULL, explicitly initializing it improves readability and reduces ambiguity:

   • l1 number := 0;
             

2. Parameter Naming:
   • Use more descriptive names for parameters and variables (e.g., input_output_number instead of i1, local_number instead of l1) for better code clarity.
3. Validation in p1:
   • Add validation in p1 to ensure i1 is not NULL:

   • if i1 is null then
       i1 := 200;
     end if;
             

This script provides a simple example of the interplay between procedures and functions in PL/SQL, showcasing how to pass and manipulate data using `IN OUT` parameters.
The next lesson teaches you to add a function to a procedure.