Database is able to further reduce the
need to invalidate and then recompile
program units.
Getting a sense of the scope of the ripple.
Before making any changes to your
existing programs, you should analyze
the potential impact of those changes.
You have the following options for ana-
lyzing the impact of changing the signa-
ture of a program:
1. Search your source code (either in
files or in the ALL_SOURCE data dic-
tionary view) to find all the program
units that contain specific strings,
such as the name of a program. The
following query, for example, finds
all programs that call my_package
.my_procedure:
SELECT FROM ALL_SOURCE
WHERE upper (text)
LIKE ‘ %MY_PACKAGE.MY_PROCEDURE%’
2. Query one of the dependency views
to find all program units that will be
affected by the change you are about
to make to a program. Here, for
example, is a query that shows all
program units that depend on the
package (my_package) defined in the
hr schema:
SELECT FROM ALL_DEPENDENCIES
WHERE referenced_owner = ‘HR’
AND referenced_name = ‘MY_PACKAGE’
3. In Oracle Database 11g only, use the
new PL/Scope feature to obtain much
more detailed information about the
way your programs are used throughout your application.
Analysis based on the ALL_
DEPENDENCIES view tells us that one
program unit depends on another. It
does not, however, tell us the nature of
the dependency, nor is that dependency
information sufficiently detailed. I can
find out, for example, that procedure
PROC1 depends on package PKG1, but
I cannot determine which subprogram
within PKG1 is actually called.
PL/Scope can do that, and much
more. A full treatment of PL/Scope is
outside the scope of this discussion, but
the following example will demonstrate
codeLISTING 1: The process_rows procedure using the OPEN FOR statement
CREATE OR REPLACE PROCEDURE process_rows (where_in IN VARCHAR2)
IS
TYPE weak_rc IS REF CURSOR;
l_cursor weak_rc;
l_name employees.last_name%TYPE;
BEGIN
DBMS_OUTPUT.put_line (
‘Employees identified by “ ’ || where_in || ‘ ” ’);
OPEN l_cursor FOR
‘SELECT last_name FROM employees WHERE ‘ || where_in;
LOOP
FETCH l_cursor INTO l_row;
EXIT WHEN l_cursor%NOTFOUND;
/*
Do the processing here.
*/
DBMS_OUTPUT.put_line (l_name);
END LOOP;
CLOSE l_cursor;
END process_rows;
/
the power of this feature.
To use PL/Scope, I must first direct
the PL/SQL compiler to analyze all of
the identifiers in my program when it
is compiled:
ALTER SESSION SET
plscope_settings=’IDENTIFIERS:ALL’
/
Then when I compile a program
unit, Oracle Database will populate the
data dictionary with detailed information about how each named element
(variables, types, programs, and so on)
in my program is used.
I next create a package and two
schema-level procedures that each
rely on a different subprogram from
that package:
CREATE OR REPLACE PACKAGE my_package
IS
FUNCTION func (arg NUMBER)
RETURN NUMBER;
PROCEDURE proc (arg VARCHAR2);
END my_package;
/
CREATE OR REPLACE PROCEDURE use_proc
IS
BEGIN
my_package.proc (‘a’);
END use_proc;
/
CREATE OR REPLACE PROCEDURE use_func
IS
BEGIN
DBMS_OUTPUT.put_line
(my_package.func ( 1));
END use_func;
/
Now I want to find all the places in
my code where the my_package.proc
procedure is called. I can do so
with this query against the USER_
IDENTIFIERS view:
SELECT called.object_name
FROM user_identifiers called,
user_identifiers declared
WHERE declared.USAGE =
‘DECLARATION’
AND declared.NAME =
‘PROC’
AND declared.object_name =
‘MY_PACKAGE’
AND called.USAGE = ‘CALL’
AND called.signature =
declared.signature
