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.
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.
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.