“What the pragmatist has his pragmatism for is to be able to say, Here is a definition and it does not differ at all from your confusedly apprehended conception because there is no practical difference.” ― Charles Sanders Peirce
The word pragma origins from the ancient Greek word πρᾶγμα (prâgma) and means “a thing done, a fact”.
In the Oracle procedural language PL/SQL, the concept of PRAGMA refers to a compiler directive and is used to give an instruction to the compiler. In other words, a pragma is a compiler directive that tells Oracle to use rules (other than the default rules) for the object.
PRAGMA are processes at compile time and not run time. They do not affect the code and the outcome of a program, they simply convey information to the compiler.
Several websites list mostly 5 PL/SQL pragmas, but actually they are more. Some are documented (10), some are not (6). Here is (most likely) the complete list:
1. The AUTONOMOUS_TRANSACTION pragma marks a routine as autonomous; that is, independent of the main transaction. This is the most commonly used and popular pragma.
In this context, a routine is one of these:
Schema-level (not nested) anonymous PL/SQL block
Standalone, package, or nested subprogram
Method of an ADT
Noncompound trigger
2. The COVERAGE pragma marks PL/SQL code which is infeasible to test for coverage. These marks improve coverage metric accuracy. The COVERAGE pragma marks PL/SQL source code to indicate that the code may not be feasibly tested for coverage. The pragma marks a specific code section. Marking infeasible code improves the quality of coverage metrics used to assess how much testing has been achieved. Here is an example.
3. The DEPRECATE pragma marks a PL/SQL element as deprecated. The compiler issues warnings for uses of pragma DEPRECATE or of deprecated elements. The associated warnings tell users of a deprecated element that other code may need to be changed to account for the deprecation. Here is a good example of how it works.
4. The EXCEPTION_INIT pragma associates a user-defined exception name with an error code. The EXCEPTION_INIT pragma can appear only in the same declarative part as its associated exception, anywhere after the exception declaration.
5. The INLINE pragma specifies whether a subprogram invocation is to be inlined. Inlining replaces a subprogram invocation with a copy of the invoked subprogram (if the invoked and invoking subprograms are in the same program unit).
6. The RESTRICT_REFERENCES pragma asserts that a user-defined subprogram does not read or write database tables or package variables. The RESTRICT_REFERENCES pragma is deprecated. Oracle recommends using DETERMINISTIC and PARALLEL_ENABLE instead of RESTRICT_REFERENCES.
7. The SERIALLY_REUSABLE pragma specifies that the package state is needed for only one call to the server (for example, an OCI call to the database or a stored procedure invocation through a database link). Do not confuse here OCI with Oracle Cloud Infrastructure! Here it means Oracle Call Interface. After this call, the storage for the package variables can be reused, reducing the memory overhead for long-running sessions. This pragma is appropriate for packages that declare large temporary work areas that are used once in the same session.
The SERIALLY_REUSABLE pragma can appear in the declare_section of the specification of a bodiless package, or in both the specification and body of a package, but not in only the body of a package.
8. The UDF pragma tells the compiler that the PL/SQL unit is a user defined function that is used primarily in SQL statements, which might improve its performance.
9. The next pragma is GoldenGate related. Procedures and packages with the pragma UNSUPPORTED stop apply at the point of procedure invocation so that manual intervention can be taken. Check the list of the packages and procedures that are pragma constructs for replication. Any package or procedure not in this list is not considered a pragma construct for PL/SQL replication and is equivalent to pragma NONE. Note also the 4 pragma options for procedures: AUTO, MANUAL, UNSUPPORTED, and NONE.
10. The latest one (available from Oracle 21c), SUPPRESSES_WARNING_6009 pragma marks a subroutine to indicate that the PLW-06009 warning is suppressed at its call site in an OTHERS exception handler. The marked subroutine has the same effect as a RAISE statement and suppresses the PLW-06009 compiler warning.
The OTHERS exception handler does not issue the compiler warning PLW-06009 if an exception is raised explicitly using either a RAISE statement or the RAISE_APPLICATION_ERROR procedure as the last statement. Similarly, a call to a subroutine marked with the SUPPRESSES_WARNING_6009 pragma, from the OTHERS exception handler, does not issue the PLW-06009 warning.
It is worth checking the example from the link above, not that first you need to run ALTER SESSION SET PLSQL_WARNINGS=’ENABLE:(6009)’;
And here are the 6 undocumented pragmas:
PRAGMA BUILTIN defines SQL built-in functions and operators and is an internal pragma being used in the sys.standard package
PRAGMA FIPSFLAG is another internal pragma used in sys.standard and most likely has something to do with FIPS from NIST
PRAGMA INTERFACE is gateway for internal functions to C libraries and is used in the sys.standard package specification
PRAGMA NEW_NAMES is another internal pragma that restricts the use of particular new entries in sys.standard
PRAGMA SUPPLEMENTAL_LOG_DATA is used by Oracle Locator/Spatial and/or Logminer – I am not 100% sure
PRAGMA TIMESTAMP sets/modifies the timestamp values of SYS packages
Read MoreJulian Dontcheff’s Database Blog
