Chapter 8

Managing Procedures, Functions, and Procedural Dependency

In this chapter, you will cover the following areas of PL/SQL program development:

• Managing procedures and functions
• Managing procedural dependency

Once developed, procedures and functions require some maintenance management for continued efficiency and performance. You need to understand some key points about the management of procedures and functions, and the dependencies Oracle enforces on procedures and functions stored in the Oracle database that use database objects like tables, views, indexes, and the like. This area, though perhaps less obvious to developers as how to construct for loops and if – then statements, is critical to maintenance programmers who must support PL/SQL running on production systems while simultaneously designing enhancements or new functionality into the production application. This subject area comprises 23% of OCP exam 2 test content.

In this section, you will cover the following points on managing procedures and functions:

• Finding information about stored procedures in the data dictionary
• Security for owner and user on stored PL/SQL

Managing stored procedures and functions has many of the same challenges inherent in managing code for other environments. There are problems of where and how to find information about the code, and application security issues. Beyond the scope of Oracle certification but certainly no less important is source code version control. These issues form the core of what you need to know about source code management in Developer/2000. In this section, you will cover discussions in these areas.

There are many views in the data dictionary in Oracle that hold information about stored procedures and functions. Those views store everything from information about who owns the procedures to who can execute them, even the source code of a stored procedure, function, trigger or package. As stated in chapter 3, there are three categories of most data dictionary views corresponding to the scope each view has over the total data in the Oracle database, USER_, ALL_ and DBA_. Sometimes the management of stored procedures and functions ultimately may fall on the DBA like management of most database objects does. In some cases, there may be a production developer or maintenance and enhancement team that handles this functionality. The DBA_ views will be considered in this discussion, because they provide the most comprehensive view on the database, though you should bear in mind that the USER_ and ALL_ views will be of some limited use in source code management as well.

The DBA_OBJECTS view stores a wide variety of information about objects in the database, including PL/SQL blocks. The columns in this view include OWNER, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, and STATUS. Pay attention to rows in this view where OBJECT_TYPE is ‘PACKAGE’, ‘PACKAGE BODY’, or ‘PROCEDURE’. This view offers information about the PL/SQL block such as when it was created, last changed, and whether it’s valid or not. Questions about the validity of a PL/SQL block touches on the topic of procedural dependency, covered later in the chapter. The formatting in this output is not standard, it has been modified for readability.

SQL> select owner, object_name, o_id, object_type, created, last_ddl, timestamp, status
2> from dba_objects where object_type in
3> (‘PROCEDURE‘,’PACKAGE’,’PACKAGE BODY’) and owner = ‘STACY’;

OWNER OBJECT_NAME O_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP
----- ----------- ---- ----------- --------- --------- -------------------
STATUS
------
STACY FLURB 1079 PROCEDURE 07-SEP-99 07-SEP-99 1999-09-07:17:02:54 VALID

STACY FOO 1078 PROCEDURE 06-SEP-99 06-SEP-99 1999-09-06:15:05:29
INVALID

Perhaps the most important of dictionary views related to PL/SQL blocks, DBA_SOURCE stores the source code for PL/SQL blocks. This source may either be in plain text format or encoded using the PL/SQL wrapper. A wrapper is an encryption method that allows the developer of PL/SQL packages to distribute them in a portable format for use on other Oracle databases, yet in such a way as to prevent others from viewing the source code directly. This method allows an individual or company to protect complex logic, business rules, or other coding secrets while allowing others to use the functionality the procedures and functions provide. Much of Oracle’s server packages such as DBMS_SQL are distributed using the PL/SQL wrapper.

The columns of the DBA_SOURCE dictionary view include OWNER, NAME, TYPE, LINE, and TEXT. The key to this dictionary view is that, when you want to view only the source code, you must remember to reference only the TEXT column in your select statements. Thus, select * from DBA_SOURCE where NAME = ‘proc_name’; is a bad idea that usually results in a buffer overflow error in SQL*Plus. Instead, you can switch the * for TEXT. This allows you to see only the source code corresponding to the PL/SQL block named by the query. The following block contains a statement that allows you to select data from this view for a procedure name that you can enter interactively. Note the use of the ampersand (&) character, which precedes any input variable, and the upper( ) SQL built-in function, which converts characters to their uppercase form.

SQL> select text from dba_source where name = upper('&plsql_codename')
2> order by line;
Enter value for plsql_codename: foo
old 1: select text from dba_source where name = upper('&plsql_codename')
new 1: select text from dba_source where name = upper('foo')

TEXT
---------------------------------------------------------------------------
procedure foo
begin
dbms_output.put_line('I am foo, who are you?’);
end;

After covering debugging in chapter 7, you know that the DBA_, USER_ or ALL_ERRORS dictionary views contain the errors produced during compilation of PL/SQL procedures, functions and packages. These errors are only valid for the most recent compile. A new compile causes the old errors to be lost. As explained in chapter 7, this view is a little difficult to use, because the line numbers it gives for error location correspond to the line number of the PL/SQL code as it appears in the SQL statement buffer in SQL*Plus during the compilation. Thus, it is usually wise to view this information in a session separate from the session you use to perform the actual compile. The columns in the DBA_ERRORS view include OWNER, NAME, TYPE, SEQUENCE, LINE, POSITION, and TEXT, while USER_ERRORS subtracts the OWNER column that defines who owns the code producing these errors. The following code block shows a selection from USER_ERRORS.

OWNER NAME TYPE SEQUENCE LINE POSITION
----- ------------------------ ------------ --------- --------- ---------
TEXT
-------------------------------------------------------------------------
STACY FOO PROCEDURE 1 2 1
PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:
( ; is with as compress compiled wrapped
The symbol "is" was substituted for "BEGIN" to continue.

STACY FOO PROCEDURE 2 3 22
PLS-00103: Encountered the symbol "I am foo, who are you?);
end;
" when expecting one of the following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
<a number> <a single-quoted SQL string> avg count exists max
min prior sql stddev sum variance

The final dictionary view considered here is the DBA_JOBS view. This dictionary view on the Oracle database tracks information about the Oracle database job scheduler, which is used to run PL/SQL code for regularly scheduled batch operations without the use of job scheduling from the operating system or a third-party vendor. The benefit of using Oracle for job scheduling is that you don’t need to develop support into the batch job to handle situations where the database is not available of if a user’s password has changed. Since these jobs are internal to Oracle, they will fire automatically as long as the database is running. If the database is not running, the job will not fail because the job will not be run. Also, Oracle retries jobs for a period of time you specify automatically, eliminating the need to retry a job manually when you learn it has not run. Oracle-scheduled jobs can be used in conjunction with packages and features in the Oracle database like DBMS_OUTPUT, DBMS_IO, and the spool command to write text output to a file. This method of job scheduling is as effective as batch job scheduling with Unix shell scripts, Pro*C programs, crontab, or other operating system-driven methods.

The columns in the DBA_JOBS view give information about the jobs scheduled to run on the Oracle database. Several columns of data are provided, including JOB, a number used to identify uniquely each job entered in the database. Several columns track the different users that may be involved in job execution, including LOG_USER, PRIV_USER, and SCHEMA_USER. Several dates for job execution are tracked as well, including LAST_DATE, LAS_SEC, THIS_DATE, THIS_SEC, NEXT_DATE, and NEXT_SEC, indicating the last, current, and next time the job did run or will run. Several other things are tracked as well, in the following columns: TOTAL_TIME, BROKEN, INTERVAL, FAILURES, WHAT, CURRENT_SESSION_LABEL, CLEARANCE_HI, CLEARANCE_LO, NLS_ENV, and MISC_ENV. Several of these columns are used to track whether a job is broken, and if so, how many times has it ended abnormally.

Recall from chapter 6 that, to create PL/SQL program components on the Oracle database, you require the create procedure system privilege granted by the DBA or some other privileged user on the database. This is required for server-side PL/SQL only, client-side PL/SQL can be created and used by anyone. To run server-side procedures, you must have the execute object privilege granted to you. If you’re still wondering about system and object privileges related to PL/SQL code development, review chapter 6.

There is an important factor to consider when running stored procedures and functions, related to what the function does and whether the user of the procedure or function is allowed to do it. Say for example that user ATHENA wants to run user SPANKY’s procedure find_mouse( ). This procedure performs a select statement on the MOUSE_HOUSE table, for which ATHENA does not have select privileges but user SPANKY does.

You might think that ATHENA would not be able to run the find_mouse( ) procedure, because even though she has execute privileges on that procedure, she doesn’t have select privileges on MOUSE_HOUSE. This assumption is wrong, in fact. ATHENA can run the procedure successfully. Why? Because Oracle only cares that SPANKY, the owner of the procedure, has the select privilege required to execute the procedural components successfully. What’s more, user SPANKY must have these privileges on the objects referenced in the program unit granted explicitly to him, not through a role, or else SPANKY’s own compilation of his program unit will fail. Thus, the user of a procedure needn’t have the underlying object privileges required to run the statements in a stored procedure or function, she need only have the execute privilege on the procedure. The owner of the procedure or function, however, must have all privileges required by the procedure for it to compile and run successfully. This fact touches on functional dependency, which is covered later this chapter as well.

User ATHENA only needs to concern herself with obtaining execute privileges on that stored function. SPANKY, meanwhile, must have all privileges granted to him that are required to run the function successfully, even though the function will be run by other people. Thus, the developer of an application can not only modularize the application functionality by encapsulating logic into stored procedures and functions, she can modularize database access privileges via stored procedures as well.

By giving the application schema owner the actual object privilege to access table data, she can effectively moderate the actual access to the database any user may have. An application schema owner (SPANKY in this case, for example) owns the database access privileges and moderates them to user ATHENA by only allowing her to view as much data as the function find_mouse( ) will provide her. So, the developer can then revoke select privileges to table MOUSE_HOUSE from ATHENA while still allowing her to do her job finding mice with the assistance of application code. Figure 8-1 illustrates the principle of maintaining security on the Oracle database by allowing users access to data programmatically.

Several advantages are offered with employment of this method throughout the database and application. The database is more secure because no user other than the application schema owner has object privileges that would allow them to go out and run SQL statements that view or modify data that are not part of a stored procedure or function. This design limits the overall usage of the system to flow only through stored procedures and functions. Interestingly, it gives the user as much access as the procedure will allow, so even though ATHENA may not have select access on MOUSE_HOUSE, she can still see the data she needs to see in that table anyway. PL/SQL then extends the basic functionality provided by Oracle in the form of granting execution on program privileges to users that cannot otherwise access the data.

In this section, you will cover the following points about managing procedural dependencies:

• Tracking procedural dependencies
• Database object changes and stored procedures
• Managing procedural dependencies in one database
• Managing procedural dependencies in distributed databases

It has been said that no man is an island. That author, of course, neglected to realize that women, like men, are not islands, either. The point here is that people and things in a society are usually interconnected. The same holds true in Oracle, the software used to represent or model reality. Objects like tables, indexes, views, and program units, are interconnected. As a result of this interconnectivity, there are dependencies placed on one object such that if the object were to fail or disappear, its absence would be noticed by its dependents. This section covers the management of dependency in PL/SQL applications. There are two different types of dependencies a PL/SQL block may have – dependency on another PL/SQL block, called procedural dependency, and dependency on a database object, called object dependency. The tracking of procedural dependencies in the Oracle database will be covered in this section, along with analyzing the effects of database object changes on PL/SQL blocks. The special responsibilities involved in managing dependency on single and distributed databases are covered, too.

In the course of PL/SQL development, you will encounter many different types of applications, from financial applications and accounting software to HR and employee-related service applications, to telemarketing applications, to just about anything else you can think of. One common thread between all these applications, however, will be that there is a strong chance the application will be big. Take this to mean that you may be required to develop and maintain applications with several thousand or more lines of source code. Now, that’s a complicated task, so you will need to understand the implications of maintaining such complexity.

Often, to simplify the complexity of a large application, you will want to use modularity in program design. This means you will take your overall program activity and break it down into logical units of work. Once this is complete, you will take your logical units of work and develop PL/SQL blocks to handle only the logical component. In particular, this method helps if there is a repeatable task that happens in the application, such as the selection of data for validation purposes. You simply develop your function or procedure to handle the logical mechanisms required, and define parameters to be passed in and out to handle the specifics of the work.

In this development scenario, you are creating procedural dependencies. Procedural dependency in this situation literally means "one procedure depends on another." When developing code in a modular fashion, beware the dependencies you create. For example, if a defect appears in a procedure that is called by 17 other procedures, then the defect will occur in at least that many places. Without a clear idea or knowledge of your overall application, you will become very frustrated very quickly unless you have methods available to track the dependencies one unit of code has on another.

Fortunately, there are some items at your disposal for tracking procedural dependencies between database PL/SQL programs. There are a group of dictionary views that were held back from prior discussion that benefit your quest to find the procedural dependencies in your Oracle database. This group is the USER_, ALL_ and DBA_DEPENDENCIES views. As with most dictionary views, there are three possible limitations on scope, including the current user’s dependencies, all dependencies in the database the current user can see, and all dependencies in the database, period. Due to the importance of tracking procedural dependencies in the Oracle database with respect to PL/SQL programs, the following is a list of columns in the DBA_DEPENDENCIES view, with an explanation of the contents of that column. Here we go:

• OWNER The user who owns the PL/SQL block, also known as an application schema owner.
• NAME The name of the PL/SQL block, in all-caps.
• TYPE The type of PL/SQL object, such as ‘PROCEDURE’, ‘FUNCTION’, ‘PACKAGE’ or ‘PACKAGE BODY’.
• REFERENCED_OWNER The user who owns the referenced PL/SQL block.
• REFERENCED_NAME The name of the referenced PL/SQL block.
• REFERENCED_TYPE The type of the referenced object, like ‘PROCEDURE’, ‘FUNCTION’, ‘PACKAGE’, ‘PACKAGE BODY’, ‘TABLE’, and so on.
• REFERENCED_LINK_NAME The name of the database link used to access the referenced PL/SQL code block. This column contains a value only when the referenced object is on another Oracle database.

Let’s examine now the usage of the DBA_DEPENDENCIES view. Assume we have a complicated set of procedures, functions, and packages on the Oracle database for an application that conducts international financial transactions. One of these functions, convert_money( ), is used to convert a monetary amount from one currency to another. The convert_money( ) function accepts four variables, from_crcy, to_crcy, from_amt and valid_date, and returns data of type NUMBER. Notice this function violates the good programming practice of only using in parameters for functions, allowing it to return only one value. The code for this function appears in the following code block.

CREATE FUNCTION convert_money (
p_from_crcy VARCHAR2,
p_to_crcy IN OUT VARCHAR2,
p_from_amt NUMBER,
p_valid_date DATE
) RETURN NUMBER IS
my_exch_rate NUMBER(15,4);
my_ret_val NUMBER(15,4);
BEGIN
SELECT exch_rate INTO my_exch_rate FROM EXCH_RATE
WHERE to_crcy = p_to_crcy AND from_crcy = p_from_crcy
AND p_valid_date = TRUNC(sysdate);
my_ret_val := p_from_amt*my_exch_rate;
RETURN my_ret_val;
END;

As you can see, to perform its application logic, convert_money( ) obtains an exchange rate from the EXCH_RATE table. The table consists of four columns, TO_CRCY, FROM_CRCY, EXCH_RATE, and VALID_DATE. The FROM_CRCY and TO_CRCY columns contains character strings that represent world currencies, like GBP for the British pound, USD for the US dollar, or IR for Indian rupee, that represent the currency you convert from and to, respectively. The EXCH_RATE column contains a small number used as the conversion factor from the one currency to another. The last column, VALID_DATE, contains a date on which that value in EXCH_RATE is valid.

Several PL/SQL procedures in the applications call the convert_money( ) function. Several of these procedures are experiencing difficulties in obtaining correct conversions for the day they pass into the application. You are the developer in this situation, and have been asked to take a look at the situation. To determine the different procedures that call the convert_money( ) function, the SQL statement in the following code block can be used.

SQL> select name, referenced_name from dba_dependencies
2> where referenced_name = ‘CONVERT_MONEY’;

NAME REFERENCED_NAME
------------------- ----------------------------
PROC_TODAY_TRAN CONVERT_MONEY
PROC_YEST_TRANS CONVERT_MONEY
PROC_MNTH_TRANS CONVERT_MONEY

Based on the data coming from this view, you can now identify the three different procedures that depend on this function. Judging strictly by the names of the procedures that call convert_money( ), you can see that three types of transactions need to convert money, those that happen today, those that happened yesterday but are processed today, and those that happen monthly. Using this information, you should be able to determine the problem – simply run proc_today_tran( ), proc_yest_trans( ), and proc_mnth_trans( ) separately to determine which one converts money properly. Upon execution of each procedure, and further examination, you will notice that the error lies in the convert_money( ) function, in that the select statement never actually uses p_valid_day at all – instead, convert_money( ) uses the sysdate keyword in the overall selection criteria for obtaining the appropriate exchange rate – a semantic error that will only be obvious in processing yesterday’s or monthly transactions!

So, the proc_today_tran( ), proc_yest_trans( ), and proc_mnth_trans( ) procedures depend on the appearance and validity of the convert_money( ) function. A procedure or function is valid only after successful compilation against the PL/SQL engine in the Oracle database. After you make the necessary change to this function and recompile, if for some reason the compilation failed, you not only cannot use convert_money( ), you cannot execute proc_today_tran( ), proc_yest_trans( ), or proc_mnth_trans( ), either.

Another view for finding database dependencies can be used as well. This view is called DEPTREE. DEPTREE lists the dependency information stored in an underlying table called DEPTREE_TEMPTAB. Another view, called IDEPTREE allows you to see dependency information such that dependent objects are shown below the objects they depend on, and indented. These views give both direct and indirect dependency information while DBA_DEPENDENCIES can give only direct procedure and object dependencies in the Oracle database. To make them available for your use, you must run the utldtree.sql script found in the rdbms/admin subdirectory under your Oracle software home directory to create the appropriate dependency objects and PL/SQL blocks that comprise this utility. Another object, the procedure called deptree_fill( ), actually populates the underlying table for your use. You should run it before trying to see the dependencies with DEPTREE or IDEPTREE, using the syntax provided in the following code block. NOTE: all three parameters are of type CHAR.

SQL> execute deptree_fill(‘obj_type’,‘obj_owner’,‘obj_name’);

The columns in the DEPTREE view include NESTED_LEVEL, OBJECT_TYPE, OWNER, OBJECT_NAME, and SEQ#. The column in IDEPTREE is DEPENDENCIES.

Recall from chapter 5 that SQL statements embed easily into PL/SQL. In fact, the main purpose of PL/SQL is to make it possible to design applications that reside partially or wholly within the Oracle database. Thus, you may find yourself developing PL/SQL programs that use SQL statements intensively. The sorts of statements that you might use include DML statements like select, update, delete, or insert. You may recall that PL/SQL does not permit the use of DDL statements directly like create table or drop index, but rather you must use dynamic SQL via a special package in Oracle called DBMS_SQL. More on the role of Oracle-supplied packages in the Oracle database in chapter 9.

Every time you incorporate a SQL statement that calls a database object in your PL/SQL statement, you create a dependency on that object. Thus, if your PL/SQL block contains the statement select * from EMPLOYEE, your PL/SQL block then depends on the existence of the EMPLOYEE table in order to function properly. Tracking object dependency in your PL/SQL programs is highly important to ensure continued usage of your programs does not fail. Since an application may call dozens or even hundreds of tables in the course of its operation, the object dependencies a PL/SQL program will have on underlying tables are so pervasive it is difficult to tell the dependency exists between the PL/SQL program until it is too late. The only real option to you as the developer or programmer is to understand each PL/SQL program unit in your application thoroughly.

Oracle has particular behavior in this situation. First, Oracle enforces dependency on database objects. Let’s return to our convert_money( ) function. In addition to the proc_today_tran( ), proc_yest_trans( ), and proc_mnth_trans( ) procedures being dependent on the availability and validity of convert_money( ), so too must the EXCH_RATE table be valid and available or the convert_money( ) function will not work properly, either. The trickle-down effect is in place as well, so execution of proc_today_tran( ), proc_yest_trans( ), and proc_mnth_trans( ) will not work properly, either. The cycle of direct procedural and object dependency and the indirect dependencies that ensue are all demonstrated in figure 8-2.

Any statement that has the effect of changing a table referenced by a procedure or function will cause that procedure or function to become invalid. For example, an drop table statement that removes EXCH_RATE that the convert_money( ) function selects will render the convert_money( ) function invalid. The following code block demonstrates use of the DBA_OBJECTS table to determine PL/SQL code block validity before any activity on the Oracle database takes place. Remember that the DBA_OBJECTS view contains the all-important STATUS column, which describes the validity of any object for use in the Oracle database. Invalid PL/SQL program units must be recompiled explicitly before you can use them again.

SQL> select object_name, object_type, status from dba_objects
2> where object_name = ‘CONVERT_MONEY’;

OBJECT_NAME OBJECT_TYPE STATUS
------------- ----------- -------
CONVERT_MONEY PROCEDURE VALID

Important to note also that any procedural dependencies that existed on convert_money( ) are still in place, so that if the underlying object in the database to convert_money( ) is dropped, the convert_money( ) and the proc_today_tran( ), proc_yest_trans( ), and proc_mnth_trans( ) procedures will all become invalid as well. The following code block illustrates.

SQL> drop table exch_rate;
Table dropped.
SQL> select object_name, object_type, status from dba_objects
2> where owner=’SPANKY’ and object_type = ‘PROCEDURE’;

OBJECT_NAME OBJECT_TYPE STATUS
---------------- ----------- -------
CONVERT_MONEY FUNCTION INVALID
PROC_MNTH_TRANS PROCEDURE INVALID
PROC_TODAY_TRAN PROCEDURE INVALID
PROC_YEST_TRANS PROCEDURE INVALID

A change will have to be made to the convert_money( ) function before it works properly again, and the function will need to be recompiled. In this case, the change is to restore the table or drop the function. If you drop the convert_money( ) function, you must change the other three procedures to not call convert_money( ) anymore or they will not work properly. Once the change is made, in this case restoring the EXCH_RATE table to its pre-drop state, you should then recompile the function convert_money( ) and the other procedures that are dependent on it, before allowing users to utilize the application again.

Recall from earlier discussion that the alter procedure proc_name compile or alter function func_name compile statement will recompile an existing procedure or function. If the underlying problem of a table missing has been corrected, there is no need to edit the source code for any of the procedures or functions involved. Bear in mind the order of procedural dependencies must be followed when recompiling these functions and procedures. The following code block illustrates.

SQL> alter function CONVERT_MONEY compile;
Function altered.
SQL> alter procedure PROC_TODAY_TRAN compile;
Procedure altered.
SQL> alter procedure PROC_YEST_TRANS compile;
Procedure altered.
SQL> alter procedure PROC_MNTH_TRANS compile;
Procedure altered.

What about situations where the underlying object dependency is not removed, merely changed? For example, say you do not create a table to replace EXCH_RATE, instead creating a synonym EXCH_RATE from data for another table. This act will allow you to recompile your procedures and functions only if the function with the object dependency on EXCH_RATE sees the same columns in the new underlying table as it did in EXCH_RATE.

Another situation to consider is when you issue an alter table statement against EXCH_RATE that adds columns not related to the activities of convert_money( ). Something interesting happens here that you should remember. Even though the column added is not accessed by any of the procedures and functions in your application, the mere act of redoing the EXCH_RATE table is enough to invalidate the function and procedures. The following code block illustrates.

SQL> alter table exch_rate add (rate_comment varchar2(80));
Table altered.
SQL> select object_name, object_type, status from dba_objects
2> where object_name = ‘EXCH_RATE’;

OBJECT_NAME OBJECT_TYPE STATUS
---------------- ----------- ------
EXCH_RATE TABLE VALID

SQL> select object_name, object_type, status from dba_objects
2> where owner=’SPANKY’ and object_type = ‘PROCEDURE’;

OBJECT_NAME OBJECT_TYPE STATUS
---------------- ----------- -------
CONVERT_MONEY FUNCTION INVALID
PROC_MNTH_TRANS PROCEDURE INVALID
PROC_TODAY_TRAN PROCEDURE INVALID
PROC_YEST_TRANS PROCEDURE INVALID

So, even though the underlying database object is valid, the PL/SQL code is not. This is simple item to fix, simply recompile all functions and procedures affected by the database object change in the order of their procedural dependency. A more significant effort will be required if you eliminate the VALID_DATE column being used in the function convert_money( ) that depends on the database object. If so, the convert_money( ) programs that relies on this table must be altered in order to accommodate the table change. Refer to the discussion of creating PL/SQL functions and procedures in chapter 6 and the original version of convert_money( ) for help if you don’t remember how to do this and a version of the function that will work properly.

One final thought – what happens if an object or procedural dependency other than convert_money( ) or EXCH_RATE causes the proc_today_tran( ) procedure to become invalid? What is the effect on convert_money( )? There is none, because convert_money( ) doesn’t share a procedural dependency with its caller – the caller is dependent on it, but the same in reverse does not hold true. So, if proc_today_tran( ) performs a select against the TRANS_TABLE, and that table gets dropped, proc_today_tran( ) will become invalid but convert_money( ) will not.

Obviously, there can be some challenges inherent in the management of dependency in an Oracle database. If the DBA alters the database objects in some way, it is likely that problems may ensue with the applications that perform DML operations against the database. Similarly, if there is a piece of PL/SQL code that many different code blocks depend on, such as those handling routine operations in the application like convert_money( ), recompiling that PL/SQL block may cause invalidation of code for other PL/SQL blocks in the application.

In general, it makes sense that you understand what the procedural and object dependencies of the application are, so as to understand procedure invalidation and its effects on that application. For example, you may have an application that consists of several different packages, one of which may contain utility procedures and functions used by several other procedures. You can’t just make changes to this utility package or any procedure in the package without considering the effects on other parts of the application, because if you do, you will invalidate some of all of your application in the process.

To assist you with the task of managing procedural dependency in a single database, you need to understand how Oracle enforces procedural dependency. One of the methods Oracle uses for managing procedural dependency is called timestamping. Recall from earlier in the chapter the discussion on where to find information about PL/SQL in the Oracle database. One dictionary view used to identify dependency information was DBA_DEPENDENCIES. Another is DBA_OBJECTS.

Recall that DBA_OBJECTS contains information about the status of the procedure or function in the Oracle database. This information is stored in the STATUS column of the view. Another bit of information you will recall is the date and time the object’s definition code was changed and/or compiled. Two columns are of use here, the LAST_DDL_TIME column and the TIMESTAMP column. Usually, the format of LAST_DDL_TIME will be Oracle’s standard date output format, ‘DD-MON-YY.’ This is true even though the datatype for this information in the DBA_OBJECTS table is CHAR. The column format for LAST_DDL_TIME may be different depending on whether or not you have altered the format Oracle uses to show dates with the alter session set nls_date_format = ‘format’ statement. The TIMESTAMP column, which is also stored as a character string, will always show both the date and time to seconds precision of the last occasion when you have changed the object definition or compiled the PL/SQL code, however. The format for timestamp is always the same, and that format is ‘YYYY-MM-DD:HH24:MI:SS’.

The use of DBA_OBJECTS and DBA_DEPENDENCIES will be combined now, to give you a clearer idea of how Oracle enforces dependencies according to their timestamp. The following code block shows information about the procedures and functions that are part of the international financial application example. Both the query and the results are shown in order to demonstrate how you identify timestamping information. The reference to chr(10) allows you to put a carriage return into the output. The formatting has been cleaned up for readability, but you should get the general idea.

SQL> select d.name, d.type, o1.status, o1.timestamp, chr(10),
2> d.referenced_name, d.referenced_type, o2.status, o2.timestamp, chr(10)
3> from dba_dependencies d, dba_objects o1, dba_objects o2
4> where o1.owner = ’SPANKY’ and d.name = o1.object_name
5> and d.owner = o1.owner and d.referenced_name = o.object_name
6> and d.referenced_owner = o2.owner;

D.NAME D.TYPE O1.STATUS O1.TIMESTAMP
----------------- ----------------- --------- -------------------
D.REFERENCED_NAME D.REFERENCED_TYPE O2.STATUS O2.TIMESTAMP
----------------- ----------------- --------- -------------------
CONVERT_MONEY FUNCTION VALID 1999-07-11:17:01:59
EXCH_RATE TABLE VALID 1998-09-30:09:15:34

PROC_MNTH_TRANS PROCEDURE VALID 1999-07-12:14:03:03
CONVERT_MONEY FUNCTION VALID 1999-07-11:17:01:59

PROC_TODAY_TRAN PROCEDURE VALID 1999-07-12:14:03:03
CONVERT_MONEY FUNCTION VALID 1999-07-11:17:01:59

PROC_YEST_TRANS PROCEDURE VALID 1999-07-12:14:03:03
CONVERT_MONEY FUNCTION VALID 1999-07-11:17:01:59

So, as you can see, all of the items in the output of this query are valid. Notice that the timestamps on every referenced item is an earlier point in time than the timestamps on the referring items. This is not by accident. If the times on the referenced items were later than the referring items, you would likely see that all the referring items would be invalid. So, you can see the evidence for the premise that referenced items must be compiled before referring items in order for everything to be valid in an application with procedural and object dependency.

Oracle works well with using the timestamp method for determining procedural dependency in local databases. Since time can be considered a constant across multiple database objects residing in the same database, it makes sense for Oracle to use this as the method of choice for handling procedural dependency. If a referenced program unit has been compiled later than a referring program unit, then the referring unit will be marked invalid by Oracle and must be recompiled before you can use it again.

Consider the following problem. You have an application that takes care of travel expenditure and relocation costs for employees of a Fortune 500 corporation. This application works in online mode for situations when you are in the office and submitting your expenses. The application also works offline, so that as you travel and are out of the office, you can enter your expenses as you incur them. Some of the PL/SQL code components for the application are stored on the client machine, while others are stored on the Oracle server. The client PL/SQL program units are compiled and in use on January 02, 2001. However, the folks in IS determined a leftover bug from the millennium project and made the necessary changes, recompiling the server-side PL/SQL program units over the holiday weekend. The following Monday, however, some people traveling on business over the holidays return to the office to find that their application, which had worked fine offline and before they left, now no longer works.

Based on the facts of this scenario, can you determine what the cause of the problem is? If you said timestamping, you’re right! Another possible situation where this may occur is when the PL/SQL code on one database calls PL/SQL via database link on another database, where the second PL/SQL block was recompiled. In order for the first block to use the second, the first must be recompiled, even though nothing changed about the first block. Unnecessary PL/SQL recompilation on the referring PL/SQL on the first database is annoying but not severe. A slightly more complicated situation is when the PL/SQL source code for the first block is immediately available on the client machine, as is often the case in Forms applications where only the executable is available.

Consider another problem involving distributed systems. You are a developer in Utah coordinating compilation of a procedure with a developer in London. Your colleague in London has just compiled a procedure, call it foo( ), that you call in your procedure, called foobar( ). When your procedure goes out to the Oracle database in London via database link to obtain a timestamp for foo( ), your compilation is not going to work, because the timestamp of foo( ) is incorporated into the compilation of your procedure, foobar( ). Since foo( ) was compiled at a "later" local time (remember, London is several time zones ahead of Utah) than foobar( ), timestamping renders the foobar( ) procedure invalid.

A method used for solving this problem is present in Oracle PL/SQL version 2.3 and later. Rather than allowing Oracle to use timestamping to determine whether a PL/SQL block is valid or not, you can use the signature method. A signature is a piece of information that Oracle can store along with the procedure or function that helps determine when the PL/SQL block must be recompiled. A signature contains several bits of information about the procedure specification being compiled, including the datatype and position of parameters passed into the procedure or function.

Unlike a timestamp, which changes every time the DDL for a database object or PL/SQL block is altered, the signature changes only when the specification for the PL/SQL object changes. Thus, in the situation where logic in the procedure or procedures changes, but the mechanism used to call the procedure (ie, the specification) does not change, you have no need for recompiling the procedures that are dependent on this stored procedure or function.

Something needs to happen on the Oracle database in support of using the signature method for determining procedural dependency. There are basically three options for starting use of the signature method for determining procedural dependency. In the first, you change an initialization parameter in your init.ora file, used for starting the database. The initialization parameter in Oracle used to initiate the signature method for determining procedural dependency is REMOTE_DEPENDENCIES_MODE. This parameter can be set to signature or timestamp. As you may assume, the timestamp value is the default for the Oracle database.

You must remember that changing the value for an initialization parameter in the init.ora file doesn’t guarantee the Oracle database will start using the new value for that parameter immediately. For any change in the init.ora file to take effect, you must shut down and restart the Oracle database. If you want the change from timestamp method to signature method for procedural dependency enforcement to take place immediately within the Oracle database, the statement in the following code block must be issued.

SQL> alter system set remote_dependencies_mode = SIGNATURE;

Only a user with the alter system privilege (usually SYS or comparable user) can issue this statement successfully. The effect will be immediate. In some cases, you may want to be more selective in which procedures and functions use the signature method and which use timestamping. Perhaps there are vast portions of applications on the Oracle database that are used locally and make no remote procedure calls. Rather than disrupting operations here, or even to ensure that those PL/SQL blocks must be recompiled if another block is changed, you can set the signature method of procedural dependency enforcement only to apply to procedures and functions compiled out of your current session. This is done with the statement in the following code block, which can be issued by any user with the create session privilege.

SQL> alter session set remote_dependencies_mode = SIGNATURE;

After setup of the signature method for procedure dependency enforcement, you must recompile your PL/SQL code to create signatures for the PL/SQL blocks on the Oracle database. Use of signature method for enforcing dependency is useful mainly to remote procedure calls on distributed databases. Several facts, behaviors, and conditions are worthy of note when using the signature method for enforcing procedural dependency in Oracle. They are:

• Signatures include name of PL/SQL block, parameter datatype and direction (in, out, or in out), and return type (functions only).
• Not included in signatures are parameter default values, parameter name, and implicit direction definition (ie, omitting in from a parameter definition).
• Certain changes to PL/SQL block specifications do not alter the signature. They include changing the name of a parameter or explicitly defining in for a parameter that was already an in parameter implicitly (ie, direction had not been defined).
• Only major datatype changes on parameters will alter the signature on a procedure or function. For example, changing a parameter from type VARCHAR2 to VARCHAR (or even ROWID) will not alter the signature, but a change from DATE to NUMBER (or DATE to ROWID) will.
• Adding another procedure or function to the end of a package will not alter the other signatures in a package specification. Adding the procedure body somewhere in the middle or at the beginning will invalidate the signatures of procedures and functions in the package appearing after the new procedure or function.
• Adding another procedure or function to an already-overloaded procedure or function falls under the same rules as adding other procedures and functions to a package (ie, its presence at the end will not cause the other signatures to become invalid). But, you will not be able to use the new procedure or function until the local procedure or function calling the new overloaded version is recompiled.

A point about major changes to parameter datatypes affecting signatures while minor changes not affecting the signature should be further clarified. A minor change in a datatype is one that alters the minutae of a datatype but not the overall function. For example, changing the datatype of a parameter between INTEGER, NUMBER, REAL, FLOAT, DECIMAL, DOUBLE_PRECISION, or SMALLINT does not alter the overall type of the parameter, that is to say the parameter will still be a number of some kind. The same can be said for altering a parameter between the VARCHAR2, VARCHAR, STRING, or ROWID datatypes. DATE datatypes are in their own category, so any type change from DATE to anything else, or from anything else to DATE is by definition a major change. The same is true for MLS_LABEL. Changes between CHAR and CHARACTER are minor, as are changes between RAW and LONG RAW. A grey area in type changes resides in the change of certain numeric datatypes like INTEGER or NUMBER to other integer datatypes like PLS_INTEGER, POSITIVE, NATURAL, or BOOLEAN. These are actually major type changes and will affect signatures.

This chapter covers a number of important concepts related to management of procedures and functions. In addition, the topic of managing procedure dependency is covered in the chapter as well. Finding information in the data dictionary and security options available for owner and user is covered, along with the functions in Oracle that are available to track procedure and object dependency. Also, the special tasks involved in managing dependency both on individual databases and in a distributed database environment are covered. Together, these areas comprise about 23% of OCP exam 2 test content. Although this chapter is slightly smaller than some of the others in the unit, it is highly important to understand these concepts in order to pass the test.

Managing procedures and functions involves the ability to find information about stored procedures in the data dictionary. Several views are available in the database dictionary for the purpose of finding information about stored procedures and functions. The first this chapter covered is the DBA_OBJECTS view. This one is important in its ability to provide status and timestamping information about a stored PL/SQL block (indeed, any database object). This timestamp is then used as a determinant in identifying procedure dependency, another topic of the chapter. To obtain information from DBA_OBJECTS related only to PL/SQL blocks, select against this view by where the value in the OBJECT_TYPE column is in the set (‘PROCEDURE’, ’FUNCTION’, ’PACKAGE’, ‘PACKAGE BODY’). And make sure that your select statement has those PL/SQL types in uppercase, as they are shown here.

Other views used for PL/SQL source code management abound in the database. One is DBA_SOURCE, the view that offers the actual source code for the PL/SQL block. This is highly useful in the absence of effective source code control, because the view allows you complete access to the most current version of your application. Of course, if you want to revert to a previous version of the block, you have to store the source code yourself. Anyway, you should beware of selecting data from this view with the select * wildcard, because this causes several other items to be drawn from the table, in addition to the source, which is stored in the TEXT column of this view. Instead, merely using the select TEXT from DBA_SOURCE where NAME = ‘proc_name’ will work just fine for obtaining the source code for your procedures. Don’t forget to issue the spool filename.sql statement before source code selection from the dictionary – most real-world procedures and functions are long and it’s a waste of time to type them over and over (although it does wonders for your abilities as a touch typist!).

It also makes sense to add the order by LINE clause to ensure that your source code is properly ordered in the output file, although experience tends to show the proper order is usually given by Oracle anyway. The DBA_SOURCE view shows source code as of the most recent compile, so if you had a valid procedure in your database, recompiled it and made it invalid, you now have the invalid version in DBA_SOURCE, not the valid one.

Another important view in the dictionary is DBA_ERRORS. It contains the most recent set of errors produced by code compilations for all users in the database. This view works best in conjunction with a tool to assist you with debugging, such as Procedure Builder. If you are not using a debugging tool to help with PL/SQL code compilation, you should have two sessions open during your compilation. The first session is used to perform the compile and the other to read the DBA_ERRORS table to ensure you are able to use the line number provided by DBA_ERRORS to find your error.

Another important view in the data dictionary that you will use when you schedule PL/SQL process execution through the DBMS_JOB package (covered in chapter 9). The view is called DBA_JOBS. This is important for batch scheduling and is used as an alternative to processing batch jobs via operating system scripts. Sometimes when using this view you get an error stating that there is a buffer overflow, which prevents you from obtaining the data in this view. To correct, you can issue the set ARRAYSIZE 10 (or smaller) and set MAXDATA 20000 (or greater, max. 32767) statements.

Oracle PL/SQL provides you with interesting opportunities to extend basic security functionality though the use of your application. To create a PL/SQL block, you need the create procedure system permission, and to run a named PL/SQL block, you must have execute permissions on that block. The chapter discussed that, if your procedure executes a database SQL statement, it is your obligation as owner of the procedure to ensure you have the required permission to execute the SQL statement, not the user executing your statement. All the user of your procedure needs is the privilege to execute the PL/SQL block.

On face value, it may seem that this is a security risk – after all, having people who don’t have permission to perform certain SQL statements directly performing them under someone else’s database access permissions sounds like you’re sneaking around something, right? In reality, this feature actually allows you more flexibility in maintaining database security by allowing users only the access to the database they need via the application. Thus, for example you can give user ATHENA the ability to perform a certain select statement on the database by allowing her to execute select_emp( ) instead of issuing a more broad grant select on EMP to ATHENA statement. Using PL/SQL applications to limit data access in this way gives the developer broad discretion in determining which users are allowed to see what data.

Procedure dependency is the other big topic this chapter covers. The chapter identifies two types of dependency in the Oracle database, procedure and object dependency. Procedure dependency is the reliance on usability a procedure has on the procedures or functions it calls. The example in the chapter was proc_today_tran( ), a procedure in a financial application that performs transactions in international currencies. It calls convert_money( ) to perform the necessary conversions. This procedure proc_today_tran( ) then has procedural dependency on convert_money( ), such that if convert_money( ) gets dropped or invalidated for any reason, proc_today_tran( ) will become invalid as well.

Object dependency is the reliance on underlying database objects like tables, views, sequences, and other things that a PL/SQL block may have in order to function properly. The example offered from the chapter was again, the financial application. Within that application, you have the convert_money( ) function, that uses exchange rates stored in the database as part of the EXCH_RATE table. If this table goes away, you lose the ability to use convert_money( ) as well.

Oracle allows you the ability to track procedural dependencies with the data dictionary. A dictionary view called DBA_DEPENDENCIES stores the names of both PL/SQL blocks with procedure and object dependencies and the names of objects they depend on. This view allows you to find the dependencies your application has quickly and easily. Another set of views called DEPTREE and IDEPTREE offer information about the direct and indirect dependencies on a database.

If you need to make changes to one area of the application or database, such as dropping and re-creating a table, you will know where to look to find out what packages, procedures, or functions that act may have invalidated. These features help you prevent the situation on your database where you allow user access to Oracle after dropping and recreating the table without recompiling the dependent PL/SQL blocks.

Several activities, then, will cause your PL/SQL program to become invalid. One is the invalidation of a program unit on which this program was dependent. The other is a change to the database objects it works with. Any change to the database applies, including a drop statement (as in drop table or drop sequence) or an alter statement. It does not matter if your alter table statement is simply adding new columns that will never even be used by the application – the application component is still invalidated, anyway.

If a PL/SQL block does become invalid, the process for making the application valid again must happen according to the procedure and object dependencies that exist on the database. Referenced objects must always be made valid first, followed by the referring object, in order to prevent the need for recompiling unnecessarily. Usually, the database objects like the underlying tables will need to be fixed first, followed by the PL/SQL blocks with only object dependencies on which other blocks have procedure dependencies. Finally, the referring PL/SQL blocks with those procedure dependencies can be compiled, after which all components of the Oracle database application should be valid and ready for use.

The mechanism and method used by Oracle for determining procedure and object dependency is called the timestamp method, or timestamping. The timestamp for a database object is stored in the data dictionary, and can be found in the DBA_OBJECTS table. This timestamp is stored and displayed in a specific date and time format, in the TIMESTAMP column. Though stored in the DBA_OBJECTS view as a CHAR(75) datatype, the format for timestamps corresponds to ‘YYYY-MM-DD:HH24:MM:SS’ in DATE formatting terms. Using the timestamp method, PL/SQL database objects that depend on availability of other objects will be valid so long as the referring object’s timestamp is younger than the timestamp of the referenced object. If the referenced object’s timestamp is older, then the referring object will not be valid and will need to be recompiled.

Timestamping is an acceptable method for procedure dependency enforcement on the Oracle database when only one database is in use by the application. However, situations arise in use of distributed databases and the client/server application model that preclude use of timestamping to enforce procedure dependency in those cases. Though the timestamping method is always used in the case of database objects like tables, you can use alternatives to timestamping for enforcing procedure dependency in the Oracle database. Reasons not to use timestamping include situations where you have stored procedures on one database that reference stored procedures on another database via remote procedure calls through a database link. Another situation that your use of timestamping may not work well is when you have client-side PL/SQL programs that are stored on the client in executable form. If you recompile your server-side stored procedures, your client PL/SQL will be invalid the next time the user requires the procedure dependencies by way of the remote procedure calls. Since the source is not available to recompile, the user is stuck until they can get a new executable.

The signature method for procedural dependency enforcement can be an alternative. Using the signature method allows your PL/SQL on the client or on the local database to remain valid even when you execute certain types of recompilation on the remote PL/SQL block. A signature consists of several items correlating to the specification of the procedure or function. A specification is basically all the source code in a procedure or function that precedes the is or as keyword of the main execution section of the block. The name of the PL/SQL block is part of the signature, along with the parameters of the block, their types, and the direction of a parameter, like in, out, or in out, and the return datatype if the PL/SQL code block is a function. Items not part of the signature that are part of the specification include the name of the parameter and default value for the parameter. By definition, the body of any PL/SQL block is not considered part of the signature.

If signature methods are used on the database for enforcing procedure dependency, then only a PL/SQL compilation that alters the signature on the remote block will invalidate local PL/SQL blocks. Changing parameter default values, explicitly stating a parameter is an in parameter when it has already been defined that way implicitly (ie, the in keyword was omitted), or "minor" changes to parameter datatype will not alter the signature. Changing the procedure body will never invalidate a signature unless the number of parameters the PL/SQL block accepts is also changed.

Items that will invalidate a signature include adding parameters, "major" changes to datatype in the specification, and changing the name of a code block. When using packages, if you add new procedures and functions to the package, you will not invalidate signatures on other code blocks in the package so long as you add your new block at the end of the package specification and body. If you add the code somewhere in the middle or at the beginning, every code block appearing after the new one will need its signature recreated.

Some tips about major and minor changes to datatypes. A major change to a parameter datatype includes any change that significantly alters the type of information the parameter will house. For example, a change from VARCHAR2 to ROWID or CHAR to CHARACTER is not significant, but a change from CHAR to DATE is. The final determinant for a minor or major datatype change rests with Oracle’s grouping of like datatypes in PL/SQL. The chapter content indicates the major datatype groups, the Two Minute Drill lists each datatype in PL/SQL and what group it falls under.

To set up Oracle for use of signature method for procedure dependency enforcement, you must do one of three things. Your DBA can change the REMOTE_DEPENDENCIES_MODE initialization parameter from timestamp to signature in the init.ora file for the Oracle database and shutdown and restart the database. Alternately, your DBA can issue the alter system set REMOTE_DEPENDENCIES_MODE = signature as a user with alter system privileges. After one of these operations is complete and the signature method is being used, you then recompile all PL/SQL on the database that you want to have a signature attached to it. You as an individual developer can use the alter session set REMOTE_DEPENDENCIES_MODE = signature to change your own session to use the signature method for enforcing constraints. You then recompile the PL/SQL blocks you own that you want Oracle to enforce procedure dependency using the signature method.