Running Some PL/SQL Code
Let’s now review one of the earlier PL/SQL examples, as shown in Example 2-8.
Example 2-8. Cursor use in 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
;
To run the code in Example 2-8, just select it (from the GitHub editor) and paste it into the SQL Developer Worksheet tab. Remove any non-PL/SQL characters and the screen should look like Figure 2-13.
Figure 2-13. PL/SQL code inserted and run
To execute the PL/SQL in Figure 2-13, click the play icon at the upper-left corner of the window, just under the Welcome Page tab. The result of the run should be displayed in the Script Output window under the worksheet.
What is the output? Well, it’s the rather disappointing error message shown in Example 2-9.
Example 2-9. An error is born!
Error
report
–
ORA
–
01722
:
invalid
number
ORA
–
06512
:
at
line
15
01722
.
00000
–
“invalid number”
*
Cause
:
The
specified
number
was
invalid
.
*
Action
:
Specify
a
valid
number
.
Listing
2
–
3
The
code
has
an
error
,
what
‘
s
going
on
?
After all the hard work of getting Docker installed and creating our Oracle Database containerized instance, we’ve hit an error. What should we do?
The error in question is actually quite simple to fix. I’ll leave it in for the moment because, in software development in general, the resolution of errors like this one is a crucial skill to acquire. Also, we often learn more from mistakes than from getting it right the first time. I’ll fix this one in the next chapter. In the meantime, can you see what might be causing the error?
Ignoring Warnings
Is it possible or feasible to not resolve the error in Example 2-9? Is there some workaround we could try? What about ignoring it? It’s definitely not an option to ignore this issue, but what about the more general case of ignoring warnings?
In the old days, with languages like C, the compilers aimed to help developers with their code. One way this occurred was when the compiler would complain about some use of the language. A simple example is when a numerical format gets truncated, which might, in turn, result in some type of data loss.
To help the programmer avoid this and similar situations, the compiler could be configured to emit appropriate warnings (above and beyond the default warnings). Many bad habits were formed at an early stage in some C programmers’ careers! An egregious bad habit and a deep antipattern was switching off or simply ignoring these types of warnings.
When your development tooling indicates to you that you’re doing something wrong, then it’s likely that you are in fact doing something wrong. In other words, treat warnings in the same way as you would treat an error message. Warnings can be seen as being like a senior developer looking over your shoulder as you work. I can’t think of a good reason not to take the advice from this extra colleague. It’s not uncommon for these warnings to be difficult to understand. Persistence in figuring them out will pay dividends in the form of spotting, fixing, and avoiding similar issues over the course of your career.
As you’ve now seen, PL/SQL also provides similar feedback when it deems code to be in some way risky or below par. When we fix the error described in Example 2-9, you’ll see firsthand how to bring the various strands together in order to achieve a successful resolution.
Before rounding off the chapter, let’s have a quick tour of some fairly typical issues that you may encounter when using Docker.