Oracle PL/SQL Best Practices, 2nd Edition

Chapter 7. Break Your Addiction to SQL

While working on the second edition of this book, I’ve been traveling to countries in Latin America and Europe, doing lots of presentations on best practices for PL/SQL. And in every one of these presentations, I’ve asked the attendees the following question:

How many of you have any guidelines for how, when, and where you should write SQL in your PL/SQL code? For example, a piece of paper with a few dos and don’ts?

And in seminar after seminar, I am shocked at the number of hands that are raised. Or should I say, not raised? In a group of 100, perhaps 2 or 3 will raise their hands. In a group of 50 or 60, most commonly not a single person raises her hand.

I think I know why—and you probably do, too, since you should be asking yourself the same question and thinking about why you too are saying, “No, no standards for SQL.”

PL/SQL developers (and their managers) take SQL completely for granted—they don’t really give it a second thought, when it is written inside a PL/SQL program. The reason for this is that Oracle Corporation has made it so easy to write SQL statements inside PL/SQL. There is no need for JDBC, ODBC, or any other intermediate layer of code. Just write the SQL you need! Right there! Over and over again!

Well, I think that this casual, thoughtless approach to writing SQL is one of the biggest problems and challenges we face in our PL/SQL code. Consider the impact of SQL statements in both the writing and the managing of our code base: ...