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.
As of version 10g, Oracle includes a number of features that assist in writing code that is more easily supported and maintained.
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; /
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.
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.
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;
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.
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.
GRANT debug any procedure, debug connect session TO <user_name>;
PL/SQL debugging requires special "debug" permissions that must be granted from a privileged user.