Oracle PL/SQL Best Practices

Name

DAT-09: Remove unused variables and code.

Synopsis

You should go through your programs and remove any part of your code that is no longer used. This is a relatively straightforward process for variables and named constants. Simply execute searches for a variable’s name in that variable’s scope. If you find that the only place it appears is in its declaration, delete the declaration and, by doing so, delete one more potential question mark from your code.

There’s never a better time to review all the steps you took, and to understand the reasons you took them, than immediately upon completion of your program. If you wait, you will find it particularly difficult to remember those parts of the program that were needed at one point, but were rendered unnecessary in the end. “Dead zones” in your code become sources of deep insecurity for maintenance programmers.

You should also leverage tools that will perform this analysis for you, such as RevealNet’s PL/Formatter.

Example

The following block of code has several dead zones that could cause a variety of problems. Can you find them all?

CREATE OR REPLACE PROCEDURE weekly_check ( isbn_in IN book.isbn%TYPE, author_in IN VARCHAR2) IS l_count PLS_INTEGER; l_counter PLS_INTEGER; l_available BOOLEAN; l_new_location PLS_INTEGER := 1056; l_published_date DATE := SYSDATE; BEGIN l_published_date := te_book.published_date (isbn_in); IF ADD_MONTHS (SYSDATE, -60) > l_published_date THEN review_usage; ELSIF ADD_MONTHS (SYSDATE, -24) > l_published_date ...