Chapter 7

Debugging PL/SQL Procedures and Functions

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

• Methods of debugging server-side PL/SQL
• Debugging PL/SQL with Procedure Builder
• Using Oracle-supplied packages for debugging

Development of code often requires debugging. Problems can occur in compilation, due to syntax errors. In addition, problems may occur later with the programming logic. Without careful debugging and testing, code gets released to users that doesn’t satisfy their needs. Without good debugging tools, your task in compiling and ensuring stable PL/SQL code products is difficult. This chapter will cover several topics about PL/SQL debugging. The tools available will be covered, including those on the Oracle database and within Procedure Builder. The special role of Oracle-supplied packages in the debugging process will also be covered. Knowing how to perform this activity is important, however OCP doesn’t test this area extensively. The contents of this chapter cover 5% of OCP test subjects.

Though mentioned already, the DBMS_OUTPUT package is the topic of this section. It is an important package with many uses. Oracle incorporated this package after Oracle6 in order to help PL/SQL developers receive better output from their code. Prior to its introduction, debugging involved populating temporary tables. Fortunately, PL/SQL development has come a long way since those days. Its use is effective in the debugging of server-side PL/SQL code.

For server-side PL/SQL developers, DBMS_OUTPUT was a godsend. No longer did you have to write all variable information in your server-side PL/SQL to a temporary table for debugging. Instead, Oracle allowed you to generate some text output to the command prompt to tell you what was happening inside your code. Consider the following situation. You’re working with a function that has many variables to hold different values in a complex transaction. The transaction doesn’t do what you wanted it to, but you don’t know where it is falling apart. To revise code on the function such that it will insert rows of data into a temporary table takes a great deal of code and server activity overhead. You add the burden of an insert statement every time you anticipate the function will change a variable and/or step through a particular area. You must also include the name of the variable, and information about the location within the function or a time/date stamp. Add to this the fact that you will need to create the table to hold debugging data from the transaction-in-progress, and a report generation script, and suddenly debugging your PL/SQL code looks like a real pain. DBMS_OUTPUT changes all that in much the same way that the printf( ) procedure helps a C programmer debug her code.

There are six procedures in this package, along with type definitions to support them. These procedures and functions are used to help you with code debugging. The contents of this package are listed and described here.

These two procedures are for use in other PL/SQL program units so that the unit can actually write output. The enable( ) procedure enables or allows the block to access a buffer for writing output at the prompt within the SQL*Plus session. This procedure accepts one number, used to determine the size of the buffer allocated. The call to the disable( ) procedure disallows your PL/SQL block to use the output buffer, and it accepts no parameters. The calls to both disable( ) and enable( ) are demonstrated in the following code listing from a procedure called elaborate_calc( ).

CREATE PROCEDURE elaborate_calc (
p_var1 IN NUMBER,
p_var2 IN NUMBER,
p_var3 IN VARCHAR2
) IS
my_var4 NUMBER(10);
my_var5 VARCHAR2(30);
BEGIN
DBMS_OUTPUT.ENABLE(800000);
...
DBMS_OUTPUT.DISABLE;
END;

The put( ) procedure is used by the developer’s PL/SQL to put information into the output buffer. This procedure points out an interesting concept in PL/SQL that will come more into play when you cover packages. The concept is called overloading, and its meaning is as follows. Several procedures or functions are called the same thing, and even perform the same functionality. The difference is that the parameters each accepts are of different datatypes. One version of put( ), for example, accepts a variable of type NUMBER, while another accepts a variable of type DATE. The other procedure, put_line( ), allows the developer to put VARCHAR2 data as well as numbers and dates into the output buffer, and it represents overloading as well. The following code block illustrates some calls to both procedures.

BEGIN
...
dbms_output.put(‘06-JAN-2000’);
dbms_output.put(56948954);
dbms_output.put_line(‘The cat ate a hat and sat on a mat.’);
END;

The get_line( ) procedure fetch data from the output buffer and return it in the variable passed as a parameter. The get_line( ) procedure is not overloaded, it only accepts variables of VARCHAR2 datatype. Thus, the get_line( ) procedure converts NUMBER and DATE data into VARCHAR2 upon retrieval. The get_line( ) procedure accepts two out parameters, the line to be taken out of the output buffer and the other a status, which identifies if get_line( ) was successful, and if so are there no more lines. Use of these procedures requires variable declaration, so ensure to pass a variable for each. An example for syntax is illustrated in the following anonymous PL/SQL block.

DECLARE
my_line VARCHAR2(255);
my_status NUMBER(10);
BEGIN
...
dbms_output.get_line(my_line, my_status);
END;

A call to procedure get_lines( ) obtains more than one line from the output buffer. There are two variables passed as parameters. Their types are CHARARR and NUMBER. The CHARARR type is a special datatype defined in the DBMS_OUTPUT package, a table of VARCHAR2 datatypes. To use the get_lines( ) procedure, you must declare a variable using this CHARARR type. The second variable is a number that defines how many lines of data will be retrieved in the call to get_lines( ). The overall use of this procedure is illustrated in the following PL/SQL block.

DECLARE
my_lines DBMS_OUTPUT.CHARARR;
my_num NUMBER;
BEGIN
...
dbms_output.get_lines(my_lines,my_num);
END;

The new_line( ) procedure puts a "carriage return line feed" character into the output buffer. In C, this character is represented as "\n", while in SQL statements you can simulate newline character behavior with chr(10), as in select …|| chr(10) from table_name. There are no variables or data to pass as parameters. The PL/SQL block illustrates.

BEGIN
...
dbms_output.new_line;
END;

So much for the facts on DBMS_OUTPUT, now for how to use them on your elaborate PL/SQL debugging needs. There are two ways to do it. The first is to have the PL/SQL block show output depending on how far processing gets in it. This process involves putting put_line( ) calls into the block at appropriate intervals. The second method is to have calls to put_line( ) that display the value in important variables at different times. The following code block shows use of both techniques. Notice, however, that the calls to procedures in the DBMS_OUTPUT package are not too fancy. You can use those procedures in ways more elaborate than shown here, but this procedure shows "bread and butter" usage that should suffice in most cases.

CREATE PROCEDURE elaborate_calc (
p_var1 IN NUMBER,
p_var2 IN NUMBER,
p_var3 IN VARCHAR2
) IS
my_var4 NUMBER(10);
my_var5 VARCHAR2(30);
BEGIN
DBMS_OUTPUT.ENABLE(800000);
DBMS_OUTPUT.PUT_LINE(‘Beginning procedure elaborate_calc.’);
DBMS_OUTPUT.PUT_LINE(‘Transform_var: p_var1 = ’ || to_char(p_var1));
DBMS_OUTPUT.PUT_LINE(‘Transform_var: p_var2 = ‘ || to_char(p_var2));
DBMS_OUTPUT.PUT_LINE(‘Calling procedure transform_var’);
my_var4 := transform_var(p_var1,p_var2);
DBMS_OUTPUT.PUT_LINE(‘Back from function transform_var’);
DBMS_OUTPUT.PUT_LINE(‘my_var4 = ‘ || to_char(my_var4));
DBMS_OUTPUT.PUT_LINE(‘Compare my_var4 to 5000, if bigger change’);
IF my_var4 > 5000 THEN
DBMS_OUTPUT.PUT_LINE(‘Now adding p_var1 and p_var2 and placing in my_var4’);
my_var4 := p_var1 + p_var2;
END IF;
DBMS_OUTPUT.PUT_LINE(‘my_var4 = ‘ || to_char(my_var4));
DBMS_OUTPUT.PUT_LINE(‘Begin changing text strings’);
my_var5 := ‘Chagall is a french artist.’;
DBMS_OUTPUT.PUT_LINE(p_var3 = ‘ || p_var3);
DBMS_OUTPUT.PUT_LINE(my_var5 = ‘ || my_var5);
DBMS_OUTPUT.PUT_LINE(‘Concatenating text strings now’);
my_var5 := CONCAT(p_var3, my_var5);
DBMS_OUTPUT.PUT_LINE(‘Done, my_var5 now: ’ || my_var5);
DBMS_OUTPUT.PUT_LINE(‘End of procedure elaborate_calc.’);
DBMS_OUTPUT.DISABLE;
END;

As you can see, even a simple procedure like this one will need many lines of code added to support debugging with DBMS_OUTPUT. However, it still is a lot simpler than inserting records into temporary tables for debugging. Consider the output from this procedure, shown using SQL*Plus. In the following block, you will see the set serveroutput on statement, which allows you to see the information coming back from Oracle using the DBMS_OUTPUT package. Also, assume the source code for the procedure above is stored in a file called elaborate_calc.sql, and invoked from SQL*plus as was covered in the last chapter.

SQL> set serveroutput on;
SQL> @elaborate_calc
procedure successfully created.
SQL> execute elaborate_calc(123,456,’I like french art.’);
Beginning procedure elaborate_calc.
Transform_var: p_var1 = 123
Transform_var: p_var2 = 456
Calling procedure transform_var
Back from function transform_var
my_var4 = 549584
Compare my_var4 to 5000, if bigger change
Now adding p_var1 and p_var2 and placing in my_var4
my_var4 = 579;
Begin changing text strings
p_var3 = I like french art.
my_var5 = Chagall is a french artist.
Concatenating text strings now
Done, my_va5 now: I like french art. Chagall is a french artist.
End of procedure elaborate_calc.

PL/SQL procedure completed successfully.
SQL>

The output from the calls to put_line( ) give you enough information to find out how the procedure runs and what values are stored in variables at different times. Had the procedure failed during execution, you would know the line it failed on depending on what output was produced and what output was not produced. The nice thing about coding PL/SQL with calls to procedures in the DBMS_OUTPUT package is that you can turn off output simply by specifying set serveroutput off or not specifying it to be on. This allows you to have the debugging feature available in your code, but not actually used at all times.

Another package is available on the Oracle server for debugging the runtime variable stack. This package, called DBMS_DEBUG, contains several different type declarations, constants, procedures and functions to support runtime debugging. However, the challenge in debugging code in runtime is having an interface that helps you understand all that is happening in Oracle while your PL/SQL runs. The next section will explain how Procedure Builder can help you with this task.

In this section, you will cover the following points about debugging PL/SQL with Procedure Builder:

• Finding errors in code compilation

• Using breakpoints to debug PL/SQL
• Using debug triggers for debugging code execution

Although the process of debugging server-side PL/SQL is simplified greatly with the use of the DBMS_OUTPUT package, the previous section glosses over a point made earlier about compiling your PL/SQL using the database and SQL*Plus. Procedure Builder provides functionality that supports debugging in a much stronger way than DBMS_OUTPUT, and provides much better support for debugging compilation errors. To illustrate, you will revisit the PL/SQL code for procedure elaborate_calc( ). There are several features in Procedure Builder that help the developer work with procedures to discover problems at compile and execution time.

Writing code in Procedure Builder’s program unit editor module allows you to compile against a PL/SQL engine right in the application. This functionality gives you PL/SQL to use on the client side, which is useful for developing Forms and Reports in Developer/2000. When you compile the code and there are errors, Procedure Builder allows you to view your code using the program unit editor module and press the compile button to compile the code. If Oracle doesn’t like what you wrote, it points out the errors it encountered in a separate area below the original source code, making it easy for you to find the errors in your code and correct them. This functionality is available for when you write code on the server side as well. Procedure Builder obtains the compilation errors from the ALL_ERRORS table and ports them directly to the stored program unit editor so that you can find the problems and resolve them easily.

Figure 7-1 demonstrates use of the program unit editor module to correct compilation problems. Once Procedure Builder identifies your problem, you can correct it with relative ease. As soon as you correct your code, you can recompile the code by pressing the compile button. Once your code compiles properly, the lower window opened to show compilation errors as demonstrated in figure 7-1 goes away. When this occurs, you may save your code by pressing the close button at the top of the interface. You now have debugged your client-side code.

Finding errors in your server-side code is slightly different. Recall that to develop server-side PL/SQL code, you must use the stored program unit editor module in Procedure Builder. First, connect to the Oracle database by double clicking on the Database Objects node in the object navigator or choosing the File>> Connect menu option, and then enter the user, password, and connect string information. Once connected, you open the stored program unit editor by clicking on program>>stored program unit editor menu selection, or simply go to the expanded node in the object navigator module and double click on the icon in front of the name of the program unit. You can then open the stored procedure you wish to edit by pulling down the list of available stored procedures on the right-hand top of the stored program unit editor module, scrolling down until you find your procedure, and then clicking on the procedure you wish to edit. These operations should be familiar to you from the last chapter. Alternately, you can create a new stored procedure or function by clicking on the new button in the module. You should be familiar with the process for creating new PL/SQL blocks using this interface as well. If not, take a moment to review appropriate discussions of creating stored procedures with Procedure Builder in chapter 6.

Once your procedure is complete, you compile it. However, again recall from chapter 6 that there is no compile button in Procedure Builder’s stored program unit editor module. Thus, you use the save button instead. This step causes the stored program unit to attempt creation of your new PL/SQL block. If there is an error from the database with the source code, PL/SQL will display the error in a new bottom window in the module. Thus, it is as easy to debug compilation of stored program code as it is to debug the client code’s compilation. Compare this method to debugging compilation errors using one or even two SQL*Plus sessions and select * from user_errors statements. Procedure Builder shows its ease of use in this area over SQL*Plus. Figure 7-2 illustrates the similarity between the two modules in debugging compile-time errors.

Once your code is compiled, there are plenty of other ways to find problems with code execution. These methods include setting up breakpoints in the code, steps, and other important features. The debugging methods described here are listed under the debug menu in the PL/SQL interpreter module. Figure 7-3 shows where the menu is and illustrates the features available when the menu is pulled down. If you do not see the menu on your window, make sure the PL/SQL interpreter module is in the foreground of the application.

The debugging process in Procedure Builder consists of several methods. For your purposes, you will debug a client-side program unit that calls procedures stored in a PL/SQL library called LIB_020, and it contains the procedures elaborate_calc( ) and foomanchoo( ). The following code blocks show the definition for both procedures in library LIB_020.

After attaching your LIB_020 library, you create a new standalone program unit that calls your procedures and functions in that library. This procedure is called call_procs( ). After creating it, you bring up your new PL/SQL block in the upper window of the PL/SQL interpreter module. The following code block indicates its definition as it would appear in the program unit editor module.

PROCEDURE call_procs IS
BEGIN
foomanchoo;
elaborate_calc(123,'add');
END;

The first tool at your disposal when debugging code in Procedure Builder is a breakpoint. To create one is a simple task, just double-click on the line number you wish the breakpoint to occur in the source code display window of the PL/SQL interpreter module. Notice what happens after you do so – in the bottom of the object navigator module, there is an item you can drill into called Debug actions. If you drill down into it, you see that there is a break point item corresponding to the break point you just created. Below that is another item you can drill into called Stack. When you execute your code by calling procedure call_procs( ), the PL/SQL interpreter runs through the procedure until it encounters the breakpoint you created. There are several new items below the stack drill-down corresponding to the procedure running, and there is a new prompt in the interactive lower window of the PL/SQL interpreter. Figure 7-4 demonstrates the call_procs( ) procedure, shown in the PL/SQL interpreter, with your breakpoints defined. Those breakpoints are also displayed in the object navigator module in that figure as well. The processing already handled in the PL/SQL interpreter module is shown in the lower window of that module as well in the figure.

Observe the new prompt in the PL/SQL interpreter – control is now passed back to the interpreter, allowing you to run the program you just developed interactively. In addition, you can observe values in the variables on the procedure stack using the appropriate drill-down menu. Now, notice back in the source code for elaborate_calc( ) procedure a type mismatch in the variables passed as arguments p_var1 and p_var2 and the declared variable used to hold the result, p_var3. In the procedure call_procs( ), you are currently stopped at a breakpoint after procedure foomanchoo( ), which simply writes the line "I am foo, who are you." To continue execution of this procedure, you must press the go button, the one with the yellow lightning bolt in the PL/SQL interpreter window. This act will cause the PL/SQL interpreter to execute your code either until the end, the next breakpoint, or until it encounters an error. In this case, you will encounter an error due to the type mismatch and the values each argument variable contains. Figure 7-5 demonstrates.

The point of this lesson is to illustrate a simple use of breakpoints. Once a breakpoint is created, it is available in the object navigator until you close or remove the program unit, PL/SQL library, or attached library. Another method for debugging PL/SQL processes is to create a debug trigger, which will execute every time the interpreter encounters a breakpoint. Several Procedure Builder built-in packages are available from Oracle to assist you with debugging your PL/SQL code. These packages are available for use in client-side PL/SQL debugging, and will be covered later in this section.

You can define a set of steps Procedure Builder will execute when it encounters a breakpoint by creating a debug trigger. To create a debug trigger, you must first create the breakpoint in the procedure. This process is already defined. After doing so, you must bring the PL/SQL interpreter module to the front of the Procedure Builder screen, making it the active module. This changes the menu bar of the application to include your debug menu. Click on debug>>trigger on the menu bar to bring up the PL/SQL trigger interface for creating debug triggers. Note that these triggers are slightly different from database triggers, to be covered later on in Chapter 10.

Debug triggers can be executed at three different times. Those times are at a specific line of PL/SQL code, at every breakpoint, or every statement in the procedure or function. Triggers are fired before the PL/SQL interpreter executes the line of code. A debug trigger can be used in many ways, particularly for creation of conditional breakpoints, where the breakpoint only happens when a certain condition exists in the PL/SQL code.

For this discussion, we will use the following PL/SQL code body to a slightly modified version of the procedure elaborate_calc( ), as shown in the following code block. Also shown in the block is a function, transform_var( ), which simply adds together two variables you pass it. Assume that you are having some problems with the execution of this code, and you want to inspect the values of variables in the procedure as it executes.

PROCEDURE elaborate_calc(
p_var1 IN NUMBER,
p_var2 IN NUMBER,
p_var3 IN VARCHAR2
) IS
my_var4 NUMBER(10);
my_var5 VARCHAR2(30);
BEGIN
my_var4 := transform_var(p_var1,p_var2);
IF my_var4 > 5000 THEN
my_var4 := p_var1 + p_var2;
END IF;
my_var5 := 'Chagall is a french artist.';
my_var5 := CONCAT(p_var3, my_var5);
END;

FUNCTION transform_var (
p_var1 in number,
p_var2 in number
) RETURN number IS
BEGIN
return (4999);
END;

Now examine the PL/SQL trigger interface, shown in figure 7-6. First, the line at the top that reads trigger: new indicates you are creating a new debug trigger. Had you been modifying an existing trigger, this line would identify the number of that existing trigger. A checkbox is off to the right, telling you whether the trigger is enabled or disabled for use. The next important point about this interface is the location pull-down menu. There are three potential source locations for debug triggers, the program unit, debug entry, or every statement. If debug entry is selected, then the trigger will be fired every time a breakpoint is encountered. If every statement is selected, then the trigger will fire before Procedure Builder executes every statement in the program. For every location except program unit, the program unit name and line boxes are grayed out. The last important area is the trigger body entry box, where the trigger code is placed. Don’t worry about coding an explicit begin or end; these details are handled automatically.

Consider now the actual process of developing a trigger. Let’s say you want to create a debug trigger that will fire before the program enters the if then statement in elaborate_calc( ). To do this, you create your breakpoint in the PL/SQL interpreter window on line 10, then click on debug>>trigger, pulling up the debug trigger interface. You will create a program unit trigger, to occur on line 10. If you review figure 7-6, you will see that the definition of the trigger is present in the trigger creation interface window as it is described here.

Creation of debug triggers touches on Oracle-supplied packages for debugging, covered more extensively in the next chapter. For now, you will use some functions in the DEBUG package to assist with creation of your debug trigger. There are two important classes of debugging functions in the DEBUG package used to obtain or change the value for a local variable in the program, called getx and setx, respectively. The character x refers to a character that represents the datatype of the variable you try to obtain, n for NUMBER, c for VARCHAR, d for DATE, or i for INTEGER. After entering the code for your trigger, you compile the trigger by clicking the ok button. For your example, the trigger body defined in figure 7-6 is shown in the following code block.

BEGIN
IF debug.getn(‘my_var4’) > 5000 THEN
text_io.put_line(‘Entering the IF statement’);

END IF;
END;

Notice a few items about this block. First, the begin and end; are added for readability, they are not actually required in the trigger body box in the trigger creation interface. Another important item to remember is in the call to getn( ), you must enclose the variable that getn( ) will obtain in single quotes or else the trigger will return an error on compile. If you do receive an error on trigger compilation, you can see what the error was by looking in the interactive PL/SQL runtime area on the PL/SQL interpreter module. Usually, if you didn’t include single-quotes around your variable name in the call to DEBUG.getx( ), the error you receive will be that your variable must be declared. Once you successfully compile your debug trigger, notice a new item included under the debug actions drill-down item in the object navigator module corresponding to the trigger.

Creating debug triggers for server-side PL/SQL code is similar to creating debug triggers for client-side PL/SQL. After connecting to the database, you bring up your stored procedure by finding it with the object navigator module and then clicking on it to view the source with the PL/SQL interpreter module. The breakpoint for server-side PL/SQL is created in the same way, by simply clicking on the line you wish to create the breakpoint. However, there is a lot more happening behind the scenes. The following code block shows the contents of the PL/SQL interpreter interactive window when you click on the line of a stored procedure to create a breakpoint on it.

PL/SQL> .break USER JASON PROGRAMUNIT CP_ACCUM_ALL_RESPS LINE 1011
Breakpoint #1 installed at line 1011 of CP_ACCUM_ALL_RESPS

Compare that to the contents of the PL/SQL interpreter interactive window after you open your library to debug a client-side stored procedure. When you click on the line of a client-side procedure to create a breakpoint on it, a great deal less information is generated by Procedure Builder.

PL/SQL> .open LIBRARY D:\ORANT\BIN\LIB_020.pll FILESYSTEM
Opening library in file D:\ORANT\BIN\LIB_020.pll...
PL/SQL> .break .
Breakpoint #2 installed at line 10 of ELABORATE_CALC

Creating a trigger at this point is accomplished on a stored procedure in the same way as on a client-side procedure. You can use all the Oracle-supplied packages such as DEBUG and TEXT_IO to do so, as was shown before. Remember, however, that debug triggers are not the same as database triggers. A debug trigger is stored within Procedure Builder, not on the Oracle database. To view the debug actions available on the database, you can drill into that item on the object navigator module.

In this section, you will cover the following points on Oracle-supplied packages for debugging:

• Overview of Oracle-supplied packages in Procedure Builder
• Packages for application component communication
• Packages for tuning, portability and compatibility
• Packages for debugging PL/SQL
• Case study of using Oracle-supplied packages for debugging

So far, a few Oracle-supplied packages have been identified and used in different aspects of client- and server-side debugging. These packages include DBMS_OUTPUT for server-side PL/SQL debugging, TEXT_IO for text output on the client side using Procedure Builder, and DEBUG for debug triggers. The use of other Oracle-supplied packages available within Procedure Builder for debugging will be covered in this section as well.

Several packages exist in Procedure Builder, supplied by Oracle. Their functionality is varied, from text output packages to packages that help with debugging, and so on. To find the packages and their contents in the Procedure Builder application, you must use the object navigator module. The drill-down item you can examine is called "Built-in Packages." This drill-down is always available in Procedure Builder, even if you are not connected to the database or have client-side PL/SQL code open or attached. Figure 7-7 shows the items that reside under this drill-down option.

These packages fall into many different categories. Some of these categories include debugging, communication between application components, and tuning portability, and backward compatibility. A final package category is packages for internal use, which will be presented as well. The following discussions will identify each package category, along with the packages that fall into that category. A brief discussion of the use of each package in the category will follow.

One of the value-added aspects of Procedure Builder is that the tool provides you with an ability to develop PL/SQL more rapidly than is available using conventional means like SQL*Plus and the Oracle database PL/SQL engine. To do so, Oracle provides some packages to help in the debugging process. The first package Oracle provides was introduced a short time ago, called DEBUG. This package helps you obtain and set values in different variables in your PL/SQL program. Another package offered by Oracle for the purpose of debugging is the TOOL_ERR package. This package contains several procedures and functions that help you manipulate the Procedure Builder error stack.

A final package offered for your benefit in this area has also been introduced, and is called TEXT_IO. This package allows you to manipulate text between the screen or a file and the application. Some usage of the TEXT_IO package suggests similarity to usage of the printf( ) command in C. In reality, this package has a great deal of flexibility for use, but since its closest counterpart on the Oracle database server side DBMS_OUTPUT is frequently used for debugging you may want to treat TEXT_IO for that purpose among others as well.

In a full-featured application, there may be many different components involved. These components may be part of the Developer/2000 product line, such as Forms or Reports, or they may be executable applications written in C. The first package that falls into this group is the DDE package. This package allows you to communicate between different components of the Developer/2000 application. Another package is the ORA_FFI package, which allows you to communicate between PL/SQL code and C programs. A final package that falls under this category is the OLE2 package, which allows you to use object linking and embedding in your PL/SQL application.

Furthermore, you may want to identify certain environment variables from your PL/SQL programs as well. There are Oracle-supplied packages that allow you to do this. The first is called TOOL_ENV. It allows you to interact with Oracle environment variables such as oracle_sid and oracle_home. The other package in this line is ORA_NLS. This package allows you to interact with national language settings.

Another important use for Oracle built-in packages is for application timing. The ORA_PROF package contains many procedures and functions that can be used to tune the application by first timing the execution of parts of the application using this package. In situations where the application must be ported to a different language, there is another package that can be useful. This package is called TOOL_RES. For backward compatibility with PL/SQL release 1, the LIST package allows you a way to create arrays.

The final area of usage for Oracle built-in packages is internal. Two packages fall into this category, the ORA_DE and STPROC packages. The ORA_DE package is used by Developer/2000 for internal purposes related to PL/SQL. The STPROC package is used by Developer/2000 to call stored procedures on the Oracle database. These calls are generated automatically by the Developer/2000 applications. Generally, you needn’t worry about the contents of these packages, therefore, they will not be covered for the remainder of the section.

Now that the package categories have been identified, the different packages that are available in Procedure Builder will be discussed in detail. In each subsection, the package contents will be identified, along with some guidelines for usage, tips, and examples. In this section, more in-depth use of certain packages for communication between application components will be covered. Turn attention now to the individual packages that comprise Procedure Builder.

The ORA_FFI package offers the ability to integrate PL/SQL programs with foreign functions written in C, as in .dll files. The general process for integrating PL/SQL programs with foreign functions is to register the external functions, then generate PL/SQL code for them. There are several types, exceptions, and functions defined in this package, listed and used as follows. The ora_ffi_error exception is for use when errors occur while using the ORA_FFI package. Several type declarations exist in this package, including funchandletype, libhandletype, and pointertype. These type declarations are used to declare variables used to store handles to external procedures and functions.

Three functions are used for registration of external program components, register_function( ) to register the function, register_return( ) to register the return values for the functions, and register_parameter( ) to register parameters passed to the functions. To locate registered components and return their handles to variables declared as the datatypes identified by the ORA_FFI package, the find_function( ) and find_library( ) functions are used. Note that you cannot use these until external programs are registered. A function can be used to determine if a variable contains a null value, called is_null_ptr( ). Once the external procedure or function is registered and loaded, the function generate_foreign( ) can be used to create PL/SQL code for use by the PL/SQL program components. Once done, you can deallocate the foreign procedures and functions with the unload_library( ) function.

Every language has its own names for general concepts, such as the days of the week, months of the year, local time format, and other character constants. Furthermore, there often are numeric constants to a particular language as well, such as the placement and character used to define currency format. The first set of functions belies the American origin of Oracle products. One is american( ), which returns a TRUE or FALSE value based on whether the current NLS character set is American english. The second is american_date( ), which returns TRUE or FALSE for whether the current date format corresponds to American custom.

The next set of functions demonstrates how to obtain information about the current language corresponding to character or numeric constant. These functions include get_lang_scalar( ) for numerics, and get_lang_str( ) for strings. Several of these functions return boolean values in response to implied questions about the current language, such as modified_date_fmt( ) or whether the data format has been altered, and right_to_left( ) or whether the current language is read from right to left. Also, linguistic_collate( ) is used to decide if standard sorting methods will yield a proper sort order according to the rules of the language, linguistic_specials( ) is used to determine if special cases exist and in place for this language, and the simple_cs( ) function, which determines if the current language is simple, meaning it is a single-byte language with no special characters or sort order required. The single_byte( ) function determines if the current character set is single or multi-byte. Finally, several exceptions are part of the package, including bad_attribute, no_item, and not_found.

This package is very simple – only one procedure is part of it, called getvar( ). To use it, you pass the name of the environment variable you want and a variable to hold the environment value.

This is a fairly large package used to support the creation and usage of OLE2 objects such as spreadsheets, documents and presentations produced by third-party vendors. As with the other built-in packages, there are type declarations and exceptions to support the work, along with procedures and functions that actually do the work. In this case, the types available are list_type and obj_type, which allow you to declare variables that hold argument list and OLE2 object handles. The exceptions include OLE_error (that’s OH-EL-EE, not ‘ole!), which is raised if an error occurs in another procedure or function on the OLE2 package, and the OLE_not_supported exception, raised when object linking and embedding is not supported by the platform. A function that works in conjunction with the OLE_error exception is called last_exception( ), and it returns the message of the last error raised in the OLE2 package. Another function working in conjunction with the OLE_not_supported exception is the issupported( ) function, which returns TRUE if object linking and embedding is supported by the platform, FALSE if it is not.

The rest of this package contains procedures and functions related to the invoking, linking and embedding of program objects into PL/SQL program units. To create a handle for PL/SQL to refer to the OLE2 object, the create_obj( ) function is used. Each object can be passed argument lists, which are created with the create_arglist( ) package. To add arguments to created lists, the add_arg( ) procedure can be used. Alternately, to add object arguments to lists, the add_arg_obj( ) procedure can be used. Several procedures are available for obtaining OLE2 object properties. They are get_char_property( ), get_num_property( ), get_obj_property( ), while the procedure to set the value for a property is set_property( ). Several procedures are available for obtaining numeric, character, or object type values from the OLE2 object, they are invoke_num( ), invoke_char( ), invoke_obj( ). The object itself is invoked using the invoke( ) procedure. When OLE2 work is complete, you can get rid of argument lists and objects with the destroy_arglist( ) and release_obj( ) procedures, respectively.

This package in Developer/2000 is used to handle data interchange between the Developer/2000 application and other Windows clients. The components of the package, though not extensive, belie the complexity of use. There are quite a number of Windows-defined formats for data passed between these two applications, as well as Oracle-defined exceptions for this package’s use.

In this section, more in-depth use of certain packages for tuning, portability, and compatibility will be covered. An introduction to this area of functionality was covered in the overview. To recap, these packages include ORA_PROF, TOOL_RES, and LIST. Turn attention now to the individual packages that comprise Procedure Builder for the functionality of tuning, portability and compatibility.

This package aids in the performance tuning of PL/SQL programs in general by giving you the ability to track the time it takes a piece of code to run. This task is accomplished with timers, and ORA_PROF allows you to manage timers with relative ease. Timers are identified with names of type VARCHAR2. The first task in working with timers should be obvious – you create them. This happens with create_timer( ). You can start, stop, and reset timers with the start_timer( ), stop_timer( ), and reset_timer( ). To eliminate a timer, you can use the destroy_timer( ) procedure. To figure out the amount of time that has elapsed since the last call to reset_timer( ) for that timer, you use the elapsed_time( ) function. Finally, if any procedure or function is passed an invalid timer name, a special exception called bad_timer will be raised.

Porting applications from one language to another can prove difficult. To do so, Procedure Builder allows you to put all text information into a resource file and read from that resource file. There are several functions in this package. The functions include rfhandle( ) to allocate a handle to a resource file, rfopen( ) and rfclose( ) to open and close resource files, and rfread( ) to read the text data from resource files. Several exceptions exist in the package as well. These are bad_file_handle, buffer_overflow, file_not_found, and no_resource.

Backward compatibility is often a concern in an application. Toward this end, Oracle provides the LIST package in Developer/2000 as a holdover from PL/SQL version 1 for the creation and maintenance of lists of character strings to create arrays. There is one type definition and one exception created as part of this package. The type is called listofchar, and it defines a datatype to store a handle to a list you will create and manipulate. The exception is called fail; appropriately enough, it is raised if any other list operation fails.

The rest of the package contains definitions and code bodies for procedures and functions. The first two functions covered are make( ) and destroy( ), used to make new lists and destroy them when needed, respectively. To find out how many items are in the list, you can use the nitems( ) function. To obtain data from a list at a certain point, use the getitem( ) function. The next set of procedures are used to write data to a list. They are appenditem( ) to append items to the end of a list, prependitem( ) to prepend items to the beginning of a list, insertitem( ) to place an item in a list at any other point than the beginning or end, and deleteitem( ) to remove an item from any point in the list.

In this section, more in-depth use of certain packages for debugging will be covered. There are three, as was described in the overview. To recap, those packages are DEBUG, TOOL_ERR, and TEXT_IO. Turn attention now to the individual packages that comprise Procedure Builder’s debugging features.

The DEBUG package is one you will likely find yourself using most heavily. This package contains utilities and exceptions designed to help you to identify and resolve bugs in your PL/SQL code. Start with the exceptions. There is only one, called break. It is raised inside debug triggers explicitly in order to activate a breakpoint. A suggested usage for this exception is for conditional breakpoints, though you may think of other uses as well. Note, however, that you do not need to raise this exception explicitly in your PL/SQL program if you wish to create a breakpoint in that program, Procedure Builder handles breakpoints for you at that level.

There are four basic procedures in DEBUG, two of which have already been covered. The first two are getx( ) and setx( ), and they are used to obtain and define values in variables in PL/SQL blocks from debug triggers, respectively. Recall from earlier discussion that the x is replaced by a character to signify the datatype of the variable whose value will be obtained or set by the procedure. The allowed replacements for x in the procedure name are n for NUMBER, c for VARCHAR, d for DATE, and i for INTEGER.

The other two procedures available in Procedure Builder are designed to emulate the processes both of interactive PL/SQL interpretation and of returning control to the PL/SQL interpreter in temporary suspension of your PL/SQL code. The first procedure is called interpret( ). It executes whatever string of text you pass in with it as if it was PL/SQL. If you have functions you would like to call from your debug trigger, you can do so easily with this procedure. The other procedure is called suspend( ). It accepts no variables, merely holds execution of the current PL/SQL program, and temporarily returns control to the interpreter for debugging.

The TOOL_ERR package works in conjunction with the DEBUG package to help you retrieve more advanced error messaging from the Procedure Builder error stack. This package contains several procedures and functions, as well as a constant and an exception. The constant, toperror, refers to the newest or topmost error on the error stack for easy reference. The exception, tool_error, can be raised whenever one or more errors have been pushed onto the error stack.

The rest of the procedures and functions will now be explained. The entire error stack can be discarded with the clear( ) procedure. To find the number of errors currently on the error stack, use the nerrors( ) function. To retrieve an error code for a specific error on the stack, you use the code( ) function, passing it the number of the error you wish to see. To view the message that corresponds the code of the error you just obtained with the code( ) function, use the message( ) function. If you want to discard the top-most error on the error stack, use the pop( ) procedure.

Although its counterpart on the Oracle server, DBMS_OUTPUT, is used most commonly to supplement the functionality provided by print statements like printf( ) in C, the TEXT_IO package in Procedure Builder in reality offers far more. Several procedures and functions are available in TEXT_IO to handle writing information to and reading information from files and standard output. Some of these functions will look familiar to you, as they are similar to functions and procedures in DBMS_OUTPUT.

The first set of procedures in TEXT_IO to be covered include the procedures to open and close files and a function to test if a file is open. These procedures are fopen( ) and fclose( ), and the function is is_open( ), respectively. Related to these procedures and function is a special type for file handle declaration, called file_type. The next procedure is designed to read a line of text from the open file and is called get_line( ). This procedure accepts two parameters, a variable declared as type file_type, and a variable to hold the line of data retrieved from the file. The last set of procedures and functions are designed to place data into an open file, and those procedures include the put( ) procedure for placing information of various datatypes, new_line( ) for placing a specified number of carriage returns, putf( ) for placing a line of formatted output to the file, and put_line( ) for putting a line of text to the file.

In the previous discussion, you learned more about the contents of the TOOL_ERR, DEBUG, and TEXT_IO packages for the purpose of debugging your PL/SQL procedures and functions. This discussion will demonstrate usage of some of these processes in an actual debugging situation. For the example, assume there is a table called NAME_NUMS, that has two columns, USER_NAME and USER_NUM. This table lists the name of each user on the system and a sequential number corresponding to that user, starting with zero. The contents of the table are shown in the following code block.

SQL> select * from name_nums order by user_num;
USER_NAME USER_NUM
---------- ----------
manfran 0
flibber 1
ellison 2
howlett 3
barbara 5
anthony 6
deborah 7
spanky 8
athena 9

Now, let’s say that the numbers are generated by the application, and for one reason or another, those numbers are generated out of sequence. A stored procedure out on the database called scrub_recs( ) can be run to put those numbers into the right order. The code for this procedure is listed in the following code block.

PROCEDURE scrub_recs IS
CURSOR name_nums_crsr IS
SELECT user_name,
user_num
FROM name_nums
ORDER BY user_num;
new_user_num NUMBER := 1;
BEGIN
FOR crsr_rec IN name_nums_crsr LOOP
IF new_user_num <> crsr_rec.user_num THEN
UPDATE name_nums
SET user_num = new_user_num
WHERE user_num = crsr_rec.user_num;
END IF;
new_user_num := new_user_num + 1;
END LOOP;
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
rollback;
raise_application_error(-20001,'primary key violation on NAME_NUMS');
WHEN OTHERS THEN
rollback;
raise_application_error(-20002,'General fault on scrub_recs');
END;

Once the stored procedure is compiled on Oracle, the first step in debugging this process is to open the stored procedure up for debugging in the PL/SQL interpreter window. This is accomplished by double-clicking on the name of the procedure in the object navigator module. After doing this, you will see the source code for this procedure in the top window of the PL/SQL, while the interactive PL/SQL interpreter prompt appears in the second window. Since this code contains a for loop, it is fair game to want to see the contents of variables as the interpreter steps through the loop for each record in the cursor. The process for creating a breakpoint is simple enough, and you have enough exposure to them now to know what a breakpoint looks like in the PL/SQL interpreter window and what inserting a breakpoint does in the object navigator. What you see in your Procedure Builder session should match the contents of figure 7-8.

Once your breakpoint is set inside the loop, you will want to define your debug trigger. Recall that this is done using the debug trigger editor made available by choosing the debug>>trigger menu option and entering PL/SQL code you want to use as part of your trigger body. The following code block demonstrates the contents of the trigger body window for a simple trigger, which obtains the values both for the USER_NUM column in the cursor and the NEW_USER_NUM that it should be.

if debug.getn('crsr_rec.user_num') <> debug.getn('new_user_num') then
text_io.put_line('These records do not match!');
end if;

Now that you have created your procedure, complete with breakpoints and triggers, you are ready to analyze how Procedure Builder supports run-time debugging. From the lower window in the PL/SQL interpreter prompt, enter the name of your procedure to run, scrub_recs( ), followed by a semicolon, and press enter. You will see the following output listed in this code block.

>> Entering Breakpoint #1 line 10 of SCRUB_RECS
(debug 1)PL/SQL> .go
These records do not match!
PL/SQL>

The PL/SQL interpreter indicates it has entered the breakpoint you created. The
.go command informs you that the debug trigger you created is now going to fire. As a result of the check performed in the trigger between the value stored in your cursor for the USER_NUM column and the value in new_user_num, the trigger identifies the discrepancy and prints a warning: "These records do not match!" You may feel that it would be nice to see exactly which records don’t match, but before changing your debug trigger, remember that you have the run-time stack at your disposal, too. Looking in the stack for the procedure name will show you what the values for each variable are. The stack and debug actions you should see in the object navigator module and the contents of the PL/SQL interpreter module are in figure 7-9.

Finally, to understand how to continue debugging the execution, you need to click on the go button in the top-center of the PL/SQL interpreter module. The go button is the one with the yellow lightning bolt on it. This indicates to Procedure Builder that you’re ready to continue debugging the execution of this procedure. Again, you will enter your breakpoint, and the debug trigger fires again to show you that once again the values in the USER_NUM column of the cursor and the new_user_num variable do not match, resulting in your text output. If you look in your run-time stack in the object navigator module, you notice that your values for both have incremented by only one digit. By now, you probably realize that your debug trigger is going to fire for every iteration because the numbers in the USER_NUM column of the table and cursor started with 0 while the initialized value for new_user_num started with 1. Recall that the initial usage for scrub_recs( ) was to eliminate sequential gaps in USER_NUM, but not to change the initial value of the list to 1. At this point, you want to reset the execution of the procedure by pressing the reset button next to the go button, and modify the initialization value for new_user_num at line 10 in the procedure to be 0, not 1. The reset button is the one with the arched black arrow on it. Figure 7-10 shows the source code with the change.

After altering the code and recompiling it, you must step through the program again, to ensure it executes properly. Your debug trigger and breakpoints may have disappeared, if so, simply re-create them. Then, run scrub_recs( ). Your program execution will stop at the breakpoint indicated, but notice this time that your trigger doesn’t fire. The output in the PL/SQL interpreter window is listed in the following code block.

PL/SQL> scrub_recs;

>> Entering Breakpoint #1 line 10 of SCRUB_RECS
(debug 1)PL/SQL>

The values for both USER_NUM in the cursor and the new_user_num match this time according to the run-time stack as well. You keep stepping through the loop by pressing the go button until the debug trigger fires on user BARBARA, whose number is 5, not 4. Each step through scrub_recs( ) will set this and all subsequent values as they are supposed to be according to variable new_user_num.

This chapter focuses on debugging PL/SQL programs. There are several areas covered, including methods of debugging server-side PL/SQL using server-side packages such as DBMS_OUTPUT. Another area covered in this chapter is debugging PL/SQL with Procedure Builder. The special role of Oracle-supplied packages for debugging within the Procedure Builder software is offered as well. All told, this chapter covers 5% of OCP exam content.

The first area covered describes methods of debugging server-side PL/SQL using methods like logging onto the database with SQL*Plus. The DBMS_OUTPUT package is a set of stored procedures and functions provided by Oracle to use for debugging your own procedures, functions and packages. It is a package provided by Oracle that is stored on the database. It was introduced by Oracle to eliminate the use of temporary tables for debugging. Instead, the developer makes use of items in the package that allow for functionality similar to the printf( ) procedure in C. There are six procedures in this package. They are enable( ) and disable( ), which handle the enabling and disabling of a special output buffer for storing output messages in Oracle, respectively. Some of the other functions include put( ) and put_line( ), which put information into the output buffer. The last three procedures in DBMS_OUTPUT are get_line( ), get_lines( ) and new_line( ), which pull line or lines of text from the output buffer and add carriage returns to the current line in the buffer, respectively.

Most frequently used procedures in the DBMS_OUTPUT package include the enable( ) and disable( ) procedures, and the put_line( ) procedure. When enabled, the DBMS_OUTPUT.put_line( ) procedure behaves like a printf( ) procedure as in C. An example was provided in the chapter that illustrated the benefits of using put_line( ), but also pointed out that frequently a great deal of code will need to be added to your stored procedures to support debugging with this method. One nice benefit for using DBMS_OUTPUT for debugging is the ability to turn it on and off, however, with the disable( ) procedure, or from the SQL*Plus prompt with the set serveroutput off statement. Recall that, in order to obtain output from your calls to procedures in DBMS_OUTPUT, you must issue the set serveroutput on statement.

Debugging PL/SQL in Procedure Builder offers the same features as the DBMS_OUTPUT package, plus a whole lot more. There are many different features in Procedure Builder for debugging that were covered in the chapter, including debugging your code compilation with Procedure Builder, and using breakpoints and debug triggers for runtime code debugging. There are some subtle differences in debugging with Procedure Builder depending on your use of client-side or server-side PL/SQL code. For client-side PL/SQL, compiling is handled with the program unit editor module. Within that module, the mechanism used for code compilation is the compile button, while for coding stored procedures and functions, you would use the stored program unit editor module of Procedure Builder. In this case, the save button instead to handle your compilation and storage of the PL/SQL program in one motion. Either way, Oracle will indicate any errors immediately from within the program development module, allowing you to identify and correct the problem quickly. The equivalent method, using the alter procedure proc_name compile statement and viewing errors in the USER_ or ALL_ERRORS dictionary views is time-consuming and difficult. You need to use two sessions for debugging in order to avoid losing your statement buffer in SQL*Plus, thus rendering meaningless the line numbers provided by the error views.

Perhaps the greatest contribution to debugging Procedure Builder makes is the ability to debug your PL/SQL during run-time. Procedure Builder allows you to do this with its breakpoint and debug trigger features. A breakpoint is inserted to a procedure once it has been compiled, stored either locally or on the server, and brought up in the PL/SQL interpreter module for execution by double-clicking on the line you want procedure execution to suspend temporarily. Recall from the chapter that the breakpoint causes PL/SQL operation to cease just prior to the line on which the breakpoint has been created. Evidence of the breakpoint creation can be found in the PL/SQL interpreter and in the object navigator module under the debug actions drill-down item.

Debug triggers work in conjunction with breakpoints. There are three potential ways for you to configure your debug trigger to fire, program unit for every time a breakpoint is entered in this program unit, debug entry for every time any breakpoint is entered, and every statement for every statement Procedure Builder executes. Using the Procedure Builder debug trigger interface is covered, including the trigger body window. Important to remember with the trigger body window is that you don’t need to write an explicit begin or end, the interface handles development of these components automatically. To create a debug trigger, select the debug>>trigger menu item. You must have the PL/SQL interpreter in the foreground to use the debug menu.

Within your debug triggers, you will find yourself using some or many of the Oracle-supplied packages. Probably the most important Oracle-supplied package is called DEBUG. Again, probably the most important function in this package allows you to obtain the value of a variable in your PL/SQL program. The function is called getx( ), where x is replaceable with n, c, d, or i, for NUMBER, VARCHAR2, DATE, or INTEGER, corresponding to the datatype of the variable you want to obtain. Another important package used in debugging is the TEXT_IO package. This package offers the same functionality as DBMS_OUTPUT, but TEXT_IO goes much further in its ability to read and write from files.

Other Oracle-supplied packages fall into a few defined categories, including application component communication; tuning, portability, and compatibility; internal functions; and debugging. You should review the section on Oracle-supplied packages to identify the various package names and their component functions, procedures, exceptions, and user-defined datatypes. For purposes of better understanding the overall process of debugging, you should focus your attention to the DEBUG, TEXT_IO, and TOOL_ERR packages. The text covered these areas of debugging through a case study. You should review it to understand the process of debugging in Procedure Builder.

• LIST – handling text strings as lists in PL/SQL version 1 – make( ), destroy( ), nitems( ), getitem( ), appenditem( ), prependitem( ), insertitem( ), and deleteitem( ). Type and exception are listofchar and fail respectively.