Understanding the Need for a PL/SQL Learning and Development Environment

As with other programming languages, PL/SQL code should be written and maintained using some sort of software tool. The tool I’ll be using in the book is the venerable SQL Developer from Oracle, which has many powerful features. SQL Developer will be introduced in the next chapter as part of the description of installing a virtualized Oracle Database instance.

Before wrapping up this chapter, let’s take a quick look at the proposed scale of resilience for PL/SQL code.

The Scale of Resilience

Let’s now back up a little and have a look at Example 1-3 from a different perspective. Rather than just trying to understand the PL/SQL, we now want to instead try to derive some idea of the code resilience. Example 1-4 shows the same code.

Example 1-4. How resilient is this PL/SQL?

DECLARE
CURSOR
c1
is
SELECT
ename
,
empno
,
sal
FROM
emp

ORDER
BY
sal
DESC
;
— start with highest paid employee

my_ename
 
VARCHAR2
(
10
);
my_empno
 
NUMBER
(
4
);

my_sal
  
NUMBER
(
7
,
2
);

BEGIN

OPEN
c1
;
FOR
i
IN
1
..
5
LOOP
FETCH
 
c1
INTO
my_ename
,
 
my_empno
,
 
my_sal
;
EXIT
 
WHEN
c1
%
NOTFOUND
;
/* in case the number requested */
/* is more than the total       */
/* number of employees          */

INSERT
INTO
temp
VALUES
(
my_sal
,
my_empno
,
my_ename
);
COMMIT
;
END
LOOP
;
CLOSE
c1
;
END
;

Remember the requirements enumerated earlier in relation to resilience? Here they are again in tabular form in Table 1-1. In order for our code to be designated as being resilient, it must include elements of all of these characteristics.

This is of course just a kind of thought experiment. It’s not rigorous because I’m just trying to arrive at a way of deciding whether the PL/SQL code is resilient or not. To make it a little more interesting, I’ve also introduced a score column in Table 1-1 with values ranging from 0 to 10, where 0 is the lowest possible score and 10 is the highest.

The values in the score column are my estimates of how the code stacks up. I’ll look at why these numbers were arrived at in the next few sections.

Table 1-1. Introducing a scale of resilience for PL/SQL Requirement number	Resilience requirement	Score (0–10)
1	Capture all errors and exceptions	0
2	Recoverability	2
3	Observability	0
4	Modifiability	5
5	Modularity	2
6	Simplicity	5
7	Coding conventions	5
8	Reusability	2
9	Repeatable testing	2
10	Avoiding common antipatterns	0
11	Schema evolution	0
	Total score	23

In Table 1-1, you see a score of 23 out of a possible 110, which is really quite low. Let’s try to see why I arrived at this disappointing result to the thought experiment.