Oracle Tips by
Stored procedures are typically called from a system’s GUI front
end, but can also be called from other stored PL/SQL objects and from
anonymous PL/SQL blocks.
Anonymous PL/SQL Blocks
It’s very common to create anonymous blocks of PL/SQL that call a
stored procedure, especially when the procedure is being tested.
Stored procedures can be called from any PL/SQL block. Consider the
block of PL/SQL in Listing 4.11, which is part of a test for the
Listing 4.11 An anonymous PL/SQL block that calls a
-- Set up a sample employee.
WHERE employee_num = 999;
DBMS_Output.Put_Line ('Salary is: ' || to_char (nNewSalary));
The highlighted portion of this example is a call to the
Annual_Review() procedure. This call is the heart and soul of the
test—after all, how can code be tested if it’s never run?
Stored PL/SQL Objects
In all but the simplest of systems, stored procedures are often
called by other stored procedures. Stored procedures can also be
called from stored functions and PL/SQL objects inside packages.
Listing 4.12 illustrates a call to a stored procedure from inside
another stored procedure.
Listing 4.12 Calling a stored procedure from another stored
PROCEDURE Check_Code (vObjectName IN varchar2,
vOwner IN varchar2)
iObjectExists integer := 0;
IF vObjectName IS NULL THEN
IF vOwner IS NULL THEN
WHERE owner = vOwner
AND object_name = vObjectName
AND object_type IN ('PROCEDURE',
WHEN NO_DATA_FOUND THEN
Check_Source_For_Insert (vOwner => vOwner,
vObject => vObjectName);
WHEN xMISSING_PARAMETER THEN
DBMS_Output.Put_Line ('Both parameters are required.');
WHEN xOBJECT_DOES_NOT_EXIST THEN
DBMS_Output.Put_Line ('The named object does not exist.');
In this example, the Check_Source_For_Insert() procedure is
called from inside the Check_Code() procedure. This allows the
Check_Code() procedure to perform the functionality of the
Check_Source_For_Insert() procedure without including that
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor.