Resilient Oracle PL/SQL

Chapter 3. Taking SQL Developer for a Drive

We’ve covered a lot of ground in the previous chapters. At this point, you’ve now got a working installation of a containerized Oracle Database instance. The venerable SQL Developer application is also configured for use in accessing the database. You can now get stuck into more detailed PL/SQL examples.

After reading this chapter, you should be able to take a deeper look at the development environment and use some new skills to fix a PL/SQL error.

Let’s now revisit the error from the previous chapter. It has always been my experience that errors are good teachers. This is because it’s not always obvious how to fix most errors, particularly the more difficult ones. The resolution of errors forces us to resort to a range of techniques, including:

• Examining logfiles

• Carefully reading error messages

• Looking up the meaning of the error messages

• Examining the data in tables

• Applying some trial fixes

• Repeating these steps

Let’s do it.

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 ...