PRAGMA in PL/SQL

This keyword signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They do not affect the meaning of a program; they simply convey information to the compiler.

In this context, the term routine includes

• Top-level (not nested) anonymous PL/SQL blocks
• Local, standalone, and packaged functions and procedures
• Methods of a SQL object type
• Database triggers

You cannot use the pragma to mark all subprograms in a package (or all methods in an object type) as autonomous. Only individual routines can be marked autonomous. You can code the pragma anywhere in the declarative section of a routine. But, for readability, code the pragma at the top of the section.

AUTONOMOUS_TRANSACTION Pragma
Instructs the PL/SQL compiler to mark a routine as autonomous (independent). An autonomous transaction is an independent transaction started by another transaction, the main transaction. Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction.

EXCEPTION_INIT Pragma
The pragma EXCEPTION_INIT associates an exception name with an Oracle error number. That lets you refer to any internal exception by name and to write a specific handler for it instead of using the OTHERS handler.

You can use EXCEPTION_INIT in the declarative part of any PL/SQL block, subprogram, or package. The pragma must appear in the same declarative part as its associated exception, somewhere after the exception declaration.

Be sure to assign only one exception name to an error number.

RESTRICT_REFERENCES Pragma
To be callable from SQL statements, a stored function must obey certain "purity" rules, which are meant to control side effects. If any SQL statement inside the function body violates a rule, you get an error at run time (when the statement is parsed). To check for violations of the rules, you can use the pragma (compiler directive) RESTRICT_REFERENCES. The pragma asserts that a function does not read and/or write database tables and/or package variables.

You can declare the pragma RESTRICT_REFERENCES only in a package spec or object type spec. You can specify up to four constraints (RNDS, RNPS, WNDS, WNPS) in any order. To call the function from parallel queries, you must specify all four constraints. No constraint implies another. For example, WNPS does not imply RNPS.

SERIALLY_REUSABLE Pragma
The pragma SERIALLY_REUSABLE lets you mark a package as serially reusable. You can so mark a package if its state is needed only for the duration of one call to the server (for example, an OCI call to the server or a server-to-server RPC). You can mark a bodiless package as serially reusable. If a package has a spec and body, you must mark both. You cannot mark only the body.

more...

PL/SQL Performance init parameters

Configuring PL/SQL for better Performance:
PLSQL_WARNING
PLSQL_DEBUG
PLSQL_OPTIMIZE_MODE
PLSQL_CODE_TYPE: specifies whether to compile PL/SQL code into default interpreted byte code or native machine code.

Procedures, Functions and Packages

Working with Functions:
Functions are PL/SQL programs that execute zero or more statements and returns value through a RETURN statement. Functions can also receive or return zero or more values through their parameters.

Ex: CREATE OR REPLACE FUNCTION is_weekend (check_date IN DATE DEFAULT SYSDATE)
RETURN VARCHAR2 AS
BEGIN
CASE TO_CHAR(check_date, ‘DY’)
WHEN ‘SAT’ THEN
RETURN ‘YES’;
WHEN ‘SUN’ THEN
RETURN ‘YES’;
ELSE
RETURN ‘NO’;
END CASE;
END;

SQL> call dbms_output.put_line(is_weekend(SYSDATE));

Call completed.

SQL> set serveroutput on;
SQL> call dbms_output.put_line(is_weekend(SYSDATE));
NO

Call completed.

Working with Procedures
Procedures are PL/SQL programs that execute one or more statements.

CREATE OR REPLACE PROCEDURE archive_orders
(cust_id IN NUMBER, retention IN NUMBER) IS
BEGIN
DELETE orders
WHERE customer = cust_id
AND order_date < style="color: rgb(255, 0, 0);">Working with Packages:
A package is a container for functions, procedures, and data structures, such as records, cursors, variables and constants. A package has a publicly visible portion, called the specification (or spec) and a private portion called the package body. Spec describes the programs and the data structures that can be accessed from other programs. The package body contains the implementation of functions and procedures. Package spec is identified in the data dictionary as the type PACKAGE, and the package body is identified as the type PACKAGE BODY.

Creating a package spec
CREATE OR REPLACE PACKAGE table_util IS
FUNCTION version RETURN varchar2;
PROCEDURE truncate (table_name IN varchar2);
END table_util;

Privileges on packages are granted at the package-spec level. The EXECUTE privilege on a package allows the grantee to execute any program or use any data structure declared in the package specification. A package body depends on a package spec having the same name.

Creating a package body
CREATE OR REPLACE PACKAGE BODY table_util IS
version_string VARCHAR2(8) := ‘1.0.0’;
FUNCTION version RETURN varchar2 IS
BEGIN
RETURN version_string;
END;
PROCEDURE truncate(table_name IN varchar2) IS
BEGIN
IF UPPER(table_name) = ‘ORDER_STAGE’
THEN
EXECUTE IMMEDIATE UPPER(table_name);
ELSE
RAISE_APPLICATION_ERROR(-20010, ‘Invalid table name ‘ || table_name);
END IF;
END;
END table_util;

To explicitly compile a PL/SQL program
ALTER PROCEDURE archive_orders COMPILE;
ALTER FUNCTION is_weekend COMPILE;
ALTER PACKAGE table_util COMPILE BODY;