Debugging PL/SQL Code

One of the simplest ways to debug PL/SQL code is to try each method, block, or statement interactively using SQL*Plus, and fix any problems before proceeding to the next statement. If you need more information on an error message, enter the statement SHOW ERRORS. Also consider displaying statements for runtime debugging, such as those of the general form:

Location in module: <location>
Parameter name: <name>
Parameter value: <value>

You can debug stored procedures and packages using the DBMS_OUTPUT package. You insert PUT and PUTLINE statements in your code to output the value of variables and expressions to your terminal. The DBMS_OUTPUT package is described in the Oracle8i Supplied Packages Reference and the PL/SQL User's Guide and Reference.

To debug stored procedures and packages, though not object type methods at present, you can use Procedure Builder, which is a part of the Oracle Developer/2000 tool set. Procedure Builder lets you execute PL/SQL stored procedures and triggers in a controlled debugging environment, and you can set breakpoints, list the values of variables, and perform other debugging tasks. See the Oracle8i Java Stored Procedures Developer's Guide

A PL/SQL tracing tool provides more information about exception conditions in application code. You can use this tool to trace the execution of server-side PL/SQL statements. Object type methods cannot be traced directly, but you can trace any PL/SQL functions or procedures that a method calls. The tracing tool also provides information about exception conditions in the application code. The trace output is written to the Oracle server trace file.

Note: Only the database administrator has access to this trace file. The tracing tool is described in the Oracle8i Application Developer's Guide - Fundamentals.

Notes for C and C++ Programmers

If you are a C or C++ programmer, several PL/SQL conventions and requirements may differ from your expectations. Note the following about PL/SQL:

• = means equal (not assign).

• := means assign (as in Algol).

• VARRAYs begin at index 1 (not 0).

• Comments begin with two hyphens (--), not with // or /*.

• The IF statement requires the THEN keyword.

• The IF statement must be concluded with the END IF keyword (which comes after the ELSE clause, if there is one).

• There is no PRINTF statement. The comparable feature is the DBMS_OUTPUT.PUT_LINE statement. In this statement, literal and variable text is separated using the double vertical bar (||).

• A function must have a return value, and a procedure cannot have a return value.

• If you call a function, it must be on the right side of an assignment operator.

• Many PL/SQL keywords cannot be used as variable names.

For complete information on PL/SQL, see the PL/SQL User's Guide and Reference.

Common Potential Errors

This section presents several kinds of errors you may make in creating a data cartridge.

Signature Mismatches

13/19    PLS-00538: subprogram or cursor '<name>' is declared in an object
         type specification and must be defined in the object type body
15/19    PLS-00539: subprogram '<name>' is declared in an object type body
         and must be defined in the object type specification

If you see either or both of these messages, you have made an error with the signature for a procedure or function. In other words, you have a mismatch between the function or procedure prototype that you entered in the object specification, and the definition in the object body.

Ensure that parameter orders, parameter spelling (including case), and function returns are identical. Use copy-and-paste to avoid errors in typing.

RPC Time Out

ORA-28576: lost RPC connection to external procedure agent
ORA-06512: at "<name>", line <number>
ORA-06512: at "<name>", line <number>
ORA-06512: at line 34

This error might occur after you exit the debugger for the DLL. Restart the program outside the debugger.

Package Corruption

ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04063: package body "<name>" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "<name>", line <number>
ORA-06512: at line <number>

This error might occur if you are extending an existing data cartridge; it indicates that the package has been corrupted and must be recompiled.

Before you can perform the recompilation, you must delete all tables and object types that depend upon the package that you will be recompiling. To find the dependents on a Windows NT system, use the Oracle Administrator toolbar. Click the Schema button, log in as sys\change_on_install, and find packages and tables that you created. Drop these packages and tables by entering SQL statements of the following form into the SQL*Plus interface:

Drop type <type_name>;
Drop table <table_name> cascade constraints;

The recompilation can then be done using a SQL statement of the following form:

Alter type <type_name> compile body;
or
Alter type <type_name> compile specification;