End User Code Objects

Object Source File Content

It is required that each custom or user source file declare exactly one SQL code object. That is, each file named dbms_cp_*.sql, ora_upkg_*.sql or dbms_up_*.sql is allowed to define only one custom procedure, user package, or user procedure, respectively. It is further required that each such code object defined by such files must bear the exact same name as the file minus the filename's dbms_ prefix and the .sql file extension. For example, a file named ora_cp_altlsesum.sql may define only one object, and that object must be named cp_altlsesum. User packages may define only one package, but each package can contain multiple functions.

The primary reason for this convention is to allow the loader to automatically—without user intervention or user code—perform the necessary grant of privileges to the logical database role or group to allow for the code object's execution.

The source files must also contain certain programming constructs designed to allow the system to manage the creation and replacement of code objects. The required constructs for Oracle and Sybase DBMS are given in Oracle Package Header and Sybase Procedure Header, respectively. Without these constructs, the loading of SQL code objects will fail, if not on the first load, then on a subsequent one.