PL/SQL is Oracle’s Procedural Language extension to SQL. This Oracle proprietary language was derived from Ada and has evolved to include a robust feature set, including sequential and conditional controls, looping constructs, exception handing, records, and collections, as well as object-oriented features such as methods, overloading, upcasting, and type inheritance.
Full knowledge of the PL/SQL language is well beyond the scope of the OCA/OCP exams, and more developers than DBAs create PL/SQL programs. But a significant number of database features are delivered as PL/SQL programs, and knowledge of how to identify and work with these programs is crucial to your effectiveness. In this section, you will learn what kinds of PL/SQL programs are available, when each is appropriate, and what configuration options are applicable to working with PL/SQL programs.
The exam covers five types of named PL/SQL programs, which are usually stored in the database: functions, procedures, packages, package bodies, and triggers. The name and source code for each stored PL/SQL program is available from the data dictionary views DBA_SOURCE and DBA_TRIGGERS, although some packages are supplied "wrapped," which means that the source code is a binary form. Programs can be wrapped with with the wrap utility.
Functions are PL/SQL programs that execute zero or more statements and return a value through a RETURN statement. Functions can also receive or return zero or more values through their parameters. Oracle provides several built-in functions such as the commonly used SYSDATE, COUNT, and SUBSTR functions. There are over 200 SQL functions that come with your Oracle10g database and hundreds of PL/SQL functions. Because functions have a return value, a datatype is associated with them. Functions can be invoked anywhere an expression of the same datatype is allowed. Here are some examples:
As a default value
DATE DEFAULT SYSDATE;
In an assignment
today := SYSDATE;
In a Boolean expression
IF TO_CHAR(SYSDATE,'Day') = 'Monday'
In a SQL expression
SELECT COUNT(*) FROM employees WHERE hire_date > SYSDATE-30;
In the parameter list of another procedure or function
Create a function with the CREATE FUNCTION statement, like this:
CREATE OR REPLACE FUNCTION is_weekend(
check_date IN DATE DEFAULT SYSDATE)
RETURN VARCHAR2 AS
WHEN 'SAT' THEN RETURN 'YES';
WHEN 'SUN' THEN RETURN 'YES';
ELSE RETURN 'NO';
Functions, like all named PL/SQL, have the OR REPLACE keywords available in the CREATE statement. When present, OR REPLACE tells the database to not raise an exception if the object already exists. This behavior differs from a DROP and CREATE, in that privileges are not lost during a REPLACE operation and any objects that reference this object will not become invalid.
Procedures are PL/SQL programs that execute one or more statements. Procedures can receive and return values only through their parameter lists. Unlike functions, only a few built-in procedures, such as RAISE_APPLICATION_ERROR are built into the PL/SQL language.
Ceate a procedure with the CREATE PROCEDURE statement, like this:
CREATE OR REPLACE PROCEDURE archive_orders (
cust_id IN NUMBER, retention IN NUMBER) IS
DELETE orders WHERE customer = cust_id AND
order_date < SYSDATE - retention;
INSERT INTO maint_log (action, action_date, who)
VALUES ('archive orders '||retention||' for
'||cust_id, SYSDATE, USER);
The keyword IS, in the above statement is synonymous with the keyword AS, seen in the last example function. Both are syntactically valid for all named SQL.
Invoke a procedure as a stand-alone statement within a PL/SQL program by using the
CALL or EXEC commands. Here is an example:
EXEC DBMS_OUTPUT.PUT_LINE('Hello world!');
PL/SQL procedure successfully completed.
CALL DBMS_OUTPUT.PUT_LINE('Hello world!');
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 for short) and a private portion called the package body. The package spec describes the programs and data structures that can be accessed from other programs. The package body contains the implementation of the procedures and functions. The package spec is identified in the data dictionary as the type PACKAGE, and the package body is identified as the type PACKAGE BODY.
To create a package spec, use the CREATE PACKAGE statement. In the following, the package spec table_util contains one function and one procedure:
CREATE OR REPLACE PACKAGE table_util IS FUNCTION version
PROCEDURE truncate (table_name IN VARCHAR2);
Privileges on a package 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. You cannot grant the EXECUTE privilege on only some of the programs declared in the spec.
A package body depends on a package spec having the same name. The package body can only be created after the spec. The package body implements the programs that were declared in the package spec and can optionally contain private programs and data accessible only from within the package body.
To create a package body, use the CREATE PACKAGE BODY statement:
CREATE OR REPLACE PACKAGE BODY table_util IS
Here is an example of a private variable that can be referenced only in the package body:
version_string VARCHAR2(8) := '1.0.0';
Here is the code for the version function:
RETURN VARCHAR2 IS
Here is the code for the truncate procedure:
PROCEDURE truncate (table_name IN VARCHAR2) IS
IF UPPER(table_name) = 'ORDER_STAGE' OR UPPER(table_name) = 'SALES_ROLLUP'
EXECUTE IMMEDIATE 'truncate table ' || UPPER(table_name);
ELSE RAISE_APPLICATION_ERROR(-20010, 'Invalid table for truncate:
The package name following the END statement is optional, but encouraged, as it improves readability.
Triggers are PL/SQL programs that are invoked in response to an event in the database. Three sets of events can be hooked, allowing you to integrate your business logic with the database in an event-driven manner. Triggers can be created on DML events, DLL events, and database events. These three trigger event classes provide developers and you, the DBA, with a robust toolkit with which to design, build, and troubleshoot systems.
DML triggers are invoked, or "fired," when the specified DML events occur. If the keywords FOR EACH ROW are included in the trigger definition, the trigger fires once for each row that is changed. If these keywords are missing, the trigger fires once for each statement that causes the specified change. If the DML event list includes the UPDATE event, the trigger can be further restricted to fire only when updates of specific columns occur.
The following example creates a trigger that fires before any insert and before an update to the hire_date column of the employee table:
CREATE OR REPLACE TRIGGER employee_trg
BEFORE INSERT OR UPDATE OF
hire_date ON employees FOR EACH ROW
IF INSERTING THEN -- if fired due to insert
:NEW.create_user := USER;
:NEW.create_ts := SYSTIMESTAMP;
ELSEIF UPDATING THEN -- if fired due to update
IF :OLD.hire_date <> :NEW.hire_date THEN
RAISE_APPLICATION_ERROR (-20013, 'update of hire_date not allowed');
This trigger will fire once for each row affected, because the keywords FOR EACH ROW are included. When the triggering event is an INSERT, two columns are forced to the specific values returned by USER and SYSTIMESTAMP. DML triggers cannot be created on SYS-owned objects. Table 7.1 shows the DML triggering events.
|Event||When It Fires|
|INSERT||When a row is added to a table or a view.|
|UPDATE||When an UPDATE statement changes a row in a table or view. Update triggers can also specify an OF clause to limit the scope of changes that fire this type of trigger.|
|DELETE||When a row is removed from a table or a view.|
Multiple triggers on a table fire in the following order:
Before statement triggers
Before row triggers
After row triggers
After statement triggers
DDL triggers fire either for DDL changes to a specific schema or to all schemas in the database. The keywords ON DATABASE specify that the trigger will fire for the specified event on any schema in the database.
The following is an example of a trigger that fires for a DDL event in only one schema:
CREATE OR REPLACE TRIGGER NoGrantToPublic BEFORE
GRANT ON engineering.SCHEMA DECLARE grantee_list dbms_standard.ora_name_list_t;
BEGIN -- get the list of grantees
counter := GRANTEE(grantee_list);
FOR loop_counter IN
LOOP -- if PUBLIC is on the grantee list, stop the action
IF REGEXP_LIKE(grantee_list(loop_counter), 'public','i') THEN
RAISE_APPLICATION_ERROR(-20113, 'No grant to PUBLIC allowed for '
In the preceding example, the DDL event is a GRANT statement issued by user engineering. The code examines the grantee list, and if it finds the special user/role PUBLIC, an exception is raised, causing the grant to fail.
|Event||When It Fires|
|ALTER||When an ALTER statement changes a database object.|
|ANALYZE||When the database gathers or deletes statistics or validates the structure of an object.|
|ASSOCIATE STATISTICS||When the database associates a statistic with a Database object with an ASSOCIATE STATISTICS statement.|
|AUDIT||When the database records an audit action (except FGA).|
|COMMENT||When a comment on a table or column is modified.|
|CREATE||When the database object is created.|
|DDL||In conjunction with any of the following: ALTER, ANALYZE, ASSOCIATE STATISTICS, AUDIT, COMMENT, CREATE, DISASSOCIATE STATISTICS, DROP GRANT, NOAUDIT, RENAME, REVOKE, or TRUNCATE.|
|DISASSOCIATE STATISTICS||When a database disassociates a statistic type from a database object with a DISASSOCIATE STATISTICS statement.|
|DROP||When a DROP statement removes an object from the database.|
|GRANT||When a GRANT statement assigns a privilege.|
|NOAUDIT||When a NOAUDIT statement changes database auditing.|
|RENAME||When a RENAME statement changes an object name.|
|REVOKE||When a REVOKE statement rescinds a privilege.|
|TRUNCATE||When a TRUNCATE statement purges a table.|
abase event triggers fire when the specified database-level event occurs. Most of these triggers are available only before or after the database event, but not both.
following example creates an after-server error trigger that sends an e-mail notification when an ORA-01555 error occurs:
CREATE OR REPLACE TRIGGER Email_on_1555_Err
AFTER SERVERERROR ON DATABASE
DECLARE mail_conn UTL_SMTP.connection;
smtp_relay VARCHAR2(32) := 'mailserver';
recipient_address VARCHAR2(64) := 'DBA@hotmail.com';
sender_address VARCHAR2 (64) := 'firstname.lastname@example.org';
mail_port NUMBER := 25;
IF USER = 'SYSTEM' THEN -- Ignore this error
IS_SERVERERROR (1555) THEN -- compose the message
msg := 'Subject: ORA-1555 error';
msg := msg||'Snapshot too old err at'||systimestamp; -- send email notice
mail_conn := UTL_SMTP.open_connection(smtp_relay, mail_port);
Be careful when using database triggers. Fully test them in development Before deploying them to production.
|Event||When It Fires|
|LOGON||When a database session is established—only AFTER trigger is allowed|
|LOGOFF||When a database session ends normally—only BEFORE trigger is allowed|
|STARTUP||When the database is opened—only AFTER trigger is allowed|
|SHUTDOWN||When the database is closed—only BEFORE trigger is allowed|
|SERVERERROR||When a database exception is raised—only AFTER trigger is allowed|
|SUSPEND||When a server error causes a transaction to be suspended|
The database automatically enables a trigger when you create it. After creating a trigger, you can disable it (temporarily prevent it from firing) or re-enable it. You can disable and enable triggers by name with an ALTER TRIGGER statement. Here are two examples:
ALTER TRIGGER after_ora60 DISABLE;
ALTER TRIGGER load_packages ENABLE;
Alternatively, you can enable and disable multiple DML triggers with an ALTER TABLE statement, like this:
ALTER TABLE employees DISABLE ALL TRIGGERS;
ALTER TABLE employees ENABLE ALL TRIGGERS;
You can query the STATUS column of the DBA_TRIGGERS or USER_TRIGGERS view to find out whether a trigger is enabled or disabled.
Oracle10g comes bundled with hundreds of built-in packages that give you significant capabilities for administering your database. Many features in the database are implemented through one or more of these built-in packages. To use the job scheduler, collect and manage optimizer statistics, implement fine- grained auditing, send e-mail from the database, and use Data Pump or Log Miner, you must engage the built-in packages. As you gain experience, you will use these built-in packages more extensively.
To view the names and parameter lists for stored programs (except triggers), use the SQL*Plus DESCRIBE command like this:
An extensive list of Oracle built-in PL/SQL packages is available in the manual PL/SQL Packages and Types Reference, a weighty 3,700-page tome. Fortunately, you don’t have to know all these programs to sit for the certification exam.
A PL/SQL program is invalidated whenever a dependent object is changed through the ALTER command. The database automatically recompiles the package body the next time it is called, but you can choose to compile invalid PL/SQL programs yourself and thus eliminate a costly recompile during regular system processing. To explicitly compile a named SQL program, use the ALTER … COMPILE statement, like this:
ALTER PROCEDURE archive_orders COMPILE; ALTER FUNCTION is_weekend COMPILE;
ALTER PACKAGE table_util COMPILE BODY;
Other objects, such as views or types, are similarly compiled.
not be appropriate for production or may need to be disabled to support legacy code. The database initialization parameters that affect PL/SQL performance include:
PL/SQL compiler warnings are new to Oracle10g. These optional compile- time warnings can help developers create better programs by identifying potential problems that might result in runtime errors or poor performance. In general, disable compile-time warnings in production. To ensure these warnings are disabled, execute the following:
ALTER SYSTEM SET plsql_warnings='DISABLE:ALL' SCOPE=BOTH;
The PLSQL_DEBUG parameter forces all subsequent PL/SQL compilations to be interpreted and include additional debugging information. Again, although this feature can be beneficial in development, disable it in production. To ensure that it is disabled, execute the following:
ALTER SYSTEM SET plsql_debug=FALSE SCOPE=BOTH;
Oracle10g introduced an optimizing compiler that significantly improves PL/SQL performance of computing-intensive programs. Unless testing has shown this optimization to be detrimental to your programs, enable it fully in both production and development. To fully enable this feature, execute this:
ALTER SYSTEM SET plsql_optimize_mode=2 SCOPE=BOTH;
The parameter PLSQL_CODE_TYPE specifies whether to compile the PL/SQL code into the default-interpreted byte code or native machine code. The native machine code provides faster runtime performance at a cost of longer compilation times and slightly greater administrative overhead. To enable native compilation, you need the C compiler supplied by your platform vendor. Set PLSQL_NATIVE_LIBRARY_DIR and make sure the directory exists. Finally, set your PLSQL_CODE_TYPE to 'NATIVE' and CREATE OR REPLACE your programs.
Directory objects are named directory locations on the database server. Directories are used with several database features, including BFILEs, external tables, utl_file, and Data Pump. Of these, only Data Pump is germane to the certification exam.
Under Unix, you create directories with the CREATE DIRECTORY statement, like this:
CREATE DIRECTORY dump_dir AS '/oracle/data_pump/dumps';
CREATE DIRECTORY log_dir AS '/oracle/data_pump/logs';
Under Windows, you create directories like this:
CREATE DIRECTORY dpump_dir AS 'G:\datadumps';
Directories are not schema objects, like tables or synonyms, as they are not owned by a schema. Instead, directories are like profiles or roles in that they are owned by the database. To control access to a directory, you need to grant the READ or WRITE object privilege on that directory, like this:
GRANT read, write ON DIRECTORY dump_dir TO PUBLIC;
To create directories, you must have the CREATE ANY DIRECTORY system privilege. By default, only users SYSTEM and SYS have this privilege. Be careful in granting this system privilege to users, because the database employs the operating system credentials of the database instance owner.