Debugging Procedures
and Unit Testing
Procedures are made up of the functions, transactions, and cursors we
have been discussing in this chapter. They are also part of the packages.
Understanding how to write stored procedures is important for a DBA.
Understanding how to review procedures and find the good and the bad
in them may be even more important.
When you’re experiencing problems with procedures, check their
permissions on objects. Also check for the dreaded missing semicolon
somewhere. These are quick areas to check, and in a small stored
procedure, they might be easy to spot.
Privileges needed for running in debug mode for PL/SQL are “Debug any
procedure” and “Debug connect session.” Figure 9-2 shows an example of
compiling a procedure in debug mode in SQL Developer. Clicking an error
message shown here will take you to the line in the code that is causing the issue.
262
Oracle Database Administration for Microsoft SQL Server DBAs
FIGURE 9-2.
Debugging procedures in SQL Developer
Breakpoints can also be set to walk through the procedures to validate
code and variables. Another way to get output throughout the procedure to
see what is happening is to use DBMS_OUTPUT.PUT_LINE to output a
statement, value, or step in the procedure.
SQL Developer also has unit testing functionality. Test plans can be set
up as an unit test repository that is created in the database. You can seed
some data and pass in parameters from tables to do the testing. To set up a
repository for unit testing or to connect to an existing repository, select the
Unit Test option from the Tools menu, as shown in Figure 9-3. Create a new
repository if one is not yet available. SYSDBA permissions are required to
create a new repository, but users can be added with lesser permissions to
run unit tests and create the test plans.

Figure 9-4 shows the first step in creating a unit test using the wizard. All
of the packages, procedures, and functions are listed and available for
testing. Other objects can be pulled in for using data or as part of the test in
later steps.
Chapter 9: PL/SQL
263
FIGURE 9-3.
Setting up a unit testing repository in SQL Developer
Error Handling
With error handling, if something in a procedure fails, it goes to the routine
for handling that exception. In SQL Server, the TRY CATCH block can be used
for error handling. This is almost like wrapping the code in a transaction
with a BEGIN TRY and then executing some code. If errors come up, it
goes to the BEGIN CATCH.
BEGIN TRY
(T-SQL Code
END TRY
BEGIN CATCH
……. (Error handling code)
END CATCH
264
Oracle Database Administration for Microsoft SQL Server DBAs
FIGURE 9-4.
Creating a unit test in SQL Developer
With PL/SQL’s exception handling, the errors that are raised can be user-
or system-defined.
DECLARE
(variables defined)
BEGIN
(Blocks of code)

EXCEPTION
WHEN exception_name THEN
(Exception handling code)
END;
The error functions in SQL Server and Oracle provide information about
the error or failure, as shown in Table 9-5. Oracle’s SQLCODE function
returns an error number. SQLERRM returns a message.
Exceptions do not cause DML statements to roll back unless this happens
by default without an exception handler routine. The exception handler
routine would need to handle commits and rollbacks depending on the
failure. Within the exception handler, there might be a separate commit for
inserting into an error log. Using savepoints is a good way to handle the
rollbacks, so this logging of errors does not become part of the transaction.
Autonomous transactions are another way to handle this. Autonomous
transactions are changes made with a block of code that can be saved or
reversed without affecting the outer or main transaction.
Chapter 9: PL/SQL
265
SQL Server Error Functions Oracle Error Functions
ERROR_NUMBER()
ERROR_SEVERITY()
ERROR_STATE()
ERROR_PROCEDURE()
ERROR_LINE()
ERROR_MESSAGE()
SQLCODE
SQLERRM
TABLE 9-5.
Error Functions in SQL Server and Oracle
Let’s take a look at a couple of examples of exception handling.

EXCEPTION
WHEN NO_DATA_FOUND THEN
v_msg := 'Record not found' || TO_CHAR(v_id);
v_err := SQLCODE;
v_prog := 'get product';
insert into errlog
values(v_err,v_msg, v_prog, sysdate);
Error Handling Packages
You can create a package to call your error procedures. Using a standard
package makes it easier to have error handling at the end of each procedure
and provides a centralized place to gather the failure information.
## Create a table to hold the error information
SQLPLUS> create table errlog (
errcode integer,
errmsg varchar2(4000),
prog_action varchar2(300),
created_on date,
created_by varchar2(30));
Table created.
## Create package with procedures for handling errors
SQLPLUS> create or replace package errlogs
IS
c_table constant NUMBER :=1;
PROCEDURE handle (
errcode IN NUMBER := NULL,
errmsg IN VARCHAR2 := NULL,
logerr IN BOOLEAN := TRUE,
reraise IN BOOLEAN := FALSE);
PROCEDURE raise (errcode IN NUMBER := NULL,
errmsg IN VARCHAR2 := NULL, prog_action IN VARCHAR2);

PROCEDURE log (errcode IN NUMBER := NULL,
errmsg IN VARCHAR2 := NULL, prog_action IN VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY errlogs
IS
g_target NUMBER := c_table;
PROCEDURE handle (
errcode IN NUMBER := NULL, errmsg IN VARCHAR2 := NULL,
prog_action IN VARCHAR2 := NULL, logerr IN BOOLEAN := TRUE,
reraise IN BOOLEAN := FALSE )
266
Oracle Database Administration for Microsoft SQL Server DBAs
IS
BEGIN
IF logerr
THEN
log (errcode, errmsg, prog_action);
END IF;
IF reraise
THEN
errlogs.raise (errcode, errmsg, prog_action);
END IF;
END;
PROCEDURE raise (
errcode IN PLS_INTEGER := NULL, errmsg IN VARCHAR2 := NULL,
prog_action IN VARCHAR2 := NULL ) IS
l_errcode PLS_INTEGER := NVL (errcode, SQLCODE);
l_errmsg VARCHAR2(1000) := NVL (errmsg, SQLERRM);
l_progact VARCHAR2(300) := NVL(prog_action,'Default Action');

BEGIN
IF l_errcode BETWEEN -20999 AND -20000
THEN
raise_application_error (l_errcode, l_errmsg);
ELSIF l_errcode != 0
THEN
EXECUTE IMMEDIATE
'DECLARE myexc EXCEPTION; ' ||
' PRAGMA EXCEPTION_INIT (myexc, ' ||
TO_CHAR (err_in) || ');' ||
'BEGIN RAISE myexc; END;';
END IF;
END;
PROCEDURE log (
errcode IN PLS_INTEGER := NULL,
errmsg IN VARCHAR2 := NULL ) IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_sqlcode pls_integer := NVL (errcode, SQLCODE);
l_sqlerrm VARCHAR2(1000) := NVL (errmsg, SQLERRM);
BEGIN
INSERT INTO errlog
(errcode, errmsg, prog_action, created_on, created_by)
VALUES (l_sqlcode,l_sqlerrm,l_progact,SYSDATE,USER);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN ROLLBACK;
END;
/
Chapter 9: PL/SQL

267
This package can be used in the exception handling of any procedure.
The call to the package passes in the needed parameters, including information
about what procedure was running, to put details in the error log.
SQLPLUS> create or replace procedure testing_errors
as
procedure_name varchar2(30) := 'testing_errors';
BEGIN

EXCEPTION
WHEN OTHERS
errlogs.handle(SQLCODE,SQLERRM,procedure_name);
END;
/
Expanding on the error handling could then allow for different logs to be
captured in a table or even a file. Rollback and commit information can be
handled in the executing procedure, and then the error capture in the same
error package for all procedures, to maintain consistency.
Standard Error Messages
PL/SQL can raise user error messages that can be passed along to the
application for handling on the application side as well. Also, application
errors can be raised to pass the information to the application.
The standard Oracle exceptions can be associated with a user-defined
application error. You can also have other data or changes raise user-
defined application errors.
Raised errors can be used in a trigger to disallow updates to a table:
raise_application_error(-20002,'Updates not allowed on this table');
If there is a check on a value, the procedure could raise an error stating
that the value is not allowed or needs to be in a different range:
raise_application_error(-20001,'Salary not in correct range for department');

You can pass through additional information about the values of the
columns or any of the variables in the procedure.
When standard Oracle messages come through, different information
can be passed through to the application:
raise_application_error(-20004,'No Data Found, values not in table');
268
Oracle Database Administration for Microsoft SQL Server DBAs
Here is a partial list of standard exceptions:
■
NO_DATA_FOUND
■
VALUE_ERROR
■
OTHERS
■
INVALID_CURSOR
■
INVALID_NUMBER
■
CASE_NOT_FOUND
■
TOO_MANY_ROWS
■
ROWTYPE_MISMATCH
Instead of having the exception handler looking at WHEN OTHERS, a
different set of steps can be coded for each of these exceptions.
EXCEPTION
WHEN exception1 THEN handler for exception1
sequence_of_statements1
WHEN exception2 THEN another handler for exception2

sequence_of_statements2

WHEN OTHERS THEN optional handler for all other errors
sequence_of_statements3
END;
## Another example with the raise application
EXCEPTION
WHEN TOO_MANY_ROWS THEN
rollback to savepoint sales1;
errlogs.handle(SQLCODE,SQLERRM,'Sales_records');
raise_application_error(-20001,'Query return more rows
than expected.');
WHEN NO_DATA_FOUND THEN
errlogs.handle(SQLCODE,SQLERRM,'customer_info');
raise_application_error(-20002,
'Data not available for this customer');
WHEN OTHERS THEN
errlogs.handle(SQLCODE,SQLERRM,'Oh No!');
raise_application_error(-20003,'Unknow error details in log');
END;
Chapter 9: PL/SQL
269
Using DBMS Packages
System packages can be used in user packages as long as the user has
permissions. SQL Server has several system procedures and extended
procedures that are used in the same way as the Oracle packages.
We have already looked at DBMS_SCHEDULER and DBMS_STATS in
previous chapters. The following are a few other packages you might
consider using:
■

DBMS_OUTPUT is useful for seeing what is running in a stored
procedure. It sends output to the screen.
■
DBMS_METADATA is useful for getting the definitions of the
objects.
■
DBMS_REDEFINITION offers a way to rebuild a table online.
■
DBMS_SQL is used to create dynamic SQL in PL/SQL.
Here’s an example of using the DBMS_METADATA package:
SQLPLUS> set long 200000 pages 0 lines 131
SQLPLUS> select dbms_metadata.get_ddl('TABLE','SALES') from dual;
CREATE TABLE "MMDEV"."SALES"
( "SALES_ID" NUMBER NOT NULL ENABLE,
"PROD_ID" NUMBER,
"STATE_ID" NUMBER,
"SALE_DATE" DATE,
"CUSTOMER_ID" NUMBER,
"REGION_ID" NUMBER,
"AMOUNT" NUMBER,
CONSTRAINT "SALES_PK" PRIMARY KEY ("SALES_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576
MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "SALES_PRODUCT_FK1" FOREIGN KEY ("STATE_ID")
REFERENCES "MMDEV"."STATES" ("STATE_ID") ENABLE )
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576

MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
270
Oracle Database Administration for Microsoft SQL Server DBAs
There are many more useful packages, including several to help monitor
databases and get details. Because of this access, permissions need to be
granted carefully.
Summary
PL/SQL is an extremely useful database programming language, which you
can use to develop robust applications as well as run maintenance tasks and
monitor databases. This chapter presented examples of how to use PL/SQL
to build packages, procedures, functions, and triggers. We looked at some
of the ways to process data through cursors, the syntax for updates, and
other differences between the database programming languages. As you can
see, there is plenty of fun to have with PL/SQL!
The processing of statements is similar in both platforms, requiring
transaction and points to commit or rollback. Oracle packages allow
procedures and functions to be grouped together. Packages can be used for
several of the transactions and processes that are written in PL/SQL. Error
packages to be used with exception handling are useful to ensure consistent
ways to log errors and raise application errors.
Chapter 9: PL/SQL
271