Expert PL/SQL Practices

Congratulations on buying this book. PL/SQL is a great tool to have in your toolbox; however, you should understand that use of PL/SQL is not suitable for all scenarios. This chapter will teach you when to code your application in PL/SQL, how to write scalable code, and, more importantly, when not to code programs in PL/SQL. Abuse of some PL/SQL constructs leads to unscalable code. In this chapter, I will review various cases in which the misuse of PL/SQL was unsuitable and lead to an unscalable application.

For the past ten years, I have attended a number of Oracle conferences all over the United States. Time after time, I have listened to presenters talking about building systems “better, faster, cheaper” But when these same people come down off the stage and discuss the same issues with you one-on-one, the message is much less optimistic. The often cited 75% failure rate of all major IT projects is still a reality. Adding in the cases of “failures declared successes” (i.e. nobody was brave enough to admit the wrongdoing), it becomes even more clear that there is a crisis in our contemporary software development process.

Imagine if you went to the grocery store and only one person was allowed in at a time. Everyone else had to wait outside in a long line until each individual completed their trip before entering the store. Even worse, image if you were only allowed to buy one item while inside the store. If you needed multiple items, you were required to get back in line outside of the store for each one.

When Oracle implemented Java in the RDBMS kernel, it was thought to be the end of PL/SQL. However, in version 10.2, Oracle rewrote the PL/SQL compiler and implemented various new features, proving that there is a future for PL/SQL. This chapter will cover two features that were first seen in Oracle 10.2: PL/SQL warnings and PL/SQL conditional compilation.

Any developer accessing the Oracle Database uses SQL whether running SQL reports and queries developed personally or provided by another developer. While it may not be true to say that every developer runs and uses PL/SQL when working with the Oracle Database, the number is no doubt very high as any procedural access to the Oracle Database is best done using PL/SQL. As a developer, if you create and work with PL/SQL, you should be debugging and testing the PL/SQL to ensure accurate and efficient code.

This chapter is about the bulk SQL operations available in PL/SQL. Bulk operations in PL/SQL enable you to manipulate and process many rows at once, rather than one row at a time. In this chapter, you’ll learn about bulk fetching and bulk binding. Bulk fetching refers to the ability to retrieve a set of rows from the database into PL/SQL structures with a single call, rather than making a call to the database for each row to be retrieved. Bulk binding lets you perform the converse: to take those sets of rows stored in PL/SQL structures and save them to the database in an efficient manner.

The name of this chapter is “Know Your Code.” You might think that if you wrote a piece of code, you know it well. You probably do. You know it at least as well as anyone else. Even if you’ve written the code, however, you will still make assumptions based from your memory. These assumptions will likely move further and further from reality as time goes on. You have a best guess—an educated assumption—but that is subjective and dependent on human frailty. And if you didn’t write the code, all bets are off.

This chapter will introduce you to a powerful software engineering paradigm called Design by Contract and a method for applying it to PL/SQL programming.

Functions are an integral part of any well-designed PL/SQL application. They are an embodiment of programming best practices, such as code modularization, reuse, and the encapsulation of business or application logic. When used as simple building-blocks for larger programs, they can be an elegant and simple way to extend functionality while reducing code-complexity at minimal cost.

Most business applications are data-centric and therefore require a database at their core. These applications are commonly used for years or even decades. During this time, the user interface is sometimes completely replaced or extended to keep it state of the art. The data model and the business logic, on the other hand, usually evolve more steadily along with the supported business processes. Many of these applications end up large, whether they start small (such as an APEX replacement of a spreadsheet) or are complex from the onset. Thus, we need an architecture and a programming language suitable for developing and maintaining data-centric business logic for years. PL/SQL in the Oracle database ideally fits these requirements.

I worked for Lockheed right after college. In my first year, I was part of a team assigned to evaluate Manufacturing Resources Planning (MRP) methods and tools. I was familiar with MRP; it had been part of the required curriculum for my bachelor’s degree, plus I had some prior experience with MRP systems. However, MRP was a very new idea at Lockheed, and it was the antithesis of the philosophy behind the existing systems. My boss and mentor, Skip Christopherson, had been at Lockheed more years than I’d been on the planet and he knew every aspect of Lockheed’s scheduling system, down to the very smallest detail. You attended classes in MRP together after work and it was like a classic comedy scene with the smart-mouthed rookie breezing through the material while the grizzled older employee who actually knows how things work struggles to grasp the point of this new fangled technology with all its bells and whistles. At the end of the class each night, you’d walk back to our windowless office while Skip talked about all that was wrong with this inane idea that a system could process changes and regenerate the schedule every night without throwing the assembly line into utter chaos. I’d been learning about how Lockheed approached scheduling at the same time you were taking the MRP class and I’d begun to understand and appreciate how it worked. Skip was right: MRP was exactly opposite our process flow model, which was based on math, statistics, and learning curves. Using the knowledge gained from decades of successful aircraft programs, you calculated the boundaries for the specific phases of the assembly process and tied them to positions on the shop floor. These positions and dates created a window in time for each crew to contain their contribution to the assembly. Within those boundaries, parts and schedules might shuffle madly while the crew worked insane hours, but the men on line knew that meeting the scheduled move date for the next position was critical and they’d learned to make it happen no matter what.

Performance and profiling are critical words in our everyday conversations in the office where I work, in our engagements with clients, and in our teaching. Both words apply equally well to all aspects of life, including software engineering and the topic at hand, PL/SQL.

Can you wear a Tuxedo and Birkenstocks at the same time? Yes, you can! Anyone who spent some time at a particle physics laboratory knows it is technically possible. However, some may say that such an outfit clashes with the conventions of elegance. You may also like to wear a combination of green and blue clothes. (Personally I prefer mixing red and black). We can argue endlessly about our respective taste: everyone is right; everyone is wrong. At the same time, conventions, written or not, help with living and working together. They have their own history and reasons.

Dependencies between PL/SQL packages can a perplexing source of application errors. Database administrators and developers unacquainted with how dependencies work can find themselves scratching their heads over sporadic and unrepeatable errors that are seemingly without cause. For example, while executing a procedure in a package you are responsible for, an application throws the following error: ORA-04068: existing state of package has been discarded