Oracle PL/SQL Programming, Third Edition

What You Need to Know

Do you really need to remember everything in this chapter? I certainly hope not, though your database administrator probably does. In addition to satisfying healthy curiosity, my goal in presenting this material was to help allay any misgivings programmers might have about what happens under the hood. Whether or not you’ve ever had such concerns, there are a number of important points to remember about PL/SQL’s runtime architecture.

• To avoid compilation overhead, programs you plan to use more than a few times should be put in stored programs rather than stored in files as anonymous blocks.

• In addition to their unique ability to preserve state throughout a session, PL/SQL packages offer performance benefits. You should put most of your extensive application logic into package bodies.

• If you don’t want to bother learning how to query Oracle’s data dictionary, you should probably be using a front-end developer’s tool of some kind.

• While Oracle’s automatic dependency management approach relieves a huge burden on developers, upgrading applications on a live production database should be undertaken with great care.

• Oracle’s sophisticated approaches aimed at minimizing the machine resources needed to run PL/SQL occasionally need a little help from developers and DBAs—for example, by explicitly freeing unused user memory or pinning objects in memory.

• When you OPEN an explicit cursor in a PL/SQL program, be sure to CLOSE it as soon as you are through fetching.

• Native ...