Subprogram Development Techniques

Contact Us or call 1-877-932-8228
Subprogram Development Techniques

Subprogram Development Techniques

Addressing Compilation Errors

The following are some areas to be aware of to increase your ability to write, support, and debug PL/SQL code.

Any mistake in PL/SQL syntax will result in a compilation error. The SHOW ERROR command can be used in PL/SQL to obtain information about compilation errors generated during the current connection session.. In addition, the USER_ERRORS view can be consulted, and errors are displayed along with detail information in SQL Developer.

Directives for Debugging

As of version 10g, Oracle includes a number of features that assist in writing code that is more easily supported and maintained.

Code Sample:

Stored-Procedures-and-Functions/Demos/debug_output.sql
BEGIN
  DBMS_OUTPUT.put_line ('Line number: '
                         || $$plsql_line);
  DBMS_OUTPUT.put_line ('Unit: ' || $$plsql_unit);
  DBMS_OUTPUT.put_line ('Unit: ' ||
                        COALESCE ($$plsql_unit, 'anonymous block'));
  RAISE VALUE_ERROR;
EXCEPTION
  WHEN VALUE_ERROR
      THEN
        DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
END;
/

Code Explanation

In this example, special directives are used to identify the line number and the programming unit being executed. Additional error information is also displayed by calling DBMS_UTILITY.format_error_backtrace.

Issues with Booleans

In general, PL/SQL is closely related to SQL so no conversion of data types is needed. One significant exception to this rule is the Boolean type which exists in PL/SQL but not in SQL. There are a variety of ways to map Boolean data types (0 and 1, TRUE and FALSE). There is also an Oracle Supplied function (DIUTIL.INT_TO_BOOL) that can be used to convert integers to Boolean values.

Code Sample:

Stored-Procedures-and-Functions/Demos/Boolean_test.sql
declare
 -- Problem: SQL does not have a BOOLEAN datatype
 i NUMBER := 1;
 b BOOLEAN := TRUE;
 -- Other variations
 -- Constant NUMBER - TRUE, String - True
begin
 /*
   Null or zero evaluates to false, 
   1 is true, 
   other values cause errors
 */
 IF sys.diutil.int_to_bool(i) THEN
   dbms_output.put_line('diutil.int_to_bool(i) : TRUE for '||i);
 ELSE
   dbms_output.put_line('diutil.int_to_bool(i) : FALSE for '||i);
 END IF;
 
 --Illegal in this context
 --select 0 into b from dual;
 
 /*
 However this works! - 0 or null is false, 
                       all other numbers are true, 
                       characters/sysdates cause errors
 */
 execute immediate 'select 1 from dual' into b; 

 IF b THEN
   dbms_output.put_line('b : TRUE');
 ELSE
   dbms_output.put_line('b : FALSE');
 END IF;

end;

Code Explanation

The code in this demonstration illustrates some surprising behavior related to Booleans.

If possible, settle on one consistent way to represent Boolean values stored in the database for your code base and make sure the practice is clear to all developers.

Integrated Development Environments

SQL Developer (as well as other 3rd party tools such as Toad for Oracle from Quest or PL/SQL Developer from Allround Automations) are Integrated Development Environments (IDE) specifically designed to facilitate development of PL/SQL code. These tools provide features such as a debugger which allows you to set breakpoints and step through code to trace execution interactively.

IDEs typically include templates or "wizards" that allow you to choose the configuration of subprograms without requiring you to recall and manually type in the associated details. These can be helpful for encouraging productivity, but make sure that you understand the code that is being generated and have chosen the correctwizard/template for your specific situation.

Many IDEs include a debugger which allows you to step through code a line at a time and evaluate variables interactively. These also can be helpful tools, but these debuggers do not step inside individual SQL statements, which are frequently the most challenging part of a program to fix.

Because PL/SQL executes within the database, some database configuration is needed (specifically granting appropriate privileges) to use a PL/SQL debugger.

Code Sample:

Stored-Procedures-and-Functions/Demos/permissions.sql
GRANT debug any procedure, debug connect session 
TO <user_name>;

Code Explanation

PL/SQL debugging requires special "debug" permissions that must be granted from a privileged user.

Next