Get It Right with the Error Directive

Question:

I often find myself working on multiple program units, fixing some and developing others anew. As I move around between them, I sometimes have to leave work half finished. What do you think is the best way to indicate that this program unit is incomplete and keep track of what needs to be done?
Answer:
This question was answered in the July/August 2006 issue of Oracle Magazine.
Include to-do lists in your program source code!
You could come up with a standard comment indicating "incomplete, please finish"—something like this:
/* INCOMPLETE - START
Note to self: add logic to
iterate through collection
*/
.
.
.
code
/* INCOMPLETE - END */
You can then search your files for the comment indicator. One problem with this approach, however, is that your program, although incomplete, might still compile, giving you or someone else a mistaken impression of the state of that code.
It would certainly be better to be able to not only include a comment about the state of your code but also stop it from compiling, so that you are immediately and unambiguously informed about the state of your program. You can do this with the conditional compilation error directive, $ERROR.
Here is an example. I am writing a program to parse a delimited string into a collection. I can't finish the final SUBSTR operation at this time and want to remind myself about it later. So I use the $ERROR directive to remind me of the task and also use the special inquiry directives, $$PLSQL_UNIT and $$PLSQL_LINE, to show the name of the program unit and the line number, when I try to compile the program.
Listing 1 shows the list_to_collection function and the results of attempting compilation.
Code Listing 1:_LIST_TO_COLLECTION function and attempted compilation

SQL> CREATE OR REPLACE FUNCTION list_to_collection (
2 string_in IN VARCHAR2
3 , delimiter_in IN VARCHAR2 DEFAULT ','
4 )
5 RETURN DBMS_SQL.varchar2a
6 IS
7 l_next_location PLS_INTEGER := 1;
8 l_start_location PLS_INTEGER := 1;
9 l_return DBMS_SQL.varchar2a;
10 BEGIN
11 IF string_in IS NOT NULL
12 THEN
13 WHILE ( l_next_location > 0 )
14 LOOP
15 -- Find the next delimiter
16 l_next_location :=
17 NVL (INSTR ( string_in, delimiter_in, l_start_location ), 0);
18
19 IF l_next_location = 0
20 THEN
21 -- No more delimiters, go to end of string
22 l_return ( l_return.COUNT + 1 ) :=
23 SUBSTR ( string_in, l_start_location );
24 ELSE
25 $ERROR
26 'list_to_collection INCOMPLETE!
27 Finish extraction of next item from list.
28 Go to ' || $$PLSQL_UNIT || ' at line ' || $$PLSQL_LINE
29 $END
30 END IF;
31 l_start_location := l_next_location + 1;
32 END LOOP; 33 END IF;
34 RETURN l_return;
35 END list_to_collection;
36 /

Warning: Function created with compilation errors.

SQL> SHOW ERRORS
Errors for FUNCTION LIST_TO_COLLECTION:

LINE/COL ERROR
-------- ---------------------------------------------------------------
25/13 PLS-00179: $ERROR: list_to_collection INCOMPLETE!
Finish extraction of next item from list.
Go to LIST_TO_COLLECTION at line 28
The error directive is, by the way, handy under all sorts of circumstances. Suppose, for example, that I would like to make sure that a particularly complex and compute-intensive program is always compiled with the maximum optimization level. I can simply include a selection directive that checks the value of the PL/SQL compilation parameter with the $$PLSQL_OPTIMIZE_LEVEL ccflag, combined with the error directive, in the definition of the program unit, as shown in Listing 2.
Code Listing 2: COMPUTE_INTENSIVE_PROGRAM

SQL> CREATE OR REPLACE PROCEDURE compute_intensive_program
2 IS
3 BEGIN
4 $IF $$PLSQL_OPTIMIZE_LEVEL <> 2
5 $THEN
6 $ERROR
7 'compute_intensive_program must be compiled with maximum optimization!'
8 $END
9 $END
10 NULL; -- Lots and lots of code here...
11 END compute_intensive_program;
12 /

Warning: Procedure created with compilation errors.

SQL> SHOW ERRORS
Errors for PROCEDURE COMPUTE_INTENSIVE_PROGRAM:

LINE/COL ERROR
-------- -----------------------------------------------------------
6/4 PLS-00179: $ERROR: compute_intensive_program must be compiled
with maximum optimization!
Finally, you should consider using the error directive to catch "case not found" situations. Listing 3 shows an example. It relies on the ALTER SESSION command to set the value for a ccflag, which can then be referenced within a selection directive.
Code Listing 3: Using the error directive to catch "case not found"

SQL> ALTER SESSION SET PLSQL_CCFLAGS = 'current_user_type:1'
2 /

Session altered.

SQL> CREATE OR REPLACE PACKAGE user_types
2 IS
3 administrator CONSTANT PLS_INTEGER := 1;
4 enduser CONSTANT PLS_INTEGER := 2;
5 END user_types;
6 /

Package created.

SQL> CREATE OR REPLACE PROCEDURE show_info
2 IS
3 BEGIN
4 $IF $$current_user_type = user_types.administrator 5 $THEN
6 DBMS_OUTPUT.PUT_LINE ('Administrator!');
7 $ELSIF $$current_user_type = user_types.enduser
8 $THEN
9 DBMS_OUTPUT.PUT_LINE ('End user!');
10 $ELSE
11 $ERROR 'Current user type of ' || $$current_user_type || ' is not known.' $END
12 $END
13 END show_info;
14 /

Procedure created.

SQL> ALTER PROCEDURE show_info COMPILE
2 PLSQL_CCFLAGS = 'current_user_type:0'
3 REUSE SETTINGS
4 /

Warning: Procedure altered with compilation errors.

SQL> show errors
Errors for PROCEDURE SHOW_INFO:

LINE/COL ERROR
------- ----------------------------------------------------------
11/4 PLS-00179: $ERROR: Current user type of 0 is not known.