Executing the PL/SQL Procedure

Right-click the UPDATE_EMPLOYEES procedure name in Figure 3-7. This should display the procedure-specific context menu, as shown in Figure 3-8.

Notice the options available in relation to managing the PL/SQL procedure, such as:

• Edit
• Export
• Debug
• Compile
• Run
• Profile
• Grant

Figure 3-8. Executing PL/SQL in the containerized Oracle Database instance

Clearly, this menu provides a lot of power. For the moment, just click the Run option, at which point the Run PL/SQL dialog appears, as shown in Figure 3-9.

Figure 3-9. Launching the PL/SQL in the containerized Oracle Database instance

In Figure 3-9, there’s no need to do anything other than click OK. If all is well, this should actually run the procedure, at which point you may be challenged for your credentials, as shown in Figure 3-10. We’re nearly there.

Figure 3-10. Supplying credentials before launching the PL/SQL procedure

Supply and submit your credentials in Figure 3-10 by clicking OK. At long last, the stored procedure should now run. Not too much feedback, though! This is because the PL/SQL procedure applies its output to the TEMP table. To see the result of the procedure, we need to use some SQL as follows. Use this select statement to view the data and delete it. Next, rerun the PL/SQL procedure. Then, run the select again to verify the data:

select
*
from
TEMP
;

delete
from
 
TEMP
;

So, that completes the description of the PL/SQL fix.

The Takeaway: Errors Are Good Teachers

The key takeaway from this is that error reports (just like compiler warnings) are your friends and they invariably give good advice. Before we move on, let’s make a note of that exotic error message (ORA-06512):

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
.

This error is in fact caused by the stack, which has been unwound on account of an unhandled exception in the PL/SQL code. As we’ll see later, this is actually providing us with some fantastically useful advice about the code. But we’ll get to that in an upcoming chapter.

Shift-Left in Operation

Shift-left is a DevOps practice that aims to address code testing, defect removal, and performance evaluation as early as possible in the software development lifecycle. In effect, the purpose of shift-left is to move (or shift) these activities to the left side of the DevOps lifecycle, i.e., away from customers and end users but closer to developers.