Fixing the Pesky PL/SQL Error
In the previous chapter, we encountered the error shown in Example 3-1 during the PL/SQL run.
Example 3-1. 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
.
Let’s look a little more closely at Example 3-1. Notice that there’s an error on line 15. I need to locate that line in the PL/SQL code. I can of course just manually count down to line 15 in the PL/SQL code. But in more realistic situations, there might well be an error on line xxx, where xxx is a large number. So, manually counting the lines quickly loses its appeal and becomes impractical. It’s better to switch on the display of line numbers in SQL Developer. To do this, click Tools > Preferences > Code Editor > Line Gutter, as shown in Figure 3-1.
Figure 3-1. Switching on line numbers in SQL Developer
If the Show Line Numbers control in Figure 3-1 is unchecked, then click the control followed by OK, and then each line will start with its number, as shown in Figure 3-2. It’s much more intuitively appealing to see the line numbers. This allows for direct comparison of the error report and the source code line that is causing the issue.
Figure 3-2. PL/SQL code with line numbers
In Figure 3-3, you see another really useful facility: displaying the data types of the columns of the TEMP target table.
Figure 3-3. Describing the target table
The following ultra-simple SQL is required to produce the Figure 3-3 output:
describe
TEMP
;
It will produce the data shown at the bottom of Figure 3-3:
ENAME
VARCHAR2
(
10
)
EMPNO
NUMBER
(
4
)
SAL
NUMBER
(
7
,
2
)
With these data types and their values noted, let’s now look at the relevant PL/SQL (keeping the INSERT INTO line in mind in particular):
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
;
Putting the code listing and the data description together, you can now see with certainty that the PL/SQL code is attempting to do two erroneous actions:
- Insert the salary number into the ENAME (VARCHAR2) column.
- Insert the employee name into the SAL (NUMBER) column.
With this in mind, I’ve applied the required code fix (see Figure 3-4). To compile the code, you just need to click the play button, which sits below the Welcome Page tab. Notice also at the bottom of Figure 3-4, the successful compilation status message. Without this, your PL/SQL won’t run; it’s important to get into the habit of checking for this message.
So, what caused the problem? The issue was caused by trying to use a string value in place of a numerical one. Running the code in Figure 3-4 should now be successful.
In summary, fixing this type of issue requires us to iteratively do the following:
Change perspective.
Look at the schema SQL.
Look at the relevant data.
Look at the error report.
Put it all together.
Arrive at a solution.
Figure 3-4. Applying the code fix
To verify the fix, you can then run the following SQL:
select
*
from
temp
;
This should produce something like Figure 3-5.
Figure 3-5. The code is now fixed and the data is verified
The PL/SQL code error is now fixed and verified. Until now, I’ve been using ad hoc PL/SQL. In the next section, you’ll see how to install a stored procedure in the database.