Oracle PL/SQL Best Practices

Chapter 9. Built-in Packages

Oracle provides a wide-ranging and ever-increasing set of built-in packages—packages that are installed into the database upon installation and that are officially supported by Oracle. These packages usually give you access to technology and features that would otherwise be difficult, if not impossible, to implement in native PL/SQL.

Tip

You should become familiar with the built-in packages; the Oracle Built-in Packages book and the Oracle HTML documentation are two excellent sources for this information.

You must, however, also be careful about how you implement programs based on these packages. In many cases, the packages are somewhat hard to use and understand; hence, you should hide that complexity so that your resulting code is easy to manage over time. I recommend you follow these general guidelines:

Encapsulate access to the built-in functionality

I often find it very worthwhile to build my own packages on top of the Oracle packages. I can then enhance the base package’s functionality. It also is then easier to use that package in a consistent fashion throughout my application.

Read the fine print—and run your own tests—on any built-in packaged functionality

Don’t assume, just because Oracle documentation says that a program will do X, that it will, in fact, do X in your environment and your version of Oracle. DBMS_UTILITY contains several programs, for example, that don’t work as advertised (COMPILE_SCHEMA, COMMA_TO_ TABLE, TABLE_TO_COMMA).^[1]