Shared Pool   «Prev 

Oracle Components including packages, memory, and Triggers

Just as the wisdom of the 1980s dictated that data should be centralized, the 1990s was an era where SQL was centralized and managed. With the centralization of SQL, many previously impossible tasks have become trivial. For instance:
  1. SQL can easily be identified and reused
  2. SQL can be extracted by a DBA, allowing the DBA to run EXPLAIN PLAN utilities to determine the proper placement of table indices
  3. SQL can be searched, allowing for fast identification of "where used" information (e.g., when a column changes definition, all SQL referencing that column can be quickly identified) The most compelling reasons to put SQL within packages are portability and code management. If applications become SQL-less with calls to stored procedures, then entire applications can be ported to other platforms without touching a single line of the application code.
    As the cost of memory drops, multi-gigabyte Oracle SGA regions will be commonplace. Until that time, however, the DBA must carefully consider the ramifications of pinning a package in the SGA.
    Whenever there are behaviors that are directly tied to DML events, such as inserts into a table, an Oracle trigger can be used. Triggers, just like stored procedures contain PL/SQL, but a trigger is tightly coupled with an SQL operation on a specific table.

Package Capabilities

The package is a powerful and important element of the PL/SQL language. It should be the cornerstone of any application development project.
Question: What makes the package so powerful and important? Consider their advantages:
  1. Enhance and maintain applications more easily: As more and more of the production PL/SQL code base moves into maintenance mode, the quality of PL/SQL applications will be measured as much by the ease of maintenance as they are by overall performance. Packages can make a substantial difference in this regard. From data encapsulation (hiding all calls to SQL statements behind a procedural interface to avoid repetition), to enumerating constants for literal or magic values, to grouping together logically related functionality, package-driven design and implementation lead to reduced points of failure in an application.
  2. Improve overall application performance: By using packages, you can improve the performance of your code in a number of ways. Persistent package data can dramatically improve the response time of queries by caching static data, thereby avoiding repeated queries of the same information. Oracle's memory management also optimizes access to code defined in packages .
  3. Shore up application or built-in weaknesses: It is quite straightforward to construct a package on top of existing functionality where there are drawbacks. (Consider, for example, the UTL_FILE and DBMS_OUTPUT built-in packages in which crucial functionality is badly or partially implemented.) You do not have to accept these weaknesses; instead, you can build your own package on top of Oracle’s to correct as many of the problems as possible. You can get some of the same effect with standalone procedures or functions, but overloading and other package features make this approach vastly preferable.
  4. Minimize the need to recompile code: As you will read below, a package usually consists of two pieces of code: the specification and body. External programs (not defined in the package) can only call programs listed in the specification. If you change and recompile the package body, those external programs are not invalidated. Minimizing the need to recompile code is a critical factor in administering large bodies of application logic.