Home > Oracle Database Usage Infor... > Subprograms and Packages: U...
Oracle offers the capability to store programs in the database. This functionality enables commonly required code to be written and tested once and then accessed by any application that requires the code. Database-resident program units also ensure that the same processing is applied to the data when the code is invoked, making the development of applications easier and providing consistency between developers.
You can write database-resident programs in PL/SQL and manage source types such as PL/SQL packages, procedures, functions, and triggers. The actions include creating, compiling, creating synonyms for, granting privileges on, and showing dependencies for these source types.
The topic discusses the development of subprograms and packages with PL/SQL. It includes the following topics:
See Also:
|
Note: Many examples use the Oracle HR sample schema. For information on the sample schemas, see Oracle Database Sample Schemas. |
PL/SQL is a modern, block-structured programming language. It provides several features that make developing powerful database applications very convenient. For example, PL/SQL provides procedural constructs, such as loops and conditional statements, that are not available in standard SQL.
You can directly enter SQL data manipulation language (DML) statements inside PL/SQL blocks, and you can use subprograms supplied by Oracle to perform data definition language (DDL) statements.
PL/SQL code runs on the server, so using PL/SQL lets you centralize significant parts of your database applications for increased maintainability and security. It also enables you to achieve a significant reduction of network overhead in client/server applications.
Note: Some Oracle tools, such as SQL Developer, contain a PL/SQL engine that lets you run PL/SQL locally. |
You can even use PL/SQL for some database applications instead of 3GL programs that use embedded SQL or Oracle Call Interface (OCI).
PL/SQL units include:
See Also:
|
An anonymous block is a PL/SQL unit that has no name. An anonymous block consists of an optional declarative part, an executable part, and one or more optional exception handlers.
The declarative part declares PL/SQL variables, exceptions, and cursors. The executable part contains PL/SQL code and SQL statements, and can contain nested blocks.
Exception handlers contain code that is invoked when the exception is raised, either as a predefined PL/SQL exception (such as NO_DATA_FOUND
or ZERO_DIVIDE
) or as an exception that you define.
Anonymous blocks are usually used interactively from a tool, such as SQL*Plus, or in a precompiler, OCI, or SQL*Module application. They are usually used to invoke stored subprograms or to open cursor variables.
The anonymous block in Example: Anonymous Block uses the DBMS_OUTPUT
package to print the names of all employees in the HR
.EMPLOYEES
table who are in department 20.
Anonymous Block
DECLARE last_name VARCHAR2(10); cursor c1 IS SELECT LAST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID = 20; BEGIN OPEN c1; LOOP FETCH c1 INTO last_name; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(last_name); END LOOP; END; /
Result:
Hartstein Fay
Exceptions let you handle Oracle Database error conditions with PL/SQL program logic, enabling your application to prevent the server from issuing an error that can cause the client application to end. The anonymous block in Example: Anonymous Block with Exception Handler for Predefined Error handles the predefined Oracle Database exception NO_DATA_FOUND
(which results in ORA-01403 if not handled).
Anonymous Block with Exception Handler for Predefined Error
DECLARE Emp_number INTEGER := 9999 Emp_name VARCHAR2(10); BEGIN SELECT LAST_NAME INTO Emp_name FROM EMPLOYEES WHERE EMPLOYEE_ID = Emp_number; DBMS_OUTPUT.PUT_LINE('Employee name is ' || Emp_name); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No such employee: ' || Emp_number); END; /
Result:
No such employee: 9999
You can also define your own exceptions; that is, you can declare them in the declaration part of a block and define them in the exception part of the block, as in Example: Anonymous Block with Exception Handler for User-Defined Exception.
Anonymous Block with Exception Handler for User-Defined Exception
DECLARE Emp_name VARCHAR2(10); Emp_number INTEGER; Empno_out_of_range EXCEPTION; BEGIN Emp_number := 10001; IF Emp_number > 9999 OR Emp_number < 1000 THEN RAISE Empno_out_of_range; ELSE SELECT LAST_NAME INTO Emp_name FROM EMPLOYEES WHERE EMPLOYEE_ID = Emp_number; DBMS_OUTPUT.PUT_LINE('Employee name is ' || Emp_name); END IF; EXCEPTION WHEN Empno_out_of_range THEN DBMS_OUTPUT.PUT_LINE('Employee number ' || Emp_number || ' is out of range.'); END; /
Result:
Employee number 10001 is out of range.
See Also:
|
A stored PL/SQL unit is a subprogram (procedure or function) or package that:
Has a name
Can take parameters, and can return values
Is stored in the data dictionary
Can be invoked by many users
If a subprogram belongs to a package, it is called a package subprogram; if not, it is called a standalone subprogram.
Topics:
Because a subprogram is stored in the database, it must be named. This distinguishes it from other stored subprograms and makes it possible for applications to invoke it. Each publicly-visible subprogram in a schema must have a unique name, and the name must be a legal PL/SQL identifier.
Note: If you plan to invoke a stored subprogram using a stub generated by SQL*Module, then the stored subprogram name must also be a legal identifier in the invoking host 3GL language, such as Ada or C. |
Stored subprograms can take parameters. In the procedure in Example: Anonymous Block with Exception Handler for User-Defined Exception, the department number is an input parameter that is used when the parameterized cursor c1
is opened.
Stored Procedure with Parameters
CREATE OR REPLACE PROCEDURE get_emp_names ( dept_num IN NUMBER ) IS emp_name VARCHAR2(10); CURSOR c1 (dept_num NUMBER) IS SELECT LAST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID = dept_num; BEGIN OPEN c1(dept_num); LOOP FETCH c1 INTO emp_name; EXIT WHEN C1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(emp_name); END LOOP; CLOSE c1; END; /
The formal parameters of a subprogram have three major attributes, described in Table: Attributes of Subprogram Parameters.
Attributes of Subprogram Parameters
Parameter Attribute | Description |
---|---|
Name |
This must be a legal PL/SQL identifier. |
Mode |
This indicates whether the parameter is an input-only parameter ( |
Data Type |
This is a standard PL/SQL data type. |
Topics:
Parameter modes define the action of formal parameters. You can use the three parameter modes, IN
(the default), OUT
, and IN
OUT
, with any subprogram. Avoid using the OUT
and IN
OUT
modes with functions. Good programming practice dictates that a function returns a single value and does not change the values of variables that are not local to the subprogram.
Table: Parameter Modes summarizes the information about parameter modes.
Parameter Modes
IN | OUT | IN OUT |
---|---|---|
The default. |
Must be specified. |
Must be specified. |
Passes values to a subprogram. |
Returns values to the caller. |
Passes initial values to a subprogram; returns updated values to the caller. |
Formal parameter acts like a constant. |
Formal parameter acts like an uninitialized variable. |
Formal parameter acts like an initialized variable. |
Formal parameter cannot be assigned a value. |
Formal parameter cannot be used in an expression; must be assigned a value. |
Formal parameter must be assigned a value. |
Actual parameter can be a constant, initialized variable, literal, or expression. |
Actual parameter must be a variable. |
Actual parameter must be a variable. |
See Also: Oracle Database PL/SQL Language Reference for details about parameter modes |
The data type of a formal parameter consists of one of these:
An unconstrained type name, such as NUMBER
or VARCHAR2
.
A type that is constrained using the %TYPE
or %ROWTYPE
attributes.
Note: Numerically constrained types such asNUMBER (2 ) or VARCHAR2 (20 ) are not allowed in a parameter list. |
Use the type attributes %TYPE
and %ROWTYPE
to constrain the parameter. For example, the procedure heading in Example: Stored Procedure with Parameters can be written as follows:
PROCEDURE get_emp_names(dept_num IN EMPLOYEES.DEPARTMENT_ID%TYPE)
This gives the dept_num
parameter the same data type as the DEPARTMENT_ID
column in the EMPLOYEES
table. The column and table must be available when a declaration using %TYPE
(or %ROWTYPE
) is elaborated.
Using %TYPE
is recommended, because if the type of the column in the table changes, it is not necessary to change the application code.
If the get_emp_names
procedure is part of a package, you can use previously-declared public (package) variables to constrain its parameter data types. For example:
dept_number NUMBER(2); ... PROCEDURE get_emp_names(dept_num IN dept_number%TYPE);
Use the %ROWTYPE
attribute to create a record that contains all the columns of the specified table. The procedure in Example: %TYPE and %ROWTYPE Attributes returns all the columns of the EMPLOYEES
table in a PL/SQL record for the given employee ID.
%TYPE and %ROWTYPE Attributes
CREATE OR REPLACE PROCEDURE get_emp_rec ( emp_number IN EMPLOYEES.EMPLOYEE_ID%TYPE, emp_info OUT EMPLOYEES%ROWTYPE ) IS BEGIN SELECT * INTO emp_info FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_number; END; /
Invoke procedure from PL/SQL block:
DECLARE
emp_row EMPLOYEES%ROWTYPE;
BEGIN
get_emp_rec(206, emp_row);
DBMS_OUTPUT.PUT('EMPLOYEE_ID: ' || emp_row.EMPLOYEE_ID);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT('FIRST_NAME: ' || emp_row.FIRST_NAME);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT('LAST_NAME: ' || emp_row.LAST_NAME);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT('EMAIL: ' || emp_row.EMAIL);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT('PHONE_NUMBER: ' || emp_row.PHONE_NUMBER);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT('HIRE_DATE: ' || emp_row.HIRE_DATE);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT('JOB_ID: ' || emp_row.JOB_ID);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT('SALARY: ' || emp_row.SALARY);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT('COMMISSION_PCT: ' || emp_row.COMMISSION_PCT);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT('MANAGER_ID: ' || emp_row.MANAGER_ID);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT('DEPARTMENT_ID: ' || emp_row.DEPARTMENT_ID);
DBMS_OUTPUT.NEW_LINE;
END;
/
Result:
EMPLOYEE_ID: 206 FIRST_NAME: William LAST_NAME: Gietz EMAIL: WGIETZ PHONE_NUMBER: 415.555.0100 HIRE_DATE: 07-JUN-94 JOB_ID: AC_ACCOUNT SALARY: 8300 COMMISSION_PCT: MANAGER_ID: 205 DEPARTMENT_ID: 110
Stored functions can return values that are declared using %ROWTYPE
. For example:
FUNCTION get_emp_rec (dept_num IN EMPLOYEES.DEPARTMENT_ID%TYPE) RETURN EMPLOYEES%ROWTYPE IS ...
You can pass PL/SQL tables as parameters to stored subprograms. You can also pass tables of records as parameters.
Note: When passing a user defined type, such as a PL/SQL table or record to a remote subprogram, to make PL/SQL use the same definition so that the type checker can verify the source, you must create a redundant loop back DBLINK so that when the PL/SQL compiles, both sources pull from the same location. |
Parameters can take initial values. Use either the assignment operator or the DEFAULT
keyword to give a parameter an initial value. For example, these are equivalent:
PROCEDURE Get_emp_names (Dept_num IN NUMBER := 20) IS ... PROCEDURE Get_emp_names (Dept_num IN NUMBER DEFAULT) IS ...
When a parameter takes an initial value, it can be omitted from the actual parameter list when you invoke the subprogram. When you do specify the parameter value on the invocation, it overrides the initial value.
Note: Unlike in an anonymous PL/SQL block, you do not use the keywordDECLARE before the declarations of variables, cursors, and exceptions in a stored subprogram. In fact, it is an error to use it. |
Use a text editor to write the subprogram. Then, using an interactive tool such as SQL*Plus, load the text file containing the procedure by entering:
@get_emp
This loads the procedure into the current schema from the get_emp
.sql
file (.sql
is the default file extension). The slash (/) after the code is not part of the code, it only activates the loading of the procedure.
Caution: When developing a subprogram, it is usually preferable to use the statementCREATE OR REPLACE PROCEDURE or CREATE OR REPLACE FUNCTION . This statement replaces any previous version of that subprogram in the same schema with the newer version, but without warning. |
You can use either the keyword IS
or AS
after the subprogram parameter list.
See Also: Oracle Database SQL Language Reference for the syntax of theCREATE FUNCTION and CREATE PROCEDURE statements |
Privileges Needed
To create a subprogram, a package specification, or a package body, you must meet these prerequisites:
You must have the CREATE
PROCEDURE
system privilege to create a subprogram or package in your schema, or the CREATE
ANY
PROCEDURE
system privilege to create a subprogram or package in another user's schema. In either case, the package body must be created in the same schema as the package.
Note: To create without errors (to compile the subprogram or package successfully) requires these additional privileges:
|
If the privileges of the owner of a subprogram or package change, then the subprogram must be reauthenticated before it is run. If a necessary privilege to a referenced object is revoked from the owner of the subprogram or package, then the subprogram cannot be run.
The EXECUTE
privilege on a subprogram gives a user the right to run a subprogram owned by another user. Privileged users run the subprogram under the security domain of the owner of the subprogram. Therefore, users need not be granted the privileges to the objects referenced by a subprogram. This allows for more disciplined and efficient security strategies with database applications and their users. Furthermore, all subprograms and packages are stored in the data dictionary (in the SYSTEM
tablespace). No quota controls the amount of space available to a user who creates subprograms and packages.
Note: Package creation requires a sort. The user creating the package must be able to create a sort segment in the temporary tablespace with which the user is associated. |
To alter a subprogram, you must first drop it using the DROP
PROCEDURE
or DROP
FUNCTION
statement, then re-create it using the CREATE
PROCEDURE
or CREATE
FUNCTION
statement. Alternatively, use the CREATE
OR
REPLACE
PROCEDURE
or CREATE
OR
REPLACE
FUNCTION
statement, which first drops the subprogram if it exists, then re-creates it as specified.
Caution: The subprogram is dropped without warning. |
A standalone subprogram, a standalone function, a package body, or an entire package can be dropped using the SQL statements DROP
PROCEDURE
, DROP
FUNCTION
, DROP
PACKAGE
BODY
, and DROP
PACKAGE
, respectively. A DROP
PACKAGE
statement drops both the specification and body of a package.
This statement drops the Old_sal_raise
procedure in your schema:
DROP PROCEDURE Old_sal_raise;
Privileges Needed
To drop a subprogram or package, the subprogram or package must be in your schema, or you must have the DROP
ANY
PROCEDURE
privilege. An individual subprogram within a package cannot be dropped; the containing package specification and body must be re-created without the subprograms to be dropped.
A PL/SQL subprogram executing on an Oracle Database instance can invoke an external subprogram written in a third-generation language (3GL). The 3GL subprogram runs in a separate address space from that of the database.
Using the PL/SQL function result cache can save significant space and time. Each time a result-cached PL/SQL function is invoked with different parameter values, those parameters and their result are stored in the cache. Subsequently, when the same function is invoked with the same parameter values, the result is retrieved from the cache, instead of being recomputed. Because the cache is stored in a shared global area (SGA), it is available to any session that runs your application.
If a database object that was used to compute a cached result is updated, the cached result becomes invalid and must be recomputed.
The best candidates for result-caching are functions that are invoked frequently but depend on information that changes infrequently or never.
For more information about the PL/SQL function result cache, see Oracle Database PL/SQL Language Reference.
A package is a collection of related program objects (for example, subprogram, variables, constants, cursors, and exceptions) stored as a unit in the database.
Using packages is an alternative to creating subprograms as standalone schema objects. Packages have many advantages over standalone subprograms. For example, they:
Let you organize your application development more efficiently.
Let you grant privileges more efficiently.
Let you modify package objects without recompiling dependent schema objects.
Enable Oracle Database to read multiple package objects into memory at once.
Can contain global variables and cursors that are available to all subprograms in the package.
Let you overload subprograms. Overloading a subprogram means creating multiple subprograms with the same name in the same package, each taking arguments of different number or data type.
See Also: Oracle Database PL/SQL Language Reference for more information about subprogram name overloading |
The specification part of a package declares the public types, variables, constants, and subprograms that are visible outside the immediate scope of the package. The body of a package defines both the objects declared in the specification and private objects that are not visible to applications outside the package.
Example: Creating PL/SQL Package and Invoking Packaged Subprogram creates a package that contains one stored function and two stored procedures, and then invokes a procedure.
Creating PL/SQL Package and Invoking Packaged Subprogram
-- Sequence that packaged function needs: CREATE SEQUENCE emp_sequence START WITH 8000 INCREMENT BY 10; -- Package specification: CREATE or REPLACE PACKAGE employee_management IS FUNCTION hire_emp ( firstname VARCHAR2, lastname VARCHAR2, email VARCHAR2, phone VARCHAR2, hiredate DATE, job VARCHAR2, sal NUMBER, comm NUMBER, mgr NUMBER, deptno NUMBER ) RETURN NUMBER; PROCEDURE fire_emp( emp_id IN NUMBER ); PROCEDURE sal_raise ( emp_id IN NUMBER, sal_incr IN NUMBER ); END employee_management; / -- Package body: CREATE or REPLACE PACKAGE BODY employee_management IS FUNCTION hire_emp ( firstname VARCHAR2, lastname VARCHAR2, email VARCHAR2, phone VARCHAR2, hiredate DATE, job VARCHAR2, sal NUMBER, comm NUMBER, mgr NUMBER, deptno NUMBER ) RETURN NUMBER IS new_empno NUMBER(10); BEGIN new_empno := emp_sequence.NEXTVAL; INSERT INTO EMPLOYEES ( employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ) VALUES ( new_empno, firstname, lastname, email, phone, hiredate, job, sal, comm, mgr, deptno ); RETURN (new_empno); END hire_emp; PROCEDURE fire_emp ( emp_id IN NUMBER ) IS BEGIN DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; IF SQL%NOTFOUND THEN raise_application_error( -20011, 'Invalid Employee Number: ' || TO_CHAR(Emp_id) ); END IF; END fire_emp; PROCEDURE sal_raise ( emp_id IN NUMBER, sal_incr IN NUMBER ) IS BEGIN UPDATE EMPLOYEES SET SALARY = SALARY + sal_incr WHERE EMPLOYEE_ID = emp_id; IF SQL%NOTFOUND THEN raise_application_error( -20011, 'Invalid Employee Number: ' || TO_CHAR(Emp_id) ); END IF; END sal_raise; END employee_management; /
Invoke packaged procedures:
DECLARE
empno NUMBER(6);
sal NUMBER(6);
temp NUMBER(6);
BEGIN
empno := employee_management.hire_emp(
'John',
'Doe',
'john.doe@company.com',
'555-0100',
'20-SEP-07',
'ST_CLERK',
2500,
0,
100,
20);
DBMS_OUTPUT.PUT_LINE('New employee ID is ' || TO_CHAR(empno));
END;
/
The size limit for PL/SQL stored database objects such as subprograms, triggers, and packages is the size of the Descriptive Intermediate Attributed Notation for Ada (DIANA) code in the shared pool in bytes. The Linux and UNIX limit on the size of the flattened DIANA/code size is 64K but the limit might be 32K on desktop platforms.
The most closely related number that a user can access is the PARSED_SIZE
in the static data dictionary view *_OBJECT_SIZE
. That gives the size of the DIANA in bytes as stored in the SYS.IDL_xxx$
tables. This is not the size in the shared pool. The size of the DIANA part of PL/SQL code (used during compilation) is significantly larger in the shared pool than it is in the system table.
Each part of a package is created with a different statement. Create the package specification using the CREATE
PACKAGE
statement. The CREATE
PACKAGE
statement declares public package objects.
To create a package body, use the CREATE
PACKAGE
BODY
statement. The CREATE
PACKAGE
BODY
statement defines the procedural code of the public subprograms declared in the package specification.
You can also define private, or local, package subprograms, and variables in a package body. These objects can only be accessed by other subprograms in the body of the same package. They are not visible to external users, regardless of the privileges they hold.
It is often more convenient to add the OR
REPLACE
clause in the CREATE
PACKAGE
or CREATE
PACKAGE
BODY
statements when you are first developing your application. The effect of this option is to drop the package or the package body without warning. The CREATE
statements are:
CREATE OR REPLACE PACKAGE Package_name AS ...
and
CREATE OR REPLACE PACKAGE BODY Package_name AS ...
The body of a package can contain:
Subprograms declared in the package specification.
Definitions of cursors declared in the package specification.
Local subprograms, not declared in the package specification.
Local variables.
Subprograms, cursors, and variables that are declared in the package specification are global. They can be invoked, or used, by external users that have EXECUTE
permission for the package or that have EXECUTE
ANY
PROCEDURE
privileges.
When you create the package body, ensure that each subprogram that you define in the body has the same parameters, by name, data type, and mode, as the declaration in the package specification. For functions in the package body, the parameters and the return type must agree in name and type.
The privileges required to create or drop a package specification or package body are the same as those required to create or drop a standalone subprogram. See Creating Subprograms and Dropping Subprograms and Packages.
The names of a package and all public objects in the package must be unique within a given schema. The package specification and its body must have the same name. All package constructs must have unique names within the scope of the package, unless overloading of subprogram names is desired.
Each session that references a package object has its own instance of the corresponding package, including persistent state for any public and private variables, cursors, and constants. If any of the session's instantiated packages (specification or body) are invalidated, then all package instances in the session are invalidated and recompiled. Therefore, the session state is lost for all package instances in the session.
When a package in a given session is invalidated, the session receives ORA-04068 the first time it attempts to use any object of the invalid package instance. The second time a session makes such a package call, the package is reinstantiated for the session without error.
Note: For optimal performance, Oracle Database returns this error message only once—each time the package state is discarded.If you handle this error in your application, ensure that your error handling strategy can accurately handle this error. For example, when a subprogram in one package invokes a subprogram in another package, your application must be aware that the session state is lost for both packages. |
In most production environments, DDL operations that can cause invalidations are usually performed during inactive working hours; therefore, this situation might not be a problem for end-user applications. However, if package invalidations are common in your system during working hours, then you might want to code your applications to handle this error when package calls are made.
There are many packages provided with Oracle Database, either to extend the functionality of the database or to give PL/SQL access to SQL features. You can invoke these packages from your application.
Oracle Database uses two engines to run PL/SQL blocks and subprograms. The PL/SQL engine runs procedural statements, while the SQL engine runs SQL statements. During execution, every SQL statement causes a context switch between the two engines, resulting in performance overhead.
Performance can be improved substantially by minimizing the number of context switches required to run a particular block or subprogram. When a SQL statement runs inside a loop that uses collection elements as bind variables, the large number of context switches required by the block can cause poor performance. Collections include:
Varrays
Nested tables
Index-by tables
Host arrays
Binding is the assignment of values to PL/SQL variables in SQL statements. Bulk binding is binding an entire collection at once. Bulk binds pass the entire collection back and forth between the two engines in a single operation.
Typically, using bulk binds improves performance for SQL statements that affect four or more database rows. The more rows affected by a SQL statement, the greater the performance gain from bulk binds.
Note: This section provides an overview of bulk binds to help you decide whether to use them in your PL/SQL applications. For detailed information about using bulk binds, including ways to handle exceptions that occur in the middle of a bulk bind operation, see Oracle Database PL/SQL Language Reference.Parallel DML is disabled with bulk binds. |
Consider using bulk binds to improve the performance of:
A bulk bind, which uses the FORALL
keyword, can improve the performance of INSERT
, UPDATE
, or DELETE
statements that reference collection elements.
The PL/SQL block in Example: DML Statements that Reference Collections increases the salary for employees whose manager's ID number is 7902, 7698, or 7839, with and without bulk binds. Without bulk bind, PL/SQL sends a SQL statement to the SQL engine for each updated employee, leading to context switches that slow performance.
DML Statements that Reference Collections
DECLARE 2 TYPE numlist IS VARRAY (100) OF NUMBER; 3 id NUMLIST := NUMLIST(7902, 7698, 7839); BEGIN -- Efficient method, using bulk bind: FORALL i IN id.FIRST..id.LAST UPDATE EMPLOYEES SET SALARY = 1.1 * SALARY WHERE MANAGER_ID = id(i); -- Slower method: FOR i IN id.FIRST..id.LAST LOOP UPDATE EMPLOYEES SET SALARY = 1.1 * SALARY WHERE MANAGER_ID = id(i); END LOOP; END; /
The BULK
COLLECT
INTO
clause can improve the performance of queries that reference collections. You can use BULK
COLLECT
INTO
with tables of scalar values, or tables of %TYPE
values.
The PL/SQL block in Example: SELECT Statements that Reference Collections queries multiple values into PL/SQL tables, with and without bulk binds. Without bulk bind, PL/SQL sends a SQL statement to the SQL engine for each selected employee, leading to context switches that slow performance.
SELECT Statements that Reference Collections
DECLARE TYPE var_tab IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER; empno VAR_TAB; ename VAR_TAB; counter NUMBER; CURSOR c IS SELECT EMPLOYEE_ID, LAST_NAME FROM EMPLOYEES WHERE MANAGER_ID = 7698; BEGIN -- Efficient method, using bulk bind: SELECT EMPLOYEE_ID, LAST_NAME BULK COLLECT INTO empno, ename FROM EMPLOYEES WHERE MANAGER_ID = 7698; -- Slower method: counter := 1; FOR rec IN c LOOP empno(counter) := rec.EMPLOYEE_ID; ename(counter) := rec.LAST_NAME; counter := counter + 1; END LOOP; END; /
You can use the FORALL
keyword with the BULK
COLLECT
INTO
keywords to improve the performance of FOR
loops that reference collections and return DML.
The PL/SQL block in Example: FOR Loops that Reference Collections and Return DML updates the EMPLOYEES
table by computing bonuses for a collection of employees. Then it returns the bonuses in a column called bonus_list_inst
. The actions are performed with and without bulk binds. Without bulk bind, PL/SQL sends a SQL statement to the SQL engine for each updated employee, leading to context switches that slow performance.
FOR Loops that Reference Collections and Return DML
DECLARE TYPE emp_list IS VARRAY(100) OF EMPLOYEES.EMPLOYEE_ID%TYPE; empids emp_list := emp_list(182, 187, 193, 200, 204, 206); TYPE bonus_list IS TABLE OF EMPLOYEES.SALARY%TYPE; bonus_list_inst bonus_list; BEGIN -- Efficient method, using bulk bind: FORALL i IN empids.FIRST..empids.LAST UPDATE EMPLOYEES SET SALARY = 0.1 * SALARY WHERE EMPLOYEE_ID = empids(i) RETURNING SALARY BULK COLLECT INTO bonus_list_inst; -- Slower method: FOR i IN empids.FIRST..empids.LAST LOOP UPDATE EMPLOYEES SET SALARY = 0.1 * SALARY WHERE EMPLOYEE_ID = empids(i) RETURNING SALARY INTO bonus_list_inst(i); END LOOP; END; /
A trigger is a special kind of PL/SQL anonymous block. You can define triggers to fire before or after SQL statements, either on a statement level or for each row that is affected. You can also define INSTEAD
OF
triggers or system triggers (triggers on DATABASE
and SCHEMA
).
See Also: Oracle Database PL/SQL Language Reference for more information about triggers |
You can speed up PL/SQL subprograms by compiling them into native code residing in shared libraries.
You can use native compilation with both the supplied packages and the subprograms you write yourself. Subprograms compiled this way work in all server environments, such as the shared server configuration (formerly known as multithreaded server) and Oracle Real Application Clusters (Oracle RAC).
This technique is most effective for computation-intensive subprograms that do not spend much time executing SQL, because it can do little to speed up SQL statements invoked from these subprograms.
With Java, you can use the ncomp
tool to compile your own packages and classes.
See Also:
|
A cursor is a static object; a cursor variable is a pointer to a cursor. Because cursor variables are pointers, they can be passed and returned as parameters to subprograms. A cursor variable can also refer to different cursors in its lifetime.
Additional advantages of cursor variables include:
Encapsulation
Queries are centralized in the stored subprogram that opens the cursor variable.
Easy maintenance
If you must change the cursor, then you only make the change in the stored subprogram, not in each application.
Convenient security
The user of the application is the user name used when the application connects to the server. The user must have EXECUTE
permission on the stored subprogram that opens the cursor. But, the user need not have READ
permission on the tables used in the query. This capability can be used to limit access to the columns in the table and access to other stored subprograms.
See Also: Oracle Database PL/SQL Language Reference for more information about cursor variables |
Topics:
Memory is usually allocated for a cursor variable in the client application using the appropriate ALLOCATE
statement. In Pro*C, use the EXEC
SQL
ALLOCATE
cursor_name
statement. In OCI, use the Cursor Data Area.
You can also use cursor variables in applications that run entirely in a single server session. You can declare cursor variables in PL/SQL subprograms, open them, and use them as parameters for other PL/SQL subprograms.
This section has examples of cursor variable usage in PL/SQL.
See Also: For additional cursor variable examples that use programmatic interfaces:
|
Example: Fetching Data with Cursor Variable creates a package that defines a PL/SQL cursor variable type and two procedures, and then invokes the procedures from a PL/SQL block. The first procedure opens a cursor variable using a bind variable in the WHERE
clause. The second procedure uses a cursor variable to fetch rows from the EMPLOYEES
table.
Fetching Data with Cursor Variable
CREATE OR REPLACE PACKAGE emp_data AS TYPE emp_val_cv_type IS REF CURSOR RETURN EMPLOYEES%ROWTYPE; PROCEDURE open_emp_cv ( emp_cv IN OUT emp_val_cv_type, dept_number IN EMPLOYEES.DEPARTMENT_ID%TYPE ); PROCEDURE fetch_emp_data ( emp_cv IN emp_val_cv_type, emp_row OUT EMPLOYEES%ROWTYPE ); END emp_data; / CREATE OR REPLACE PACKAGE BODY emp_data AS PROCEDURE open_emp_cv ( emp_cv IN OUT emp_val_cv_type, dept_number IN EMPLOYEES.DEPARTMENT_ID%TYPE ) IS BEGIN OPEN emp_cv FOR SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = dept_number; END open_emp_cv; PROCEDURE fetch_emp_data ( emp_cv IN emp_val_cv_type, emp_row OUT EMPLOYEES%ROWTYPE ) IS BEGIN FETCH emp_cv INTO emp_row; END fetch_emp_data; END emp_data; /
Invoke packaged procedures:
DECLARE emp_curs emp_data.emp_val_cv_type; dept_number EMPLOYEES.DEPARTMENT_ID%TYPE; emp_row EMPLOYEES%ROWTYPE; BEGIN dept_number := 20; -- Open cursor, using variable: emp_data.open_emp_cv(emp_curs, dept_number); -- Fetch and display data: LOOP emp_data.fetch_emp_data(emp_curs, emp_row); EXIT WHEN emp_curs%NOTFOUND; DBMS_OUTPUT.PUT(emp_row.LAST_NAME || ' '); DBMS_OUTPUT.PUT_LINE(emp_row.SALARY); END LOOP; END; /
In Example: Cursor Variable with Discriminator, the procedure opens a cursor variable for either the EMPLOYEES
table or the DEPARTMENTS
table, depending on the value of the parameter discrim
. The anonymous block invokes the procedure to open the cursor variable for the EMPLOYEES
table, but fetches from the DEPARTMENTS
table, which raises the predefined exception ROWTYPE_MISMATCH
.
Cursor Variable with Discriminator
CREATE OR REPLACE PACKAGE emp_dept_data AS TYPE cv_type IS REF CURSOR; PROCEDURE open_cv ( cv IN OUT cv_type, discrim IN POSITIVE ); END emp_dept_data; / CREATE OR REPLACE PACKAGE BODY emp_dept_data AS PROCEDURE open_cv ( cv IN OUT cv_type, discrim IN POSITIVE) IS BEGIN IF discrim = 1 THEN OPEN cv FOR SELECT * FROM EMPLOYEES; ELSIF discrim = 2 THEN OPEN cv FOR SELECT * FROM DEPARTMENTS; END IF; END open_cv; END emp_dept_data; /
Invoke procedure open_cv
from anonymous block:
DECLARE emp_rec EMPLOYEES%ROWTYPE; dept_rec DEPARTMENTS%ROWTYPE; cv Emp_dept_data.CV_TYPE; BEGIN emp_dept_data.open_cv(cv, 1); -- Open cv for EMPLOYEES fetch. FETCH cv INTO dept_rec; -- Fetch from DEPARTMENTS. DBMS_OUTPUT.PUT(dept_rec.DEPARTMENT_ID); DBMS_OUTPUT.PUT_LINE(' ' || dept_rec.LOCATION_ID); EXCEPTION WHEN ROWTYPE_MISMATCH THEN BEGIN DBMS_OUTPUT.PUT_LINE ('Row type mismatch, fetching EMPLOYEES data ...'); FETCH cv INTO emp_rec; DBMS_OUTPUT.PUT(emp_rec.DEPARTMENT_ID); DBMS_OUTPUT.PUT_LINE(' ' || emp_rec.LAST_NAME); END; END; /
Result:
Row type mismatch, fetching EMPLOYEES data ...
90 King
To list compile-time errors, query the static data dictionary view *_ERRORS
. From these views, you can retrieve original source code. The error text associated with the compilation of a subprogram is updated when the subprogram is replaced, and it is deleted when the subprogram is dropped.
SQL*Plus issues a warning message for compile-time errors, but for more information about them, you must use the command SHOW
ERRORS
.
Note: Before issuing theSHOW ERRORS statement, use the SET LINESIZE statement to get long lines on output. The value 132 is usually a good choice. For example:
SET LINESIZE 132 |
Example: Compile-Time Errors has two compile-time errors: WHER
should be WHERE
, and END
should be followed by a semicolon. SHOW
ERRORS
shows the line, column, and description of each error.
Compile-Time Errors
CREATE OR REPLACE PROCEDURE fire_emp ( emp_id NUMBER ) AS BEGIN DELETE FROM EMPLOYEES WHER EMPLOYEE_ID = Emp_id; END /
Result:
Warning: Procedure created with compilation errors.
Command:
SHOW ERRORS;
Result:
Errors for PROCEDURE FIRE_EMP: LINE/COL ERROR -------- ----------------------------------------------------------------- 5/3 PL/SQL: SQL Statement ignored 6/8 PL/SQL: ORA-00933: SQL command not properly ended 7/3 PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ; <an identifier> <a double-quoted delimited-identifier> current delete exists prior <a single-quoted SQL string> The symbol ";" was substituted for "end-of-file" to continue.
See Also:
|
Oracle Database allows user-defined errors in PL/SQL code to be handled so that user-specified error numbers and messages are returned to the client application, which can handle the error.
User-specified error messages are returned using the RAISE_APPLICATION_ERROR
procedure. For example:
RAISE_APPLICATION_ERROR(error_number, 'text', keep_error_stack)
This procedure stops subprogram execution, rolls back any effects of the subprogram, and returns a user-specified error number and message (unless the error is trapped by an exception handler). error_number
must be in the range of -20000 to -20999.
Use error number -20000 as a generic number for messages where it is important to relay information to the user, but having a unique error number is not required. Text
must be a character expression, 2 KB or less (longer messages are ignored). To add the error to errors on the stack, set Keep_error_stack
to TRUE
; to replace the existing errors, set it to FALSE
(the default).
The RAISE_APPLICATION_ERROR
procedure is often used in exception handlers or in the logic of PL/SQL code. For example, this exception handler selects the string for the associated user-defined error message and invokes the RAISE_APPLICATION_ERROR
procedure:
... WHEN NO_DATA_FOUND THEN SELECT Error_string INTO Message FROM Error_table, V$NLS_PARAMETERS V WHERE Error_number = -20101 AND Lang = v.value AND v.parameter = "NLS_LANGUAGE"; Raise_application_error(-20101, Message); ...
Topics:
User-defined exceptions are explicitly defined and raised within the PL/SQL block, to process errors specific to the application. When an exception is raised, the usual execution of the PL/SQL block stops, and an exception handler is invoked. Specific exception handlers can be written to handle any internal or user-defined exception.
Application code can check for a condition that requires special attention using an IF
statement. If there is an error condition, then two options are available:
Enter a RAISE
statement that names the appropriate exception. A RAISE
statement stops the execution of the subprogram, and control passes to an exception handler (if any).
Invoke the RAISE_APPLICATION_ERROR
procedure to return a user-specified error number and message.
You can also define an exception handler to handle user-specified error messages. The following example shows:
An exception and associated exception handler in a subprogram
A conditional statement that checks for an error (such as transferring funds not available) and enters a user-specified error number and message within a trigger
How user-specified error numbers are returned to the invoking environment (in this case, a subprogram), and how that application can define an exception that corresponds to the user-specified error number
Declare a user-defined exception in a subprogram or package body (private exceptions), or in the specification of a package (public exceptions), and efine an exception handler in the body of a subprogram (standalone or package), as follows:
PROCEDURE fire_emp (empid NUMBER) IS invalid_empid EXCEPTION PRAGMA EXCEPTION_INIT (invalid_empid, -20101); BEGIN DELETE FROM emp WHERE empno = empid; EXCEPTION WHEN invlid_empid THEN INSERT INTO emp_audit VALUES (empid, 'Fired before probation ended'); END
This program returns the error number 20101 to the invoking environment (the preceding program), and a user-defined before-delete trigger named emp_probation
checks for that error number, as follows:
TRIGGER emp_probation BEFORE DELETE ON emp FOR EACH ROW BEGIN IF (sysdate - :old.hiredate) < 30 THEN raise_application_error(20101, 'Employee' || old.ename || ' on probation') END IF; END;
In database PL/SQL units, an unhandled user-error condition or internal error condition that is not trapped by an appropriate exception handler causes the implicit rollback of the program unit. If the program unit includes a COMMIT
statement before the point at which the unhandled exception is observed, then the implicit rollback of the program unit can only be completed back to the previous COMMIT
.
Additionally, unhandled exceptions in database-stored PL/SQL units propagate back to client-side applications that invoke the containing program unit. In such an application, only the application program unit invocation is rolled back (not the entire application program unit), because it is submitted to the database as a SQL statement.
If unhandled exceptions in database PL/SQL units are propagated back to database applications, modify the database PL/SQL code to handle the exceptions. Your application can also trap for unhandled exceptions when invoking database program units and handle such errors appropriately.
You can use a trigger or a stored subprogram to create a distributed query. This distributed query is decomposed by the local Oracle Database instance into a corresponding number of remote queries, which are sent to the remote nodes for execution. The remote nodes run the queries and send the results back to the local node. The local node then performs any necessary post-processing and returns the results to the user or application.
If a portion of a distributed statement fails, possibly from a constraint violation, then Oracle Database returns ORA-02055. Subsequent statements, or subprogram invocations, return ORA-02067 until a rollback or a rollback to savepoint is entered.
Design your application to check for any returned error messages that indicates that a portion of the distributed update has failed. If you detect a failure, rollback the entire transaction (or rollback to a savepoint) before allowing the application to proceed.
When a subprogram is run locally or at a remote location, these types of exceptions can occur:
PL/SQL user-defined exceptions, which must be declared using the keyword EXCEPTION
PL/SQL predefined exceptions, such as NO_DATA_FOUND
SQL errors, such as ORA-00900
Application exceptions, which are generated using the RAISE_APPLICATION_ERROR
procedure.
When using local subprograms, all of these messages can be trapped by writing an exception handler, such as:
EXCEPTION WHEN ZERO_DIVIDE THEN /* Handle the exception */
The WHEN
clause requires an exception name. If the exception that is raised does not have a name, such as those generated with RAISE_APPLICATION_ERROR
, then one can be assigned using PRAGMA_EXCEPTION_INIT
. For example:
DECLARE ... Null_salary EXCEPTION; PRAGMA EXCEPTION_INIT(Null_salary, -20101); BEGIN ... RAISE_APPLICATION_ERROR(-20101, 'salary is missing'); ... EXCEPTION WHEN Null_salary THEN ...
When invoking a remote subprogram, exceptions are also handled by creating a local exception handler. The remote subprogram must return an error number to the local invoking subprogram, which then handles the exception, as shown in the previous example. Because PL/SQL user-defined exceptions always return ORA-06510 to the local subprogram, these exceptions cannot be handled. All other remote exceptions can be handled in the same manner as local exceptions.
Compiling a stored subprogram involves fixing any syntax errors in the code. You might need to do additional debugging to ensure that the subprogram works correctly, performs well, and recovers from errors. Such debugging might involve:
Adding extra output statements to verify execution progress and check data values at certain points within the subprogram.
Running a separate debugger to analyze execution in greater detail.
Topics:
PL/Scope is a compiler-driven tool that collects and organizes data about user-defined identifiers from PL/SQL source code. Because PL/Scope is a compiler-driven tool, you use it through interactive development environments (such as SQL Developer and JDeveloper), rather than directly.
PL/Scope enables the development of powerful and effective PL/Scope source code browsers that increase PL/SQL developer productivity by minimizing time spent browsing and understanding source code.
The PL/SQL hierarchical profiler reports the dynamic execution profile of your PL/SQL program, organized by subprogram calls. It accounts for SQL and PL/SQL execution times separately. Each subprogram-level summary in the dynamic execution profile includes information such as number of calls to the subprogram, time spent in the subprogram itself, time spent in the subprogram's subtree (that is, in its descendent subprograms), and detailed parent-children information.
You can browse the generated HTML reports in any browser. The browser's navigational capabilities, combined with well chosen links, provide a powerful way to analyze performance of large applications, improve application performance, and lower development costs.
You can also debug stored subprograms and triggers using the Oracle package DBMS_OUTPUT
. Put PUT
and PUT_LINE
statements in your code to output the value of variables and expressions to your terminal.
This section describes the privilege model that applies to debugging PL/SQL and Java code running within the database. This model applies whether you are using SQL Developer, Oracle JDeveloper, or any of the various third-party PL/SQL or Java development environments, and it affects both the DBMS_DEBUG
and DBMS_DEBUG_JDWP
APIs.
For a session to connect to a debugger, the effective user at the time of the connect operation must have the DEBUG
CONNECT
SESSION
system privilege. This effective user might be the owner of a DR subprogram involved in making the connect call.
When a debugger becomes connected to a session, the session login user and the enabled session-level roles are fixed as the privilege environment for that debugging connection. Any DEBUG
or EXECUTE
privileges needed for debugging must be granted to that combination of user and roles.
To be able to display and change Java public variables or variables declared in a PL/SQL package specification, the debugging connection must be granted either EXECUTE
or DEBUG
privilege on the relevant code.
To be able to either display and change private variables or breakpoint and execute code lines step by step, the debugging connection must be granted DEBUG
privilege on the relevant code
Caution: TheDEBUG privilege allows a debugging session to do anything that the subprogram being debugged could have done if that action had been included in its code. |
In addition to these privilege requirements, the ability to stop on individual code lines and debugger access to variables are allowed only in code compiled with debug information generated. Use the PL/SQL compilation parameter PLSQL_DEBUG
and the DEBUG
keyword on statements such as ALTER
PACKAGE
to control whether the PL/SQL compiler includes debug information in its results. If not, variables are not accessible, and neither stepping nor breakpoints stop on code lines. The PL/SQL compiler never generates debug information for code hidden with the PL/SQL wrap
utility.
See Also: Oracle Database PL/SQL Language Reference for information about thewrap utility |
The DEBUG
ANY
PROCEDURE
system privilege is equivalent to the DEBUG
privilege granted on all objects in the database. Objects owned by SYS
are included if the value of the O7_DICTIONARY_ACCESSIBILITY
parameter is TRUE
.
A debug role mechanism is available to carry privileges needed for debugging that are not normally enabled in the session. See the documentation on the DBMS_DEBUG
and DBMS_DEBUG_JDWP
packages for details on how to specify a debug role and any necessary related password.
The JAVADEBUGPRIV
role carries the DEBUG
CONNECT
SESSION
and DEBUG
ANY
PROCEDURE
privileges. Grant it only with the care those privileges warrant.
Caution: GrantingDEBUG ANY PROCEDURE privilege, or granting DEBUG privilege on any object owned by SYS , means granting complete rights to the database. |
If you are writing code for part of a debugger, you might need to use packages such as DBMS_DEBUG_JDWP
or DBMS_DEBUG
.
The DBMS_DEBUG_JDWP
package provides a framework for multilanguage debugging that is expected to supersede the DBMS_DEBUG
package over time. It is especially useful for programs that combine PL/SQL and Java.
Stored PL/SQL subprograms can be invoked from many different environments. For example:
Interactively, using an Oracle Database tool
From the body of another subprogram
From within an application (such as a SQL*Forms or a precompiler)
From the body of a trigger
Stored PL/SQL functions (but not procedures) can also be invoked from within SQL statements. For details, see Invoking Stored PL/SQL Functions from SQL Statements.
Topics:
See Also:
|
You do not need privileges to invoke:
Standalone subprograms that you own
Subprograms in packages that you own
Public standalone subprograms
Subprograms in public packages
To invoke a standalone or packaged subprogram owned by another user:
You must have the EXECUTE
privilege for the standalone subprogram or for the package containing the subprogram, or you must have the EXECUTE
ANY
PROCEDURE
system privilege.
If you are executing a remote subprogram, then you must be granted the EXECUTE
privilege or EXECUTE
ANY
PROCEDURE
system privilege directly, not through a role.
You must include the name of the owner in the invocation. For example:
EXECUTE jdoe.Fire_emp (1043); EXECUTE jdoe.Hire_fire.Fire_emp (1043);
If the subprogram is a definer's-rights (DR) subprogram, then it runs with the privileges of the owner. The owner must have all the necessary object privileges for any referenced objects.
If the subprogram is an invoker's-rights (IR) subprogram, then it runs with your privileges. You must have all the necessary object privileges for any referenced objects; that is, all objects accessed by the subprogram through external references that are resolved in your schema. You can hold these privileges either directly or through a role. Roles are enabled unless an IR subprogram is invoked directly or indirectly by a DR subprogram.
You can invoke a subprogram interactively from an Oracle Database tool, such as SQL*Plus. Example: Invoking a Subprogram Interactively with SQL*Plus uses SQL*Plus to create a procedure and then invokes it in two different ways.
Invoking a Subprogram Interactively with SQL*Plus
CREATE OR REPLACE PROCEDURE salary_raise ( employee EMPLOYEES.EMPLOYEE_ID%TYPE, increase EMPLOYEES.SALARY%TYPE ) IS BEGIN UPDATE EMPLOYEES SET SALARY = SALARY + increase WHERE EMPLOYEE_ID = employee; END; /
Invoke procedure from within PL/SQL block:
BEGIN
salary_raise(205, 200);
END;
/
Result:
PL/SQL procedure successfully completed.
Invoke procedure with EXECUTE
statement:
EXECUTE salary_raise(205, 200);
Result:
PL/SQL procedure successfully completed.
Some interactive tools allow you to create session variables, which you can use for the duration of the session. Using SQL*Plus, Example: Creating and Using a Session Variable with SQL*Plus creates, uses, and prints a session variable.
Creating and Using a Session Variable with SQL*Plus
-- Create function for later use: CREATE OR REPLACE FUNCTION get_job_id ( emp_id EMPLOYEES.EMPLOYEE_ID%TYPE ) RETURN EMPLOYEES.JOB_ID%TYPE IS job_id EMPLOYEES.JOB_ID%TYPE; BEGIN SELECT JOB_ID INTO job_id FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; RETURN job_id; END; / -- Create session variable: VARIABLE job VARCHAR2(10); -- Execute function and store returned value in session variable: EXECUTE :job := get_job_id(204); PL/SQL procedure successfully completed.
SQL*Plus command:
PRINT job;
Result:
JOB -------------------------------- PR_REP
A subprogram or a trigger can invoke another stored subprogram. In Example: Invoking a Subprogram from Within Another Subprogram, the procedure print_mgr_name
invokes the procedure print_emp_name
.
Recursive subprogram invocations are allowed (that is, a subprogram can invoke itself).
Invoking a Subprogram from Within Another Subprogram
-- Create procedure that takes employee's ID and prints employee's name: CREATE OR REPLACE PROCEDURE print_emp_name ( emp_id EMPLOYEES.EMPLOYEE_ID%TYPE ) IS fname EMPLOYEES.FIRST_NAME%TYPE; lname EMPLOYEES.LAST_NAME%TYPE; BEGIN SELECT FIRST_NAME, LAST_NAME INTO fname, lname FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; DBMS_OUTPUT.PUT_LINE ( 'Employee #' || emp_id || ': ' || fname || ' ' || lname ); END; / -- Create procedure that takes employee's ID and prints manager's name: CREATE OR REPLACE PROCEDURE print_mgr_name ( emp_id EMPLOYEES.EMPLOYEE_ID%TYPE ) IS mgr_id EMPLOYEES.MANAGER_ID%TYPE; BEGIN SELECT MANAGER_ID INTO mgr_id FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; DBMS_OUTPUT.PUT_LINE ( 'Manager of employee #' || emp_id || ' is: ' ); print_emp_name(mgr_id); END; /
Invoke procedures:
BEGIN print_emp_name(200); print_mgr_name(200); END; /
Result:
Employee #200: Jennifer Whalen Manager of employee #200 is: Employee #101: Neena Kochhar
A 3GL database application, such as a precompiler or an OCI application, can invoke a subprogram from within its own code.
Assume that the procedure Fire_emp1
was created as follows:
CREATE OR REPLACE PROCEDURE fire_emp1 (Emp_id NUMBER) AS BEGIN DELETE FROM Emp_tab WHERE Empno = Emp_id; END;
To run a subprogram within the code of a precompiler application, you must use the EXEC
call interface. For example, this statement invokes the Fire_emp
procedure in the code of a precompiler application:
EXEC SQL EXECUTE BEGIN Fire_emp1(:Empnum); END; END-EXEC;
Remote subprograms (standalone and packaged) can be invoked from within a subprogram, OCI application, or precompiler by specifying the remote subprogram name, a database link, and the parameters for the remote subprogram.
For example, this SQL*Plus statement invokes the procedure fire_emp1
, which is located in the database and referenced by the local database link named boston_server
:
EXECUTE fire_emp1@boston_server(1043);
You must specify values for all remote subprogram parameters, even if there are defaults. You cannot access remote package variables and constants.
Caution:
|
Topics:
See Also: Handling Errors in Remote Subprograms for information about exception handling when invoking remote subprograms |
You can create a synonym for a remote subprogram name and database link, and then use the synonym to invoke the subprogram. For example:
CREATE SYNONYM synonym1 for fire_emp1@boston_server; EXECUTE synonym1(1043); /
The synonym enables you to invoke the remote subprogram from an Oracle Database tool application, such as a SQL*Forms application, as well from within a subprogram, OCI application, or precompiler.
Synonyms provide both data independence and location transparency. Synonyms permit applications to function without modification regardless of which user owns the object and regardless of which database holds the object. However, synonyms are not a substitute for privileges on database objects. Appropriate privileges must be granted to a user before the user can use the synonym.
Because subprograms defined within a package are not individual objects (the package is the object), synonyms cannot be created for individual subprograms within a package.
If you do not want to use a synonym, you can create a local subprogram to invoke the remote subprogram. For example:
CREATE OR REPLACE PROCEDURE local_procedure (arg IN NUMBER) AS BEGIN fire_emp1@boston_server(arg); END; / DECLARE arg NUMBER; BEGIN local_procedure(arg); END; /
All invocations to remotely stored subprograms are assumed to perform updates; therefore, this type of referencing always requires two-phase commit of that transaction (even if the remote subprogram is read-only). Furthermore, if a transaction that includes a remote subprogram invocation is rolled back, then the work done by the remote subprogram is also rolled back.
A subprogram invoked remotely can usually execute a COMMIT
, ROLLBACK
, or SAVEPOINT
statement, the same as a local subprogram. However, there are some differences in action:
If the transaction was originated by a database that is not an Oracle database, as might be the case in XA applications, these operations are not allowed in the remote subprogram.
After doing one of these operations, the remote subprogram cannot start any distributed transactions of its own.
If the remote subprogram does not commit or roll back its work, the commit is done implicitly when the database link is closed. In the meantime, further invocations to the remote subprogram are not allowed because it is still considered to be performing a transaction.
A distributed transaction modifies data on two or more databases. A distributed transaction is possible using a subprogram that includes two or more remote updates that access data on different databases. Statements in the construct are sent to the remote databases, and the execution of the construct succeeds or fails as a unit. If part of a distributed update fails and part succeeds, then a rollback (of the entire transaction or to a savepoint) is required to proceed. Consider this when creating subprograms that perform distributed updates.
Caution: Because SQL is a declarative language, rather than an imperative (or procedural) one, you cannot know how many times a function invoked from a SQL statement will execute—even if the function is written in PL/SQL, an imperative language.If your application requires that a function be executed a certain number of times, do not invoke that function from a SQL statement. Use a cursor instead. For example, if your application requires that a function be called once for each selected row, then open a cursor, select rows from the cursor, and call the function for each row. This guarantees that the number of calls to the function is the number of rows fetched from the cursor. |
To be invoked from a SQL statement, a stored PL/SQL function must be declared either at schema level or in a package specification.
These SQL statements can invoke stored PL/SQL functions:
INSERT
UPDATE
DELETE
SELECT
CALL
(CALL
can also invoke a stored PL/SQL procedure.)
To invoke a PL/SQL subprogram from SQL, you must either own or have EXECUTE
privileges on the subprogram. To select from a view defined with a PL/SQL function, you must have SELECT
privileges on the view. No separate EXECUTE
privileges are necessary to select from the view.
For general information about invoking subprograms, including passing parameters, see Oracle Database PL/SQL Language Reference.
Topics:
Invoking PL/SQL subprograms in SQL statements can:
Increase user productivity by extending SQL
Expressiveness of the SQL statement increases where activities are too complex, too awkward, or unavailable with SQL.
Increase query efficiency
Functions used in the WHERE
clause of a query can filter data using criteria that must otherwise be evaluated by the application.
Manipulate character strings to represent special data types (for example, latitude, longitude, or temperature).
Provide parallel query execution
If the query is parallelized, then SQL statements in your PL/SQL subprogram might also be run in parallel (using the parallel query option).
A PL/SQL function can appear in a SQL statement wherever a built-in SQL function or an expression can appear in a SQL statement. For example:
Select list of the SELECT
statement
Condition of the WHERE
or HAVING
clause
CONNECT
BY
, START
WITH
, ORDER
BY
, or GROUP
BY
clause
VALUES
clause of the INSERT
statement
SET
clause of the UPDATE
statement
A PL/SQL table function (which returns a collection of rows) can appear in a SELECT
statement instead of:
Column name in the SELECT
list
Table name in the FROM
clause
A PL/SQL function cannot appear in these contexts, which require unchanging definitions:
CHECK
constraint clause of a CREATE
or ALTER
TABLE
statement
Default value specification for a column
To be invoked from a SQL expression, a PL/SQL function must satisfy these requirements:
It must be a row function, not a column (group) function; that is, its argument cannot be an entire column.
Its formal parameters must be IN
parameters, not OUT
or IN
OUT
parameters.
Its formal parameters and its return value (if any) must have Oracle built-in data types (such as CHAR
, DATE
, or NUMBER
), not PL/SQL data types (such as BOOLEAN
, RECORD
, or TABLE
).
There is an exception to this rule: A formal parameter can have a PL/SQL data type if the corresponding actual parameter is implicitly converted to the data type of the formal parameter (as in Example: PL/SQL Function that Can Appear in a SQL Expression).
The function in Example: PL/SQL Function that Can Appear in a SQL Expression satisfies the preceding requirements.
PL/SQL Function that Can Appear in a SQL Expression
DROP TABLE payroll; -- in case it exists CREATE TABLE payroll ( srate NUMBER, orate NUMBER, acctno NUMBER ); CREATE OR REPLACE FUNCTION gross_pay ( emp_id IN NUMBER, st_hrs IN NUMBER := 40, ot_hrs IN NUMBER := 0 ) RETURN NUMBER IS st_rate NUMBER; ot_rate NUMBER; BEGIN SELECT srate, orate INTO st_rate, ot_rate FROM payroll WHERE acctno = emp_id; RETURN st_hrs * st_rate + ot_hrs * ot_rate; END gross_pay; /
In Example: PL/SQL Function with Formal Parameter of PL/SQL Data Type, Invoked from a SQL Expression, the SQL statement CALL
invokes the PL/SQL function f1
, whose formal parameter and return value have PL/SQL data type PLS_INTEGER
. The CALL
statement succeeds because the actual parameter, 2, is implicitly converted to the data type PLS_INTEGER
. If the actual parameter had a value outside the range of PLS_INTEGER
, the CALL
statement would fail.
PL/SQL Function with Formal Parameter of PL/SQL Data Type, Invoked from a SQL Expression
CREATE OR REPLACE FUNCTION f1 ( b IN PLS_INTEGER ) RETURN PLS_INTEGER IS BEGIN RETURN CASE WHEN b > 0 THEN 1 WHEN b <= 0 THEN -1 ELSE NULL END; END f1; / VARIABLE x NUMBER; CALL f1(b=>2) INTO :x; PRINT x;
Result:
X ---------- 1
The purity of a stored subprogram refers to the side effects of that subprogram on database tables or package variables. Side effects can prevent the parallelization of a query, yield order-dependent (and therefore, indeterminate) results, or require that package state be maintained across user sessions. Various side effects are not allowed when a function is invoked from a SQL query or DML statement.
Topics related to side effects:
When a new SQL statement is run, checks are made to see if it is logically embedded within the execution of a running SQL statement. This occurs if the statement is run from a trigger or from a subprogram that was in turn invoked from the running SQL statement. In these cases, further checks determine if the new SQL statement is safe in the specific context.
These restrictions are enforced on subprograms:
A subprogram invoked from a query or DML statement might not end the current transaction, create or rollback to a savepoint, or ALTER
the system or session.
A subprogram invoked from a query (SELECT
) statement or from a parallelized DML statement might not execute a DML statement or otherwise modify the database.
A subprogram invoked from a DML statement might not read or modify the particular table being modified by that DML statement.
These restrictions apply regardless of what mechanism is used to run the SQL statement inside the subprogram or trigger. For example:
They apply to a SQL statement invoked from PL/SQL, whether embedded directly in a subprogram or trigger body, run using the native dynamic mechanism (EXECUTE
IMMEDIATE
), or run using the DBMS_SQL
package.
They apply to statements embedded in Java with SQLJ syntax or run using JDBC.
They apply to statements run with OCI using the callback context from within an "external" C function.
You can avoid these restrictions if the execution of the new SQL statement is not logically embedded in the context of the running statement. PL/SQL autonomous transactions provide one escape. Another escape is available using Oracle Call Interface (OCI) from an external C function, if you create a new connection rather than using the handle available from the OCIExtProcContext
argument.
You can use the keywords DETERMINISTIC
and PARALLEL_ENABLE
in the syntax for declaring a function. These are optimization hints that inform the query optimizer and other software components about:
Functions that need not be invoked redundantly
Functions permitted within a parallelized query or parallelized DML statement
Only functions that are DETERMINISTIC
are allowed in function-based indexes and in certain snapshots and materialized views.
A deterministic function depends solely on the values passed into it as arguments and does not reference or modify the contents of package variables or the database or have other side-effects. Such a function produces the same result value for any combination of argument values passed into it.
You place the DETERMINISTIC
keyword after the return value type in a declaration of the function. For example:
CREATE OR REPLACE FUNCTION f1 (
p1 NUMBER
) RETURN NUMBER DETERMINISTIC
IS
BEGIN
RETURN p1 * 2;
END;
/
You might place this keyword in these places:
On a function defined in a CREATE
FUNCTION
statement
In a function declaration in a CREATE
PACKAGE
statement
On a method declaration in a CREATE
TYPE
statement
Do not repeat the keyword on the function or method body in a CREATE
PACKAGE
BODY
or CREATE
TYPE
BODY
statement.
Certain performance optimizations occur on invocations of functions that are marked DETERMINISTIC
without any other action being required. These features require that any function used with them be declared DETERMINISTIC
:
Any user-defined function used in a function-based index.
Any function used in a materialized view, if that view is to qualify for Fast Refresh or is marked ENABLE
QUERY
REWRITE
.
The preceding functions features attempt to use previously calculated results rather than invoking the function when it is possible to do so.
It is good programming practice to make functions that fall into these categories DETERMINISTIC
:
Functions used in a WHERE
, ORDER
BY
, or GROUP
BY
clause
Functions that MAP
or ORDER
methods of a SQL type
Functions that help determine whether or where a row appears in a result set
Keep these points in mind when you create DETERMINISTIC
functions:
The database cannot recognize if the action of the function is indeed deterministic. If the DETERMINISTIC
keyword is applied to a function whose action is not truly deterministic, then the result of queries involving that function is unpredictable.
If you change the semantics of a DETERMINISTIC
function and recompile it, then existing function-based indexes and materialized views report results for the prior version of the function. Thus, if you change the semantics of a function, you must manually rebuild any dependent function-based indexes and materialized views.
See Also: Oracle Database PL/SQL Language Reference forCREATE FUNCTION restrictions |
The Oracle Database parallel execution feature divides the work of executing a SQL statement across multiple processes. Functions invoked from a SQL statement that is run in parallel might have a separate copy run in each of these processes, with each copy invoked for only the subset of rows that are handled by that process.
Each process has its own copy of package variables. When parallel execution begins, these are initialized based on the information in the package specification and body as if a user is logging into the system; the values in package variables are not copied from the original login session. And changes made to package variables are not automatically propagated between the various sessions or back to the original session. Java STATIC
class attributes are similarly initialized and modified independently in each process. Because a function can use package (or Java STATIC
) variables to accumulate some value across the various rows it encounters, Oracle Database cannot assume that it is safe to parallelize the execution of all user-defined functions.
Oracle Database parallelizes functions that meet certain criteria for being parallelizable; however, the PARALLEL_ENABLE
keyword is the preferred way to mark your code as safe for parallel execution. This keyword is syntactically similar to DETERMINISTIC
as described in Declaring a Function; it is placed after the return value type in a declaration of the function, as in:
CREATE OR REPLACE FUNCTION f1 (
p1 NUMBER
) RETURN NUMBER PARALLEL_ENABLE
IS
BEGIN
RETURN p1 * 2;
END;
/
A PL/SQL function defined with CREATE
FUNCTION
might still be run in parallel without any explicit declaration that it is safe to do so, if the system can determine that it neither reads nor writes package variables nor invokes any function that might do so. A Java method or C function is never seen by the system as safe to run in parallel, unless the programmer explicitly indicates PARALLEL_ENABLE
on the call specification, or provides a PRAGMA
RESTRICT_REFERENCES
indicating that the function is sufficiently pure.
An additional runtime restriction is imposed on functions run in parallel as part of a parallelized DML statement. Such a function is not permitted to in turn execute a DML statement; it is subject to the same restrictions that are enforced on functions that are run inside a query (SELECT
) statement.
In a data warehousing environment, you might use PL/SQL functions to transform large amounts of data. Perhaps the data is passed through a series of transformations, each performed by a different function. PL/SQL table functions let you perform such transformations without significant memory overhead or the need to store the data in tables between each transformation stage. These functions can accept and return multiple rows, can return rows as they are ready rather than all at once, and can be parallelized.
See Also: Oracle Database PL/SQL Language Reference for more information about performing multiple transformations with pipelined table functions |
To analyze a set of rows and compute a result value, you can code your own aggregate function that works the same as a built-in aggregate like SUM
:
Define an ADT that defines these member functions:
ODCIAggregateInitialize
ODCIAggregateIterate
ODCIAggregateMerge
ODCIAggregateTerminate
Code the member functions. In particular, ODCIAggregateIterate
accumulates the result as it is invoked once for each row that is processed. Store any intermediate results using the attributes of the ADT.
Create the aggregate function, and associate it with the ADT.
Call the aggregate function from SQL queries, DML statements, or other places that you might use the built-in aggregates. You can include typical options such as DISTINCT
and ALL
in the invocation of the aggregate function.
See Also: Oracle Database Data Cartridge Developer's Guide for more information about user-defined aggregate functions |
Oracle and various Oracle tools are supplied with product-specific packages that define application programming interfaces (APIs) you can call from PL/SQL, SQL, Java, or other programming environments. This topic includes a summary of two widely used packages and a list of the most common packages with a brief description.
See Also: Oracle Database PL/SQL Packages and Types Reference for information on and usage of product-specific packages |
About the DBMS_OUTPUT Package
Package DBMS_OUTPUT
enables you to display output from PL/SQL blocks, subprograms, packages, and triggers. The package is especially useful for displaying PL/SQL debugging information. The procedure PUT_LINE
outputs information to a buffer that can be read by another trigger, procedure, or package. You display the information by calling the procedure GET_LINE
or by setting SERVEROUTPUT
ON
in SQL*Plus. See Inputting and Outputting Data with PL/SQL.
Example: Using PUT_LINE in the DBMS_OUTPUT Package shows how to display output from a PL/SQL block.
Using PUT_LINE in the DBMS_OUTPUT Package
-- set server output to ON to display output from DBMS_OUTPUT SET SERVEROUTPUT ON BEGIN DBMS_OUTPUT.PUT_LINE('These are the tables that ' || USER || ' owns:'); FOR item IN (SELECT table_name FROM user_tables) LOOP DBMS_OUTPUT.PUT_LINE(item.table_name); END LOOP; END; /
About the UTL_FILE Package
Package UTL_FILE
lets PL/SQL programs read and write operating system (OS) text files. It provides a restricted version of standard OS stream file I/O, including open, put, get, and close operations.
When you want to read or write a text file, you call the function FOPEN
, which returns a file handle for use in subsequent procedure calls. For example, the procedure PUT_LINE
writes a text string and line terminator to an open file, and the procedure GET_LINE
reads a line of text from an open file into an output buffer.
Common Oracle Supplied Packages
Table: Summary of Oracle Supplied PL/SQL Packages provides a list of the common PL/SQL packages included with Oracle Database.
Summary of Oracle Supplied PL/SQL Packages
Package Name | Description |
---|---|
|
Provides support for the asynchronous notification of database events. |
|
Lets you register an application name with the database for auditing or performance tracking purposes. |
|
Lets you add a message (of a predefined object type) onto a queue or to dequeue a message. |
|
Lets you perform administrative functions on a queue or queue table for messages of a predefined object type. |
|
Provides procedures to manage the configuration of Advanced Queuing asynchronous notification by email and HTTP. |
|
Plays a part in providing secure access to the Oracle JMS interfaces. |
|
Is part of a set of features that clients use to receive notifications when result sets of a query have changed. The package contains interfaces that can be used by mid-tier clients to register objects and specify delivery mechanisms. |
|
Lets you encrypt and decrypt stored data, can be used in conjunction with PL/SQL programs running network communications, and supports encryption and hashing algorithms. |
|
Lets you move all, or part of, a database between databases, including both data and metadata. |
|
Specifies the Oracle version numbers and other information useful for simple conditional compilation selections based on Oracle versions. |
|
Provides access to some SQL DDL statements from stored procedures, and provides special administration operations not available as DDLs. |
|
Implements server-side debuggers and provides a way to debug server-side PL/SQL program units. |
|
Describes the arguments of a stored procedure with full name translation and security checking. |
|
Implements the embedded PL/SQL gateway that enables a web browser to invoke a PL/SQL stored procedure through an HTTP listener. |
|
Provides a procedure that enables you to create an error logging table so that DML operations can continue after encountering errors rather than abort and roll back. |
|
Lets you copy a binary file within a database or to transfer a binary file between databases. |
|
Lets you flash back to a version of the database at a specified wall-clock time or a specified system change number (SCN). |
|
Lets you schedule administrative procedures that you want performed at periodic intervals; it is also the interface for the job queue. |
|
Provides general purpose routines for operations on Oracle Large Object ( |
|
Lets you request, convert and release locks through Oracle Lock Management services. |
|
Lets callers easily retrieve complete database object definitions (metadata) from the dictionary. |
|
Provides procedures for Data Encryption Standards. |
|
Accumulates information in a buffer so that it can be retrieved later. |
|
Provides a DBMS pipe service which enables messages to be sent between sessions. |
|
Provides a built-in random number generator. |
|
Lets you suspend large operations that run out of space or reach space limits after executing for a long time, fix the problem, and make the statement resume execution. |
|
Provides procedures to create rowids and to interpret their contents. |
|
Provides a collection of scheduling functions that are callable from any PL/SQL program. |
|
Lets you issue alerts when some threshold has been violated. |
|
Provides access to SQL |
|
Lets you use dynamic SQL to access the database. |
|
Reports whether a database can be transported between platforms using the RMAN |
|
Checks if the transportable set is self-contained. |
|
Consists of constants, which represent the built-in and user-defined types. |
|
Provides various utility routines. |
|
Provides the interface to query, modify and delete current system or session settings. |
|
Describes Resource Management and Access Control APIs for PL/SQL |
|
Describes versioning APIs |
|
Describes how an administrator can create a ConText index on the XML DB hierarchy and configure it for automatic maintenance |
|
Controls the Oracle XML DB repository security, which is based on Access Control Lists (ACLs). |
|
Explains access to XMLType objects |
|
Converts the results of a SQL query to a canonical XML format. |
|
Explains access to the contents and structure of XML documents. |
|
Provides database-to-XMLType functionality. |
|
Provides XML-to-database-type functionality. |
|
Explains procedures to register and delete XML schemas. |
|
Provides the ability to store XML data in relational tables. |
|
Describes how to format the output of the |
|
Explains access to the contents and structure of XML documents. |
|
Hypertext functions generate HTML tags. |
|
Enables users to take advantage of global variables |
|
Enables users to create form elements dynamically based on a SQL query instead of creating individual items page by page. |
|
Enables users to create form elements dynamically based on a SQL query instead of creating individual items page by page. |
|
Provides utilities for getting and setting session state, getting files, checking authorizations for users, resetting different states for users, and also getting and setting preferences for users. |
|
Hypertext procedures generate HTML tags. |
|
Provides an interface that enables the |
|
Provides an interface for sending and retrieving HTTP cookies from the client's browser. |
|
Provides a Global PLSQL Agent Authorization callback function |
|
Provides an interface to access the coordinates where a user clicked on an image. |
|
Contains subprograms that impose optimistic locking strategies so as to prevent lost updates. |
|
Provides an interface to locate text patterns within strings and replace the matched string with another string. |
|
Provides an interface for custom authentication. |
|
Contains subprograms used by |
|
Contains utility subprograms for performing operations such as getting the value of CGI environment variables, printing the data that is returned to the client, and printing the results of a query in an HTML table. |
|
Enables PL/SQL programs to use collection locators to query and update. |
|
Provides a set of data compression utilities. |
|
Provides database web services. |
|
Provides functions that encode RAW data into a standard encoded format so that the data can be transported between hosts. |
|
Enables your PL/SQL programs to read and write operating system text files and provides a restricted version of standard operating system stream file I/O. |
|
Enables HTTP callouts from PL/SQL and SQL to access data on the Internet or to call Oracle Web Server Cartridges. |
|
Provides a set of services (Oracle Globalization Service) that help developers build multilingual applications. |
|
Provides a procedure to support internet addressing. |
|
Retrieves and formats error messages in different languages. |
|
A utility for managing email which includes commonly used email features, such as attachments, CC, BCC, and return receipt. |
|
Provides SQL functions for |
|
Recompiles invalid PL/SQL modules, Java classes, indextypes and operators in a database, either sequentially or in parallel. |
|
Enables a PL/SQL program to access an object by providing a reference to the object. |
|
Provides PL/SQL functionality to send emails. |
|
Provides PL/SQL functionality to support simple TCP/IP-based communications between servers and the outside world. |
|
Provides escape and unescape mechanisms for URL characters. |