Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Calling Functions in PL/SQL
Oracle Tips by Burleson

In addition to calls that are made from the system’s front end, functions can be called in three ways:

  • DML statements
  • Anonymous PL/SQL blocks
  • Stored PL/SQL objects

Each method of calling a function is quite similar despite the varied origins of the calls.

DML Statements

Stored functions can be executed as part of a DML statement. An example of calling a function this way is shown in Listing 5.11.

Listing 5.11 Calling a function within a DML statement.

SET    base_salary = Raise_Salary (nRaisePercent => 3.5,
                                   nBaseSalary   => base_salary)
WHERE  faculty_id  = 6572;

In this example, the value of the base_salary column is passed to the Raise_Salary() function. The value returned from the function is stored in the base_salary column. The function Raise_Salary() might look like the function shown in Listing 5.12.

Listing 5.12 The Raise_Salary() function called in Listing 5.11.

FUNCTION Raise_Salary (nBaseSalary   IN     number,
                       nRaisePercent IN     number)
  RETURN (nBaseSalary * nRaisePercent);

When packaged functions are used in this way, a purity level for the function must be defined within the package spec. Purity levels for functions are discussed in Chapter 6.

Anonymous PL/SQL Blocks

Functions can be called from any PL/SQL block, including an anonymous block created during an SQL*Plus session or as part of a script. Consider the PL/SQL block in Listing 5.13, which is part of a unit test for the Raise_Salary() function.

Listing 5.13 An anonymous PL/SQL block that calls a function.

  nResult  number;
  DBMS_Output.Put_Line ('Calling Raise_Salary for 3.5 % ' ||
                        'of $20,000. The result should be ' ||
  nResult := Raise_Salary (nBaseSalary   => 20000,
                           nRaisePercent => 3.5);
  DBMS_Output.Put_Line (to_char (nResult));
  IF (nResult <> 20700) THEN
     DBMS_Output.Put_Line ('ERROR: Function returned wrong value!');

Stored PL/SQL Objects

Functions can be called from other stored functions and procedures, as shown in Listing 5.14.

Listing 5.14 A stored function calling another stored function.

FUNCTION Calculate_Bonus (nEmployee IN     number)
   RETURN number;
   nSalary   EMPLOYEES.base_salary%TYPE;
    IF Check_Bonus_Eligibility (nEmployee => nEmployee) THEN
       SELECT base_salary
       INTO   nSalary
       WHERE  employee_num = nEmployee;
       RETURN (nSalary * 0.01);
    END IF;

In this example, the function Check_Bonus_Eligibility() is a boolean function, returning TRUE if the employee is eligible for a bonus and FALSE if the employee isn’t eligible for a bonus. If the response is FALSE, the THEN clause will not be executed.

This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter