PL/SQL

PL/SQL is an acronym for Programming Language for SQL or just Programming Language SQL. PL/SQL is effectively a modular programming language and SQL scripting language. The only problems with PL/SQL are twofold. The first issue are it roots which are as an extension or wrapper for SQL, making it originally somewhat primitive, as relational database stored procedure languages usually are. The second issue with PL/SQL is that it is interpretive. Interpretive implies not compiled into binary and thus performance is slow. The result is that current versions of PL/SQL can be syntactically complex to the point of being capable of intensive application level complex processing. The obvious result is extremely poorly performing applications.

So what makes PL/SQL a programming language? Programming languages have certain constructs. Generally these constructs encompass some type of structural integrity, strong typing using variables and datatypes plus a set of programming controls. PL/SQL has the following constructs.

Two other factors must be considered for PL/SQL. The first is obviously database access since PL/SQL is a database access programming language and the second is Oracle provided PL/SQL packaged functionality. Let's start by going through each of the above mentioned aspects of PL/SQL in turn.

Variables and Datatypes in PL/SQL

Variables in PL/SQL

The following image shows how variables are declared and referred inside a block of PL/SQL code. Note how the VAL variable is set using the instantiation operator (:=).

Scope of Variables

Variable scope is the definable or accessible area or level of a variable. Examine the following graphic and note the following points.

Datatypes in PL/SQL

A multitude of datatypes can be used in PL/SQL including all datatypes which can be used as table column definitions plus a few others which cannot. Also included in this section is a quick summary of object datatypes with reference to two other older web pages, covering objects and object datatypes available in Oracle plus large object datatypes use in Oracle.

Datatypes Specific to PL/SQL

Some datatypes are specific to PL/SQL and cannot be used in table creation.

Object Datatypes and Capabilities

Object and object datatype definitions encompass collections, reference pointers, classes and methods plus binary object storage.

Collections

Reference Pointers

Reference datatypes can be used to reference or dereference collection elements using type casting, much like object type casting of classes in order to cast a class as an abstract or a specialization of another class within a hierarchical structure of classes, applying the type casted class definition, inluding its methods and types.

x := Class(Class(Object))

Classes and Methods

Classes with associated or encapsulated methods and processing can be constructed in PL/SQL using constructor, member, map and order methods.

PL/SQL Programming Controls or Constructs

PL/SQL programming controls can be broken into three section of selection, loops and sequences.

Selection

IF

IF conditional expression 1 THEN
	…
[ ELSIF conditional expression  2 THEN ]
	…
[ ELSE
	… ]
END IF;

CASE

Case statements can occur in one of two forms.

As a Search Condition

CASE
	WHEN search condition 1 THEN
		…
	[ WHEN search condition 2 THEN 		… ]
	[ ELSE … ]
END CASE;

As a Selector and Expression

CASE selector
	 WHEN expression 1 THEN
		…
	[ WHEN expression 2 THEN ]
		…
	[ ELSE … ]
END CASE;

Loops

FOR LOOP

FOR counter IN [ REVERSE ] lower..higher LOOP
   	…
	EXIT WHEN expression;
END LOOP;

WHILE LOOP

WHILE condition LOOP
	…
	[ EXIT WHEN expression; ]
END LOOP;

LOOP … END LOOP

This loop has two forms where the first shown is the equivalent of a WHILE loop since the EXIT WHEN option is at the start of the LOOP.

LOOP
	[ EXIT WHEN expression; ]
	…
END LOOP;

The second form is the equivalent of an UNTIL loop since the EXIT WHEN option is at the end of the loop.

LOOP
	…
	[ EXIT WHEN expression; ]
END LOOP;

Sequences

GOTO

As seen in the diagram GOTO allows label definitions and specified jumps to those labels.

NULL

BEGIN
	…
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
	NULL;
END;
/

Block or Modular Structure

Blocks and Exception Trapping

As can be seen in the following diagram blocks are delineated by the keywords BEGIN and END. Additionally blocks can be nested within blocks.

The next image shows multiple exception traps, each applicable to the block they are contained within. An expception occuring within a block without an exception trap will cause the exception to be passed outwards to the calling block or procedure.

Anonymous Procedures

Blocks can be named or anonymous. A named procedure can be stored in the database and re-executed, an anonymouns procedure cannot.

The following procedure is not named and thus anonymous. When entered into a tool such as SQL*Plus it will be interpreted and executed, once.

SET SERVEROUTPUT ON;
DECLARE
	i INTEGER;
BEGIN
	FOR i IN 1..10 LOOP
		DBMS_OUTPUT.PUT_LINE(TO_CHAR(i)||' of to 10');
	END LOOP;
EXCEPTION WHEN OTHERS THEN
	ROLLBACK;
	RAISE;
END;
/
SET SERVEROUTPUT OFF;

Named Procedures or Blocks

Procedures

CREATE PROCEDURE Syntax

CREATE [ OR REPLACE ] PROCEDURE [schema.]procedure
( parameter { IN | OUT | IN OUT } datatype [, ... ] )
{ IS | AS }
BEGIN
 	… PL/SQL statements …
[ EXCEPTION WHEN condition THEN ]
	… PL/SQL statements …
END;
/

Parameter settings are as follows.

ALTER and DROP PROCEDURE Syntax

ALTER PROCEDURE [schema.]procedure COMPILE
	[ DEBUG ] [ REUSE SETTINGS ];


DROP PROCEDURE [schema.]procedure;

Let's say we wanted to increase the LIST_PRICE column values for a particular publisher. Let's examine editions printed by a specific publisher first.

COL title FORMAT A16 HEADING "Publication";
SELECT pl.title, e.list_price
FROM publication pl JOIN edition e USING (publication_id)
	JOIN publisher pu USING (publisher_id)
WHERE pu.name = 'Del Rey Books';

Publication      LIST_PRICE
---------------- ----------
Foundation             7.99
Foundation             4.99
Footfall               7.99

This procedure will change prices of all editions for a specified publisher by a ratio.

CREATE OR REPLACE PROCEDURE PriceIncrease(pPublisher IN VARCHAR2, pIncrease IN FLOAT) AS
BEGIN
	IF pPublisher IS NOT NULL AND pIncrease IS NOT NULL THEN
		UPDATE edition SET list_price = ROUND(list_price * pIncrease, 2)
		WHERE publisher_id = (SELECT publisher_id FROM publisher WHERE name = pPublisher);
	END IF;
	COMMIT;
EXCEPTION WHEN OTHERS THEN
	ROLLBACK;
	RAISE;
END;
/
ALTER PROCEDURE PriceIncrease COMPILE;

EXEC PriceIncrease('Del Rey Books',1.1);

Now let's check the new prices.

COL title FORMAT A16 HEADING "Publication";
SELECT pl.title, e.list_price
FROM publication pl JOIN edition e USING (publication_id)
	JOIN publisher pu USING (publisher_id)
WHERE pu.name = 'Del Rey Books';

Publication      LIST_PRICE
---------------- ----------
Foundation             8.79
Foundation             5.49
Footfall               8.79

Functions

A function is defined in the same way as a procedure except that it returns a value.

Function Syntax

CREATE [ OR REPLACE ] FUNCTION [schema.]procedure
( parameter { IN | OUT | IN OUT } datatype [, ... ] ) RETURN datatype
{ IS | AS }
BEGIN
 	… PL/SQL statements …
[ EXCEPTION WHEN condition THEN ]
	… PL/SQL statements …
END;
/

ALTER FUNCTION [schema.]procedure COMPILE
	[ DEBUG ] [ REUSE SETTINGS ];

DROP FUNCTION [schema.]procedure;

Here is an example function.

--
--F = (C * 9/5) + 32
--

CREATE OR REPLACE FUNCTION CToF(C IN NUMBER DEFAULT 0) RETURN NUMBER IS
BEGIN
	RETURN ROUND(((C * 9/5) + 32), 0);
END;
/
ALTER FUNCTION CToF COMPILE;
/

set serveroutput on;
exec dbms_output.put_line('0C = '||TO_CHAR(CToF(0))||'F.');
exec dbms_output.put_line('10C = '||TO_CHAR(CToF(10))||'F.');
exec dbms_output.put_line('20C = '||TO_CHAR(CToF(20))||'F.');
exec dbms_output.put_line('30C = '||TO_CHAR(CToF(30))||'F.');
exec dbms_output.put_line('40C = '||TO_CHAR(CToF(40))||'F.');
set serveroutput off;

DROP FUNCTION CToF;

Triggers

A trigger is used to detect database events where the trigger responds to that event by executing its code. Triggers can be used to maintain Referential Integrity of data or for logging and auditing. Since triggers are PL/SQL which is interpretive and not compiled, extensive use of triggers can cause serious performance problems. An additional issue with using triggers is convoluted and very large transactions. This is because triggers cannot contain transactional completion commands such as COMMIT or ROLLBACK. Triggers are not supposed to contain COMMIT or ROLLBACK commands due to their nature but still, use of triggers can cause serious performance problems as a result.

CREATE TRIGGER Syntax

A trigger can be executed before or after an event or as a replacement for another event (instead of).

CREATE [ OR REPLACE ] TRIGGER [schema.]trigger
   { BEFORE | AFTER | INSTEAD OF }
   { INSERT [ OR UPDATE [ OF column [, column ... ] ] [ OR DELETE ] ] }
      ON [schema.]table
   [ REFERENCING
      { OLD [ AS ] old | NEW [ AS ] new | PARENT [ AS ] parent } ]
   [ FOR EACH ROW ]
   [ WHEN ( condition ) ];

Any combination of insert, update and delete events can be detected and reacted upon.

CREATE [ OR REPLACE ] TRIGGER [schema.]trigger
   { BEFORE | AFTER | INSTEAD OF }
   { INSERT [ OR UPDATE [ OF column [, column ... ] ] [ OR DELETE ] ] }
      ON [schema.]table
   [ REFERENCING
      { OLD [ AS ] old | NEW [ AS ] new | PARENT [ AS ] parent } ]
   [ FOR EACH ROW ]
   [ WHEN ( condition ) ];

Column values can be referenced as old.column and new.column. The REFERENCING clause can rename old. and new..

CREATE [ OR REPLACE ] TRIGGER [schema.]trigger
   { BEFORE | AFTER | INSTEAD OF }
   { INSERT [ OR UPDATE [ OF column [, column ... ] ] [ OR DELETE ] ] }
      ON [schema.]table
   [ REFERENCING
      { OLD [ AS ] old | NEW [ AS ] new | PARENT [ AS ] parent } ]
   [ FOR EACH ROW ]
   [ WHEN ( condition ) ];

A trigger specified as FOR EACH ROW will trigger the event for every row operated on in a SQL code statement. Otherwise the trigger is fired once for each statement, regardless of how many rows the statement operates on.

CREATE [ OR REPLACE ] TRIGGER [schema.]trigger
   { BEFORE | AFTER | INSTEAD OF }
   { INSERT [ OR UPDATE [ OF column [, column ... ] ] [ OR DELETE ] ] }
      ON [schema.]table
   [ REFERENCING
      { OLD [ AS ] old | NEW [ AS ] new | PARENT [ AS ] parent } ]
   [ FOR EACH ROW ]
   [ WHEN ( condition ) ];

The WHEN clause specifies a SQL conditional clause which even though the trigger may be fired by a DML event, it can be aborted. DML event triggers must be declared as FOR EACH ROW for the WHEN condition to be used.

CREATE [ OR REPLACE ] TRIGGER [schema.]trigger
   { BEFORE | AFTER | INSTEAD OF }
   { INSERT [ OR UPDATE [ OF column [, column ... ] ] [ OR DELETE ] ] }
      ON [schema.]table
   [ REFERENCING
      { OLD [ AS ] old | NEW [ AS ] new | PARENT [ AS ] parent } ]
   [ FOR EACH ROW ]
   [ WHEN ( condition ) ];

ALTER and DROP TRIGGER Syntax

ALTER TRIGGER [schema.]trigger
	{ ENABLE | DISABLE
	| RENAME TO trigger
	| COMPILE [ DEBUG ] [ RESUSE SETTINGS ] };

DROP TRIGGER [schema.]procedure;

Let's create a log file for events occuring on the Review table in the Books schema.

CREATE TABLE log (event VARCHAR2(128), event_date DATE DEFAULT SYSDATE);

CREATE OR REPLACE TRIGGER logI
	AFTER INSERT ON review FOR EACH ROW
BEGIN
	INSERT INTO log(event) VALUES('New review inserted for '||
		(SELECT title FROM publication WHERE publication_id = :new.publication_id)||'.');
END;
/

CREATE OR REPLACE TRIGGER logU
	AFTER UPDATE OF text ON review FOR EACH ROW
BEGIN
	INSERT INTO log(event) VALUES('Review changed for '||
		(SELECT title FROM publication WHERE publication_id = :old.publication_id)||'.');
END;
/

CREATE OR REPLACE TRIGGER logD
	AFTER DELETE ON review FOR EACH ROW
BEGIN
	INSERT INTO log(event) VALUES('Review deleted for '||
		(SELECT title FROM publication WHERE publication_id = :old.publication_id)||'.');
END;
/

INSERT INTO review VALUES(
	 review_seq.NEXTVAL
	,(SELECT publication_id FROM publication WHERE title = 'Second Foundation')
	,SYSDATE
	,'This is a review');

UPDATE review SET text = 'The review text has been changed'
WHERE publication_id = (SELECT publication_id FROM publication WHERE title = 'Second Foundation');

DELETE FROM review WHERE publication_id = (SELECT publication_id FROM publication WHERE title = 'Second Foundation');

Now let's look at our log table.

COLUMN event FORMAT A64;
SELECT event_date, event FROM log;

EVENT_DAT EVENT
--------- -----------------------------------------------
15-OCT-03 New review inserted for Second Foundation.
15-OCT-03 Review changed for Second Foundation.
15-OCT-03 Review changed for Second Foundation.
15-OCT-03 Review deleted for Second Foundation.
15-OCT-03 Review deleted for Second Foundation.

I could disable triggers and prevent a response.

ALTER TRIGGER trigger name DISABLE;

Database Access using PL/SQL

DML in PL/SQL

In the following syntax diagram for PL/SQL SELECT operations placeholder variables are required for each expression in the SELECT statement list.

SELECT { [alias.]column | expression | [alias.]* [ , … ] }
INTO variable [, variable …] FROM [schema.]table [alias]
[ WHERE [ [schema.]table.|alias.] { column | expression }
   comparison { … } [ { AND | OR } [ NOT ] … ] ]
[ ORDER BY { { column | expression | position }
      [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, … ] } ]
[ GROUP BY { expression | rollup-cube | grouping-sets }
      [,  { expression | rollup-cube | grouping-sets } ... ]
   [ HAVING condition ]
];

The INSERT, UPDATE and DELETE statements have a RETURNING clause in addition for DML in PL/SQL. The RETURNING clause can return single variables for a single row or arrays for multiple rows.

INSERT INTO [schema.]table [ ( column [, column …] ) ]
VALUES ( { expr | DEFAULT | (subquery) }
		[, { expr | DEFAULT | (subquery) } ... ] )
[ RETURNING expr [, expr ] INTO variable [, variable ]  ];

UPDATE [schema.]table
SET column = expr | (subquery) | DEFAULT
	| ( column [, column … ] ) = (subquery)
[ WHERE ... ]
[ RETURNING expr [, expr] INTO variable [, variable ] ) ];

DELETE [ FROM ] [schema.]table
[ WHERE ... ]
[ RETURNING expr [, expr] INTO variable [, variable ] ) ];

Here is an anonymous procedure retrieving a single value.

SET SERVEROUTPUT ON;

DECLARE
	i INTEGER;
BEGIN
	SELECT COUNT(*) INTO i FROM subject;
	DBMS_OUTPUT.PUT_LINE('There are '||TO_CHAR(i)||' Subjects in the database');
END;
/

This example retrieves more than one column. Note how variable names can be the same as column names.

DECLARE
	subject_id subject.subject_id%TYPE;
	name subject.name%TYPE;
BEGIN
	SELECT subject_id, name INTO subject_id, name
	FROM subject WHERE ROWNUM = 1;
	DBMS_OUTPUT.PUT_LINE('The first subject stored is '||TRIM(TO_CHAR(subject_id))||': '||TRIM(name));
END;
/

Let's use the RETURNING INTO clause.

DECLARE
	author_id author.author_id%TYPE;
	publisher_id publisher.publisher_id%TYPE;
	publication_id publication.publication_id%TYPE;
BEGIN
	INSERT INTO author VALUES(author_seq.nextval, 'Kurt Vonnegut')
		RETURNING author_id INTO author_id;
	INSERT INTO publisher VALUES(publisher_seq.nextval, 'Berkley Publishing Group')
		RETURNING publisher_id INTO publisher_id;
	INSERT INTO publication VALUES(publication_seq.nextval, 
		(SELECT subject_id FROM subject WHERE name = 'Modern American'),
		author_id, 'Hocus Pocus')
		RETURNING publication_id INTO publication_id;
	INSERT INTO edition VALUES(0425130215, publisher_id, publication_id, '30-NOV-91', 
		NULL, 7.99, 'Paperback', 98173, NULL);
	COMMIT;
EXCEPTION WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLERRM));
	ROLLBACK;
END;
/

Cursors

Cursors are used by PL/SQL to allow for operations on more than a single row. There are two types of cursors, implicit and explicit. As the two different terms imply, the former is built automatically and the latter is constructed manually using PL/SQL commands. Cursors allow scrolling through a set of retrieved rows. In theory every SELECT statement creates a cursor because a cursor is the chunk of memory allocated to the results of a SELECT statement.

The Internal "SQL" Cursor

The internal cursor SQL. can be inspected to find values for the most recently executed cursor.

BEGIN
	DELETE FROM author WHERE name = 'Douglas Adams';
	IF SQL%NOTFOUND THEN
		INSERT INTO author VALUES(author_seq.nextval, 'Douglas Adams');
		DBMS_OUTPUT.PUT_LINE('New author added.');
	ELSE
		DBMS_OUTPUT.PUT_LINE('Author deleted.');
	END IF;
	COMMIT;
EXCEPTION WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
	ROLLBACK;
END;
/

There is another way.

DECLARE
	author_id author.author_id%TYPE;
BEGIN
	BEGIN
		SELECT author_id INTO author_id FROM author WHERE name = 'Douglas Adams';
		DELETE FROM author WHERE name = 'Douglas Adams';
		DBMS_OUTPUT.PUT_LINE('Author Deleted.');
	EXCEPTION WHEN NO_DATA_FOUND THEN
		INSERT INTO author VALUES(author_seq.nextval, 'Douglas Adams');
		DBMS_OUTPUT.PUT_LINE('New author added.');
	END;
	COMMIT;
EXCEPTION WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
	ROLLBACK;
END;
/

Implicit Cursors

A cursor FOR loop is an creates an implicit cursor.

DECLARE
	CURSOR cAuthors IS SELECT * FROM author ORDER BY name;
BEGIN
	FOR rAuthor IN cAuthors LOOP
		DBMS_OUTPUT.PUT_LINE(rAuthor.name);
	END LOOP;
END;
/

Explicit Cursors

Explicit cursors have the following characteristics in relation to implicit cursors.

Here is an example of an explicit cursor.

DECLARE
	CURSOR cAuthors IS SELECT author_id, name FROM author ORDER BY name;
	rAuthor Author%ROWTYPE;
BEGIN
	OPEN cAuthors;
	LOOP
		FETCH cAuthors INTO rAuthor;
		EXIT WHEN cAuthors%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(rAuthor.name);
	END LOOP;
	CLOSE cAuthors;
EXCEPTION WHEN OTHERS THEN
	CLOSE cAuthors;
	RAISE;
END;
/

This example creates an explicit cursor generically, which can be altered at runitime.

DECLARE
	TYPE tAuthor IS REF CURSOR RETURN author%ROWTYPE;
	cAuthors tAuthor;
	rAuthor author%ROWTYPE;
BEGIN
	OPEN cAuthors FOR SELECT author_id, name FROM author WHERE name LIKE '%a%';
	LOOP
		FETCH cAuthors INTO rAuthor;
		EXIT WHEN cAuthors%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(rAuthor.name);
	END LOOP;
	CLOSE cAuthors;
EXCEPTION WHEN OTHERS THEN
	CLOSE cAuthors;
	RAISE;
END;
/

This example is also generic but using a parameter.

CREATE OR REPLACE PROCEDURE getAuthors(pattern IN VARCHAR2 DEFAULT NULL) AS
	TYPE tAuthor IS REF CURSOR RETURN author%ROWTYPE;
	cAuthors tAuthor;
	rAuthor Author%ROWTYPE;
BEGIN
	OPEN cAuthors FOR SELECT author_id, name FROM author WHERE name LIKE pattern;
	LOOP
		FETCH cAuthors INTO rAuthor;
		EXIT WHEN cAuthors%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(rAuthor.name);
	END LOOP;
	CLOSE cAuthors;
EXCEPTION WHEN OTHERS THEN
	CLOSE cAuthors;
	RAISE;
END;
/

EXEC getAuthors('%e%');

James Blish
Jerry Pournelle
Kurt Vonnegut
Larry Niven
William Shakespeare

PL/SQL procedure successfully completed.

This example Security implementation contains some interesting example cursor coding.

Dynamic PL/SQL

Dynamic SQL is literally dynamic or generic, constructed at run-time, and has the following characteristics.

Let's look at some examples. Count all rows in all tables.

DECLARE
	CURSOR cTables IS SELECT table_name FROM user_tables ORDER BY table_name;
	rows INTEGER;
BEGIN
	FOR rTable IN cTables LOOP
		EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||rTable.table_name INTO rows;
		DBMS_OUTPUT.PUT_LINE(RPAD(rTable.table_name,30,' ')||' = '||TO_CHAR(rows));
	END LOOP;
END;
/

AUTHOR                         = 7
COAUTHOR                       = 2
CRITIC                         = 2
EDITION                        = 18
PUBLICATION                    = 12
PUBLISHER                      = 12
REVIEW                         = 10
REVIEWER                       = 0
SUBJECT                        = 17

PL/SQL procedure successfully completed.

Now do the same thing but using a variable.

DECLARE
	CURSOR cTables IS SELECT table_name FROM user_tables ORDER BY table_name;
	sqlcode VARCHAR2(4000);
	rows INTEGER;
BEGIN
	FOR rTable IN cTables LOOP
		sqlcode := 'SELECT COUNT(*) FROM '||rTable.table_name;
		EXECUTE IMMEDIATE sqlcode INTO rows;
		DBMS_OUTPUT.PUT_LINE(RPAD(rTable.table_name,30,' ')||' = '||TO_CHAR(rows));
	END LOOP;
END;
/

Similarly we could do the same thing using SQL*Plus scripting without a procedure.

set termout off echo off feed off trimspool on head off pages 0 timing off;
spool count.sql;
select 'select count(*),'''||table_name||''' from '||table_name||';' from user_tables order by table_name;
spool off;
set termout on;
spool count.log;
@@count.sql;
spool off;

Here is a simple example incorporating a DDL command. This example would require explicit privileges.

CREATE OR REPLACE PROCEDURE NewTable(tab IN VARCHAR2) AS
	id INTEGER;
	name VARCHAR2(32);
	sqlcode VARCHAR2(4000);
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE '||tab||'(id NUMBER, name VARCHAR2(32))';
	FOR id IN 1..10 LOOP
		sqlcode := 'INSERT INTO '||tab||' VALUES(:1, :2)';
		name := TO_CHAR(id);
 		EXECUTE IMMEDIATE sqlcode USING id, name;
	END LOOP;
	COMMIT;
EXCEPTION WHEN OTHERS THEN
	ROLLBACK;
	DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
	RAISE;
END;
/

This procedure uses the DBMS_SQL procedure.

PROCEDURE dynSQL (pSTR IN VARCHAR2 DEFAULT '') AS
	cCURSOR INTEGER DEFAULT 0;
	lROWS INTEGER DEFAULT 0;
BEGIN
	DBMS_OUTPUT.PUT_LINE (pSTR);
	cCURSOR := DBMS_SQL.OPEN_CURSOR;
	DBMS_SQL.PARSE (cCURSOR, pSTR, DBMS_SQL.NATIVE);
	lROWS := DBMS_SQL.EXECUTE (cCURSOR);
	DBMS_SQL.CLOSE_CURSOR (cCURSOR);
EXCEPTION WHEN OTHERS THEN
	IF DBMS_SQL.IS_OPEN (cCURSOR) THEN
		DBMS_SQL.CLOSE_CURSOR (cCURSOR);
		ROLLBACK;
	END IF;
	DBMS_OUTPUT.PUT_LINE (SQLERRM(SQLCODE));
	RAISE;
END;
/

*Oracle Provided PL/SQL Packages

There are a multitude of built-in Oracle PL/SQL packages for all sorts of applications. Oracle manuals are the best reference.

*PL/SQL Performance and Confiuration Options

Identify configuration options that affect PL/SQL performance

perhaps these - from OEM

PL/SQL
	utl_file_dir
	plsql_v2_compatibility
	plsql_compiler_glags
	plsql_native_library_dir
	plsql_native_library_subdir_count
	remote_dependencies_mode