Running the Updated PL/SQL
Before running the new version of the PL/SQL procedure, I need to replace the old one. To do this, I drop the existing version, as shown in Figure 4-1. Just right-click the procedure name and use the dialog you saw back in Figure 3-8. Technically, the CREATE OR REPLACE will of course obviate the need to manually drop the stored procedure. I’m just focusing here on a simple set of steps to get the procedure update done.
Figure 4-1. Drop the old version of the PL/SQL procedure
Notice in Figure 4-1 the appearance in line 1 of the word NONEDITIONABLE. This text is automatically inserted by the Oracle Database when the procedure is saved. It doesn’t have any effect on this discussion and can be ignored during development. I’ve left the reference to NONEDITIONABLE in the example just in case you come across it in your own work.
Then, replace the code with the new version from Example 4-3, as shown in Figure 4-2. Click the play button and then click the refresh button in Figure 4-2 to verify that the updated procedure has been stored.
Version Control for Database Scripts, PL/SQL, and Other Artifacts
It is highly likely that your PL/SQL code is maintained in a version control system such as Git or Subversion. This is strongly recommended and applies as well to any other SQL scripts required to build your database, such as the SQL required to build your database schema.
You might encounter debate about the need for version control. This could happen in very small organizations, startups, or organizations that are new to using database technology. Aside from built components (derived from version-controlled code), it is in fact really important to maintain version-controlled artifacts for all of your code. This even applies to database views.
The approach I’m suggesting is to be able to re-create your entire database from scratch. Some organizations might take the position that some or all of these artifacts can be derived on demand from the database itself. After all, the database is, well, a database. This is true to some extent. However, when dealing with considerations of resilience (as well as defense in depth, as discussed in “Verifying Downloaded Files: Defense in Depth”), it is useful to consider how you might recover after a complete destruction of your databases. This is a question I like to ask organizations of any size! You’d be surprised by some of the answers.
Suppose your production database consists of a non-cloud-based, geographically separated active and backup pair. This is quite common for organizations that, for a variety of reasons, have not opted to move their data infrastructure to the cloud. So, all actions (updates, deletes, etc.) made against the active database instance are automatically reflected in the backup instance. If the active database is destroyed, for example, in a natural disaster, the backup database can be used during the recovery process. This is the happy path, so to speak. On the other hand, if both database instances are destroyed, it may not be possible to derive the necessary database-resident scripts in order to reinstate your database. Also, there may not be materialized scripts for your views and the schema scripts may be out of date. The latter can occur when modifications to tables have occurred without reflecting those changes in the underlying scripts.
Such an event might have very serious repercussions for an organization. Not maintaining version-controlled code and scripts is a recipe for a lot of unnecessary pain. Just because it’s unlikely doesn’t mean it won’t happen. Make sure you always have a complete set of scripts available to rebuild your database in its entirety. Similar considerations also apply to the data inside the database.
Figure 4-2. Updated PL/SQL with exception handling
At this point, I can run the new update_employees procedure and then review the output, as shown in Figure 4-3.
