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]
[1] COMPILE_SCHEMA is supposed to recompile all invalid objects. Sometimes it works, sometimes it does nothing, and sometimes it invalidates other objects as it recompiles currently invalid objects. COMMA_TO_TABLE and TABLE_TO_COMMA work with comma-delimited lists, but the elements in the list have to be valid PL/SQL identifiers. If you pass “1,2,3” to COMMA_TO_TABLE, for example, Oracle raises an exception.