Release 2 (9.2)
Part Number A96590-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Application Developer's Guide - Fundamentals Release 2 (9.2) Part Number A96590-01 |
|
This chapter describes some of the procedural capabilities of Oracle for application development, including:
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 procedures, 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 Oracle Forms, contain a PL/SQL engine that lets you run PL/SQL locally. |
You can even use PL/SQL for some database applications in place of 3GL programs that use embedded SQL or the Oracle Call Interface (OCI).
PL/SQL program units include:
| See Also,:
PL/SQL User's Guide and Reference. for syntax and examples of operations on PL/SQL packages. Oracle9i Supplied PL/SQL Packages and Types Reference. for information about the PL/SQL packages that come with the Oracle database server. |
An anonymous block is a PL/SQL program unit that has no name and it does not require the explicit presence of the BEGIN and END keywords to enclose the executable statements. 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 called 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.
The following short example of a PL/SQL anonymous block prints the names of all employees in department 20 in the Emp_tab table, using the DBMS_OUTPUT package:
DECLARE Emp_name VARCHAR2(10); Cursor c1 IS SELECT Ename FROM Emp_tab WHERE Deptno = 20; BEGIN OPEN c1; LOOP FETCH c1 INTO Emp_name; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(Emp_name); END LOOP; END;
| See Also:
For complete information about the |
Exceptions let you handle Oracle error conditions within PL/SQL program logic. This allows your application to prevent the server from issuing an error that could cause the client application to abend. The following anonymous block handles the predefined Oracle exception NO_DATA_FOUND (which would result in an ORA-01403 error if not handled):
DECLARE Emp_number INTEGER := 9999; Emp_name VARCHAR2(10); BEGIN SELECT Ename INTO Emp_name FROM Emp_tab WHERE Empno = Emp_number; -- no such 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;
You can also define your own exceptions, declare them in the declaration part of a block, and define them in the exception part of the block. An example follows:
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 Ename INTO Emp_name FROM Emp_tab WHERE Empno = 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;
| See Also:
"Handling Run-Time PL/SQL Errors" and see the PL/SQL User's Guide and Reference. |
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 call stored procedures or to open cursor variables.
A stored procedure, function, or package is a PL/SQL program unit that:
Because a procedure or function is stored in the database, it must be named. This distinguishes it from other stored procedures and makes it possible for applications to call it. Each publicly-visible procedure or function in a schema must have a unique name, and the name must be a legal PL/SQL identifier.
|
Note: If you plan to call a stored procedure using a stub generated by SQL*Module, then the stored procedure name must also be a legal identifier in the calling host 3GL language, such as Ada or C. |
Stored procedures and functions can take parameters. The following example shows a stored procedure that is similar to the anonymous block in "Anonymous Blocks".
PROCEDURE Get_emp_names (Dept_num IN NUMBER) IS Emp_name VARCHAR2(10); CURSOR c1 (Depno NUMBER) IS SELECT Ename FROM Emp_tab WHERE deptno = Depno; 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;
In this stored procedure example, the department number is an input parameter which is used when the parameterized cursor c1 is opened.
The formal parameters of a procedure have three major attributes:
Parameter modes define the behavior of formal parameters. The three parameter modes, IN (the default), OUT, and IN OUT, can be used with any subprogram. However, avoid using the OUT and IN OUT modes with functions. The purpose of a function is to take no arguments and return a single value. It is poor programming practice to have a function return multiple values. Also, functions should be free from side effects, which change the values of variables not local to the subprogram.
Table 9-1 summarizes the information about parameter modes.
| See Also:
Parameter modes are explained in detail in the PL/SQL User's Guide and Reference. |
The datatype of a formal parameter consists of one of the following:
NUMBER or VARCHAR2.%TYPE or %ROWTYPE attributes.
Use the type attributes %TYPE and %ROWTYPE to constrain the parameter. For example, the Get_emp_names procedure specification in "Parameters for Procedures and Functions" could be written as the following:
PROCEDURE Get_emp_names(Dept_num IN Emp_tab.Deptno%TYPE)
This has the Dept_num parameter take the same datatype as the Deptno column in the Emp_tab 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, then it is not necessary to change the application code.
If the Get_emp_names procedure is part of a package, then you can use previously-declared public (package) variables to constrain a parameter datatype. 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 following example defines the Get_emp_rec procedure, which returns all the columns of the Emp_tab table in a PL/SQL record for the given empno:
PROCEDURE Get_emp_rec (Emp_number IN Emp_tab.Empno%TYPE, Emp_ret OUT Emp_tab%ROWTYPE) IS BEGIN SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno INTO Emp_ret FROM Emp_tab WHERE Empno = Emp_number; END;
You could call this procedure from a PL/SQL block as follows:
DECLARE Emp_row Emp_tab%ROWTYPE; -- declare a record matching a -- row in the Emp_tab table BEGIN Get_emp_rec(7499, Emp_row); -- call for Emp_tab# 7499 DBMS_OUTPUT.PUT(Emp_row.Ename || ' ' || Emp_row.Empno); DBMS_OUTPUT.PUT(' ' || Emp_row.Job || ' ' || Emp_row.Mgr); DBMS_OUTPUT.PUT(' ' || Emp_row.Hiredate || ' ' || Emp_row.Sal); DBMS_OUTPUT.PUT(' ' || Emp_row.Comm || ' '|| Emp_row.Deptno); DBMS_OUTPUT.NEW_LINE; END;
Stored functions can also return values that are declared using %ROWTYPE. For example:
FUNCTION Get_emp_rec (Dept_num IN Emp_tab.Deptno%TYPE) RETURN Emp_tab%ROWTYPE IS ...
You can pass PL/SQL tables as parameters to stored procedures and functions. You can also pass tables of records as parameters.
Parameters can take default values. Use the DEFAULT keyword or the assignment operator to give a parameter a default value. For example, the specification for the Get_emp_names procedure could be written as the following:
PROCEDURE Get_emp_names (Dept_num IN NUMBER DEFAULT 20) IS ...
or
PROCEDURE Get_emp_names (Dept_num IN NUMBER := 20) IS ...
When a parameter takes a default value, it can be omitted from the actual parameter list when you call the procedure. When you do specify the parameter value on the call, it overrides the default value.
|
Note: Unlike in an anonymous PL/SQL block, you do not use the keyword |
Use a text editor to write the procedure or function. At the beginning of the procedure, place the following statement:
CREATE PROCEDURE Procedure_name AS ...
For example, to use the example in "%TYPE and %ROWTYPE Attributes", create a text (source) file called get_emp.sql containing the following code:
CREATE PROCEDURE Get_emp_rec (Emp_number IN Emp_tab.Empno%TYPE, Emp_ret OUT Emp_tab%ROWTYPE) AS BEGIN SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno INTO Emp_ret FROM Emp_tab WHERE Empno = Emp_number; END; /
Then, using an interactive tool such as SQL*Plus, load the text file containing the procedure by entering the following statement:
SQL> @get_emp
This loads the procedure into the current schema from the get_emp.sql file (.sql is the default file extension). Note the slash (/) at the end of the code. This is not part of the code; it just activates the loading of the procedure.
Use the CREATE [OR REPLACE] FUNCTION... statement to store functions.
You can use either the keyword IS or AS after the procedure parameter list.
| See Also:
Oracle9i Database Reference for the complete syntax of the |
To create a standalone procedure or function, or package specification or body, you must meet the following prerequisites:
CREATE PROCEDURE system privilege to create a procedure or package in your schema, or the CREATE ANY PROCEDURE system privilege to create a procedure or package in another user's schema.
If the privileges of a procedure's or a package's owner change, then the procedure must be reauthenticated before it is run. If a necessary privilege to a referenced object is revoked from the owner of the procedure or package, then the procedure cannot be run.
The EXECUTE privilege on a procedure gives a user the right to run a procedure owned by another user. Privileged users run the procedure under the security domain of the procedure's owner. Therefore, users never need to be granted the privileges to the objects referenced by a procedure. This allows for more disciplined and efficient security strategies with database applications and their users. Furthermore, all procedures 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 procedures and packages.
|
Note: Package creation requires a sort. So the user creating the package should be able to create a sort segment in the temporary tablespace with which the user is associated. |
To alter a stored procedure or function, 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 procedure or function if it exists, then recreates it as specified.
You can control some runtime behavior for PL/SQL procedures and functions by setting parameters within the database. These parameters can apply to all PL/SQL procedures and functions, or to a particular procedure or function. For example:
-- Set default behavior for PL/SQL procedures and functions ALTER SESSION SET PLSQL_V2_COMPATIBILITY = TRUE; -- Use a different setting for this one procedure ALTER PROCEDURE myproc SET PLSQL_V2_COMPATIBILITY = FALSE;
The parameters can apply to procedures, functions, packages, types, and triggers. Once specified, the settings apply whenever these schema objects are updated by CREATE OR REPLACE or the automatic recompilation that happens when the object is invalidated. When a schema object is dropped, any settings that apply to only that object are lost.
You can find out what settings are in effect by querying the catalog views ALL_PLSQL_SWITCH_SETTINGS and USER_PLSQL_SWITCH_SETTINGS. You can find out all the possible setting names and parameters by querying the view ALL_PLSQL_SWITCHES. Within an application, you can also find this information by calling functions in the DBMS_DESCRIBE package.
A standalone procedure, 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 a package's specification and body.
The following statement drops the Old_sal_raise procedure in your schema:
DROP PROCEDURE Old_sal_raise;
To drop a procedure, function, or package, the procedure or package must be in your schema, or you must have the DROP ANY PROCEDURE privilege. An individual procedure within a package cannot be dropped; the containing package specification and body must be re-created without the procedures to be dropped.
A PL/SQL procedure executing on an Oracle Server can call an external procedure written in a 3GL. The 3GL procedure runs in a separate address space from that of the Oracle Server.
| See Also:
For information about external procedures, see the Chapter 10, "Calling External Procedures". |
A package is an encapsulated collection of related program objects (for example, procedures, functions, variables, constants, cursors, and exceptions) stored together in the database.
Using packages is an alternative to creating procedures and functions as standalone schema objects. Packages have many advantages over standalone procedures and functions. For example, they:
| See Also:
The PL/SQL User's Guide and Reference has 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 the objects declared in the specification, as well as private objects that are not visible to applications outside the package.
The following example shows a package specification for a package named Employee_management. The package contains one stored function and two stored procedures. The body for this package defines the function and the procedures:
CREATE PACKAGE BODY Employee_management AS FUNCTION Hire_emp (Name VARCHAR2, Job VARCHAR2, Mgr NUMBER, Hiredate DATE, Sal NUMBER, Comm NUMBER, Deptno NUMBER) RETURN NUMBER IS New_empno NUMBER(10); -- This function accepts all arguments for the fields in -- the employee table except for the employee number. -- A value for this field is supplied by a sequence. -- The function returns the sequence number generated -- by the call to this function. BEGIN SELECT Emp_sequence.NEXTVAL INTO New_empno FROM dual; INSERT INTO Emp_tab VALUES (New_empno, Name, Job, Mgr, Hiredate, Sal, Comm, Deptno); RETURN (New_empno); END Hire_emp; PROCEDURE fire_emp(emp_id IN NUMBER) AS -- This procedure deletes the employee with an employee -- number that corresponds to the argument Emp_id. If -- no employee is found, then an exception is raised. BEGIN DELETE FROM Emp_tab WHERE Empno = 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) AS -- This procedure accepts two arguments. Emp_id is a -- number that corresponds to an employee number. -- SAL_INCR is the amount by which to increase the -- employee's salary. If employee exists, then update -- salary with increase. BEGIN UPDATE Emp_tab SET Sal = Sal + Sal_incr WHERE Empno = 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;
The size limitation for PL/SQL stored database objects such as procedures, functions, triggers, and packages is the size of the DIANA in the shared pool in bytes. The UNIX limit on the size of the flattened DIANA/pcode size is 64K but the limit may be 32K on desktop platforms such as DOS and Windows.
The most closely related number that a user can access is the PARSED_SIZE in the data dictionary view USER_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.
The size limitation of a PL/SQL package is approximately 128K parsed size in release 7.3. For releases earlier than 7.3 the limitation is 64K.
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 procedures and functions declared in the package specification.
You can also define private, or local, package procedures, functions, and variables in a package body. These objects can only be accessed by other procedures and functions 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 would then be the following:
CREATE OR REPLACE PACKAGE Package_name AS ...
and
CREATE OR REPLACE PACKAGE BODY Package_name AS ...
The body of a package can contain include:
Procedures, functions, cursors, and variables that are declared in the package specification are global. They can be called, 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, make sure that each procedure that you define in the body has the same parameters, by name, datatype, 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 procedure or function.
| See Also:
"Privileges to Create Procedures and Functions" and "Privileges to Drop Procedures and Functions". |
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 procedure 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 subsequently invalidated and recompiled, then all other dependent package instantiations (including state) for the session are lost.
For example, assume that session S instantiates packages P1 and P2, and that a procedure in package P1 calls a procedure in package P2. If P1 is invalidated and recompiled (for example, as the result of a DDL operation), then the session S instantiations of both P1 and P2 are lost. In such situations, a session receives the following error the first time it attempts to use any object of an invalidated package instantiation:
ORA-04068: existing state of packages has been discarded
The second time a session makes such a package call, the package is reinstantiated for the session without error.
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 specification or body invalidations are common in your system during working hours, then you might want to code your applications to detect for this error when package calls are made.
There are many built-in packages provided with the Oracle database, either to extend the functionality of the database or to give PL/SQL access to SQL features. You can call these packages from your application.
These packages run as the calling user, rather than the package owner. Unless otherwise noted, the packages are callable through public synonyms of the same name.
For details on all of these Oracle-supplied packages, see:
DBMS_JOB, DBMS_LOB, DBMS_LOCK, DBMS_OUTPUT, DBMS_RANDOM, DBMS_SQL, DBMS_UTILITY, DBMS_XMLGEN, UTL_FILE, UTL_HTTP, and UTL_SMTP, .Oracle 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 the following:
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 if you should 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 the PL/SQL User's Guide and Reference. |
If you have scenarios like these in your applications, consider using bulk binds to improve performance.
The FORALL keyword can improve the performance of INSERT, UPDATE, or DELETE statements that reference collection elements.
For example, the following PL/SQL block increases the salary for employees whose manager's ID number is 7902, 7698, or 7839, both with and without using bulk binds:
DECLARE TYPE Numlist IS VARRAY (100) OF NUMBER; Id NUMLIST := NUMLIST(7902, 7698, 7839); BEGIN -- Efficient method, using a bulk bind FORALL i IN Id.FIRST..Id.LAST -- bulk-bind the VARRAY UPDATE Emp_tab SET Sal = 1.1 * Sal WHERE Mgr = Id(i); -- Slower method, running the UPDATE statements within a regular loop FOR i IN Id.FIRST..Id.LAST LOOP UPDATE Emp_tab SET Sal = 1.1 * Sal WHERE Mgr = Id(i); END LOOP; END;
Without the bulk bind, PL/SQL sends a SQL statement to the SQL engine for each employee that is updated, leading to context switches that hurt performance.
If you have a set of rows prepared in a PL/SQL table, you can bulk-insert or bulk-update the data using a loop like:
FORALL i in Emp_Data.FIRST..Emp_Data.LAST INSERT INTO Emp_tab VALUES(Emp_Data(i));
The BULK COLLECT INTO clause can improve the performance of queries that reference collections.
For example, the following PL/SQL block queries multiple values into PL/SQL tables, both with and without bulk binds:
-- Find all employees whose manager's ID number is 7698. DECLARE TYPE Var_tab IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; Empno VAR_TAB; Ename VAR_TAB; Counter NUMBER; CURSOR C IS SELECT Empno, Ename FROM Emp_tab WHERE Mgr = 7698; BEGIN -- Efficient method, using a bulk bind SELECT Empno, Ename BULK COLLECT INTO Empno, Ename FROM Emp_Tab WHERE Mgr = 7698; -- Slower method, assigning each collection element within a loop. counter := 1; FOR rec IN C LOOP Empno(Counter) := rec.Empno; Ename(Counter) := rec.Ename; Counter := Counter + 1; END LOOP; END;
You can use BULK COLLECT INTO with tables of scalar values, or tables of %TYPE values.
Without the bulk bind, PL/SQL sends a SQL statement to the SQL engine for each employee that is selected, leading to context switches that hurt performance.
You can use the FORALL keyword along with the BULK COLLECT INTO keywords to improve the performance of FOR loops that reference collections and return DML.
For example, the following PL/SQL block updates the Emp_tab table by computing bonuses for a collection of employees; then it returns the bonuses in a column called Bonlist. The actions are performed both with and without using bulk binds:
DECLARE TYPE Emplist IS VARRAY(100) OF NUMBER; Empids EMPLIST := EMPLIST(7369, 7499, 7521, 7566, 7654, 7698); TYPE Bonlist IS TABLE OF Emp_tab.sal%TYPE; Bonlist_inst BONLIST; BEGIN Bonlist_inst := BONLIST(1,2,3,4,5); FORALL i IN Empids.FIRST..empIDs.LAST UPDATE Emp_tab SET Bonus = 0.1 * Sal WHERE Empno = Empids(i) RETURNING Sal BULK COLLECT INTO Bonlist; FOR i IN Empids.FIRST..Empids.LAST LOOP UPDATE Emp_tab Set Bonus = 0.1 * sal WHERE Empno = Empids(i) RETURNING Sal INTO BONLIST(i); END LOOP; END;
Without the bulk bind, PL/SQL sends a SQL statement to the SQL engine for each employee that is updated, leading to context switches that hurt performance.
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).
You can deliver your stored procedures in object code format using the PL/SQL Wrapper. Wrapping your PL/SQL code hides your application internals. To run the PL/SQL Wrapper, enter the WRAP statement at your system prompt using the following syntax:
wrap INAME=input_file [ONAME=output_file]
| See Also:
For complete instructions on using the PL/SQL Wrapper, see the PL/SQL User's Guide and Reference. |
You can speed up PL/SQL procedures by compiling them into native code residing in shared libraries. The procedures are translated into C code, then compiled with your usual C compiler and linked into the Oracle process.
You can use this technique with both the supplied Oracle PL/SQL packages, and procedures you write yourself. You can use the ALTER SYSTEM or ALTER SESSION command, or update your initialization file, to set the parameter PLSQL_COMPILER_FLAGS to include the value NATIVE. The default setting includes the value INTERPRETED, and you must remove this keyword from the parameter value.
Because this technique cannot do much to speed up SQL statements called from these procedures, it is most effective for compute-intensive procedures that do not spend much time executing SQL.
With Java, you can use the ncomp tool to compile your own packages and classes.
| See Also:
For full details on PL/SQL native compilation, see the Tuning chapter of the PL/SQL User's Guide and Reference. For full details on Java native compilation, see the Oracle9i Java Developer's Guide. |
Dependencies among PL/SQL program units can be handled in two ways:
If timestamps are used to handle dependencies among PL/SQL program units, then whenever you alter a program unit or a relevant schema object, all of its dependent units are marked as invalid and must be recompiled before they can be run.
Each program unit carries a timestamp that is set by the server when the unit is created or recompiled. Figure 9-1 demonstrates this graphically. Procedures P1 and P2 call stored procedure P3. Stored procedure P3 references table T1. In this example, each of the procedures is dependent on table T1. P3 depends upon T1 directly, while P1 and P2 depend upon T1 indirectly.
If P3 is altered, then P1 and P2 are marked as invalid immediately, if they are on the same server as P3. The compiled states of P1 and P2 contain records of the timestamp of P3. Therefore, if the procedure P3 is altered and recompiled, then the timestamp on P3 no longer matches the value that was recorded for P3 during the compilation of P1 and P2.
If P1 and P2 are on a client system, or on another Oracle Server in a distributed environment, then the timestamp information is used to mark them as invalid at runtime.
The disadvantage of this dependency model is that it is unnecessarily restrictive. Recompilation of dependent objects across the network are often performed when not strictly necessary, leading to performance degradation.
Furthermore, on the client side, the timestamp model can lead to situations that block an application from running at all, if the client-side application is built using PL/SQL version 2. Earlier releases of tools, such as Oracle Forms, that used PL/SQL version 1 on the client side did not use this dependency model, because PL/SQL version 1 had no support for stored procedures.
For releases of Oracle Forms that are integrated with PL/SQL version 2 on the client side, the timestamp model can present problems. For example, during the installation of the application, the application is rendered invalid unless the client-side PL/SQL procedures that it uses are recompiled at the client site. Also, if a client-side procedure depends on a server procedure, and if the server procedure is changed or automatically recompiled, then the client-side PL/SQL procedure must then be recompiled. Yet in many application environments (such as Forms runtime applications), there is no PL/SQL compiler available on the client. This blocks the application from running at all. The client application developer must then redistribute new versions of the application to all customers.
To alleviate some of the problems with the timestamp-only dependency model, Oracle provides the additional capability of remote dependencies using signatures. The signature capability affects only remote dependencies. Local (same server) dependencies are not affected, as recompilation is always possible in this environment.
A signature is associated with each compiled stored program unit. It identifies the unit using the following criteria:
IN, OUT, IN OUT).The user has control over whether signatures or timestamps govern remote dependencies.
When the signature dependency model is used, a dependency on a remote program unit causes an invalidation of the dependent unit if the dependent unit contains a call to a subprogram in the parent unit, and if the signature of this subprogram has been changed in an incompatible manner.
For example, consider a procedure get_emp_name stored on a server in Boston (BOSTON_SERVER). The procedure is defined as the following:
CREATE OR REPLACE PROCEDURE get_emp_name ( emp_number IN NUMBER, hire_date OUT VARCHAR2, emp_name OUT VARCHAR2) AS BEGIN SELECT ename, to_char(hiredate, 'DD-MON-YY') INTO emp_name, hire_date FROM emp WHERE empno = emp_number; END;
When get_emp_name is compiled on BOSTON_SERVER, its signature, as well as its timestamp, is recorded.
Now, assume that on another server in California, some PL/SQL code calls get_emp_name identifying it using a DBlink called BOSTON_SERVER, as follows:
CREATE OR REPLACE PROCEDURE print_ename (emp_number IN NUMBER) AS hire_date VARCHAR2(12); ename VARCHAR2(10); BEGIN get_emp_name@BOSTON_SERVER(emp_number, hire_date, ename); dbms_output.put_line(ename); dbms_output.put_line(hire_date); END;
When this California server code is compiled, the following actions take place:
get_emp_name is transferred to the California server.print_ename.At runtime, during the remote procedure call from the California server to the Boston server, the recorded signature of get_emp_name that was saved in the compiled state of print_ename gets sent to the Boston server, regardless of whether or not there were any changes.
If the timestamp dependency mode is in effect, then a mismatch in timestamps causes an error status to be returned to the calling procedure.
However, if the signature mode is in effect, then any mismatch in timestamps is ignored, and the recorded signature of get_emp_name in the compiled state of Print_ename on the California server is compared with the current signature of get_emp_name on the Boston server. If they match, then the call succeeds. If they do not match, then an error status is returned to the print_name procedure.
Note that the get_emp_name procedure on the Boston server could have been changed. Or, its timestamp could be different from that recorded in the print_name procedure on the California server, possibly due to the installation of a new release of the server. As long as the signature remote dependency mode is in effect on the California server, a timestamp mismatch does not cause an error when get_emp_name is called.
A signature changes when you switch from one class of datatype to another. Within each datatype class, there can be several types. Changing a parameter datatype from one type to another within a class does not cause the signature to change. Datatypes that are not listed in the following table, such as NCHAR or TIMESTAMP, are not part of any class; changing their type always causes a signature mismatch.
VARCHAR types: VARCHAR2, VARCHAR, STRING, LONG, ROWID
Character types: CHARACTER, CHAR
Raw types: RAW, LONG RAW
Integer types: BINARY_INTEGER, PLS_INTEGER, BOOLEAN, NATURAL, POSITIVE, POSITIVEN, NATURALN
Number types: NUMBER, INTEGER, INT, SMALLINT, DECIMAL, DEC, REAL, FLOAT, NUMERIC, DOUBLE PRECISION, DOUBLE PRECISION, NUMERIC
Date types: DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND
Changing to or from an explicit specification of the default parameter mode IN does not change the signature of a subprogram. For example, changing between:
PROCEDURE P1 (Param1 NUMBER); PROCEDURE P1 (Param1 IN NUMBER);
does not change the signature. Any other change of parameter mode does change the signature.
Changing the specification of a default parameter value does not change the signature. For example, procedure P1 has the same signature in the following two examples:
PROCEDURE P1 (Param1 IN NUMBER := 100); PROCEDURE P1 (Param1 IN NUMBER := 200);
An application developer who requires that callers get the new default value must recompile the called procedure, but no signature-based invalidation occurs when a default parameter value assignment is changed.
Using the Get_emp_names procedure defined in "Parameters for Procedures and Functions", if the procedure body is changed to the following:
DECLARE Emp_number NUMBER; Hire_date DATE; BEGIN -- date format model changes SELECT Ename, To_char(Hiredate, 'DD/MON/YYYY') INTO Emp_name, Hire_date FROM Emp_tab WHERE Empno = Emp_number; END;
The specification of the procedure has not changed, so its signature has not changed.
But if the procedure specification is changed to the following:
CREATE OR REPLACE PROCEDURE Get_emp_name ( Emp_number IN NUMBER, Hire_date OUT DATE, Emp_name OUT VARCHAR2) AS
And if the body is changed accordingly, then the signature changes, because the parameter Hire_date has a different datatype.
However, if the name of that parameter changes to When_hired, and the datatype remains VARCHAR2, and the mode remains OUT, the signature does not change. Changing the name of a formal parameter does not change the signature of the unit.
Consider the following example:
CREATE OR REPLACE PACKAGE Emp_package AS TYPE Emp_data_type IS RECORD ( Emp_number NUMBER, Hire_date VARCHAR2(12), Emp_name VARCHAR2(10)); PROCEDURE Get_emp_data (Emp_data IN OUT Emp_data_type); END; CREATE OR REPLACE PACKAGE BODY Emp_package AS PROCEDURE Get_emp_data (Emp_data IN OUT Emp_data_type) IS BEGIN SELECT Empno, Ename, TO_CHAR(Hiredate, 'DD/MON/YY') INTO Emp_data FROM Emp_tab WHERE Empno = Emp_data.Emp_number; END; END;
If the package specification is changed so that the record's field names are changed, but the types remain the same, then this does not affect the signature. For example, the following package specification has the same signature as the previous package specification example:
CREATE OR REPLACE PACKAGE Emp_package AS TYPE Emp_data_type IS RECORD ( Emp_num NUMBER, -- was Emp_number Hire_dat VARCHAR2(12), -- was Hire_date Empname VARCHAR2(10)); -- was Emp_name PROCEDURE Get_emp_data (Emp_data IN OUT Emp_data_type); END;
Changing the name of the type of a parameter does not cause a change in the signature if the type remains the same as before. For example, the following package specification for Emp_package is the same as the first one:
CREATE OR REPLACE PACKAGE Emp_package AS TYPE Emp_data_record_type IS RECORD ( Emp_number NUMBER, Hire_date VARCHAR2(12), Emp_name VARCHAR2(10)); PROCEDURE Get_emp_data (Emp_data IN OUT Emp_data_record_type); END;
The dynamic initialization parameter REMOTE_DEPENDENCIES_MODE controls whether the timestamp or the signature dependency model is in effect.
REMOTE_DEPENDENCIES_MODE = TIMESTAMP
Then only timestamps are used to resolve dependencies (if this is not explicitly overridden dynamically).
REMOTE_DEPENDENCIES_MODE = SIGNATURE
Then signatures are used to resolve dependencies (if this not explicitly overridden dynamically).
ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = {SIGNATURE | TIMESTAMP}
Thise example alters the dependency model systemwide after startup: ALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE = {SIGNATURE | TIMESTAMP}
If the REMOTE_DEPENDENCIES_MODE parameter is not specified, either in the init.ora parameter file or using the ALTER SESSION or ALTER SYSTEM DDL statements, then timestamp is the default value. Therefore, unless you explicitly use the REMOTE_DEPENDENCIES_MODE parameter, or the appropriate DDL statement, your server is operating using the timestamp dependency model.
When you use REMOTE_DEPENDENCIES_MODE=SIGNATURE:
When REMOTE_DEPENDENCIES_MODE = TIMESTAMP (the default value), dependencies among program units are handled by comparing timestamps at runtime. If the timestamp of a called remote procedure does not match the timestamp of the called procedure, then the calling (dependent) unit is invalidated and must be recompiled. In this case, if there is no local PL/SQL compiler, then the calling application cannot proceed.
In the timestamp dependency mode, signatures are not compared. If there is a local PL/SQL compiler, then recompilation happens automatically when the calling procedure is run.
When REMOTE_DEPENDENCIES_MODE = SIGNATURE, the recorded timestamp in the calling unit is first compared to the current timestamp in the called remote unit. If they match, then the call proceeds. If the timestamps do not match, then the signature of the called remote subprogram, as recorded in the calling subprogram, is compared with the current signature of the called subprogram. If they do not match (using the criteria described in the section "When Does a Signature Change?"), then an error is returned to the calling session.
Oracle recommends that you follow these guidelines for setting the REMOTE_DEPENDENCIES_MODE parameter:
TIMESTAMP (or let it default to that) to get the timestamp dependency mode.SIGNATURE. This allows:
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 procedures and functions. A cursor variable can also refer to different cursors in its lifetime.
Some additional advantages of cursor variables include:
EXECUTE permission on the stored procedure that opens the cursor. But, the user does not need to have READ permission on the tables used in the query. This capability can be used to limit access to the columns in the table, as well as access to other stored procedures.
| See Also:
The PL/SQL User's Guide and Reference has a complete discussion of cursor variables. |
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 includes several examples of cursor variable usage in PL/SQL. For additional cursor variable examples that use the programmatic interfaces, see the following manuals:
The following package defines a PL/SQL cursor variable type Emp_val_cv_type, and two procedures. The first procedure, Open_emp_cv, opens the cursor variable using a bind variable in the WHERE clause. The second procedure, Fetch_emp_data, fetches rows from the Emp_tab table using the cursor variable.
CREATE OR REPLACE PACKAGE Emp_data AS TYPE Emp_val_cv_type IS REF CURSOR RETURN Emp_tab%ROWTYPE; PROCEDURE Open_emp_cv (Emp_cv IN OUT Emp_val_cv_type, Dept_number IN INTEGER); PROCEDURE Fetch_emp_data (emp_cv IN Emp_val_cv_type, emp_row OUT Emp_tab%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 INTEGER) IS BEGIN OPEN emp_cv FOR SELECT * FROM Emp_tab WHERE deptno = dept_number; END open_emp_cv; PROCEDURE Fetch_emp_data (Emp_cv IN Emp_val_cv_type, Emp_row OUT Emp_tab%ROWTYPE) IS BEGIN FETCH Emp_cv INTO Emp_row; END Fetch_emp_data; END Emp_data;
The following example shows how to call the Emp_data package procedures from a PL/SQL block:
DECLARE -- declare a cursor variable Emp_curs Emp_data.Emp_val_cv_type; Dept_number Dept_tab.Deptno%TYPE; Emp_row Emp_tab%ROWTYPE; BEGIN Dept_number := 20; -- open the cursor using a variable Emp_data.Open_emp_cv(Emp_curs, Dept_number); -- fetch the data and display it LOOP Emp_data.Fetch_emp_data(Emp_curs, Emp_row); EXIT WHEN Emp_curs%NOTFOUND; DBMS_OUTPUT.PUT(Emp_row.Ename || ' '); DBMS_OUTPUT.PUT_LINE(Emp_row.Sal); END LOOP; END;
The power of cursor variables comes from their ability to point to different cursors. In the following package example, a discriminant is used to open a cursor variable to point to one of two different cursors:
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 Emp_tab WHERE Sal > 2000; ELSIF Discrim = 2 THEN OPEN Cv FOR SELECT * FROM Dept_tab; END IF; END Open_cv; END Emp_dept_data;
You can call the Open_cv procedure to open the cursor variable and point it to either a query on the Emp_tab table or the Dept_tab table. The following PL/SQL block shows how to fetch using the cursor variable, and then use the ROWTYPE_MISMATCH predefined exception to handle either fetch:
DECLARE Emp_rec Emp_tab%ROWTYPE; Dept_rec Dept_tab%ROWTYPE; Cv Emp_dept_data.CV_TYPE; BEGIN Emp_dept_data.open_cv(Cv, 1); -- Open Cv For Emp_tab Fetch Fetch cv INTO Dept_rec; -- but fetch into Dept_tab record -- which raises ROWTYPE_MISMATCH DBMS_OUTPUT.PUT(Dept_rec.Deptno); DBMS_OUTPUT.PUT_LINE(' ' || Dept_rec.Loc); EXCEPTION WHEN ROWTYPE_MISMATCH THEN BEGIN DBMS_OUTPUT.PUT_LINE ('Row type mismatch, fetching Emp_tab data...'); FETCH Cv INTO Emp_rec; DBMS_OUTPUT.PUT(Emp_rec.Deptno); DBMS_OUTPUT.PUT_LINE(' ' || Emp_rec.Ename); END;
When you use SQL*Plus to submit PL/SQL code, and when the code contains errors, you receive notification that compilation errors have occurred, but there is no immediate indication of what the errors are. For example, if you submit a standalone (or stored) procedure PROC1 in the file proc1.sql as follows:
SQL> @proc1
And, if there are one or more errors in the code, then you receive a notice such as the following:
MGR-00072: Warning: Procedure proc1 created with compilation errors
In this case, use the SHOW ERRORS statement in SQL*Plus to get a list of the errors that were found. SHOW ERRORS with no argument lists the errors from the most recent compilation. You can qualify SHOW ERRORS using the name of a procedure, function, package, or package body:
SQL> SHOW ERRORS PROC1 SQL> SHOW ERRORS PROCEDURE PROC1
| See Also:
See the SQL*Plus User's Guide and Reference for complete information about the |
|
Note: Before issuing the SET LINESIZE 132 |
Assume that you want to create a simple procedure that deletes records from the employee table using SQL*Plus:
CREATE OR REPLACE PROCEDURE Fire_emp(Emp_id NUMBER) AS BEGIN DELETE FROM Emp_tab WHER Empno = Emp_id; END /
Notice that the CREATE PROCEDURE statement has two errors: the DELETE statement has an error (the 'E' is absent from WHERE), and the semicolon is missing after END.
After the CREATE PROCEDURE statement is entered and an error is returned, a SHOW ERRORS statement returns the following lines:
SHOW ERRORS; ERRORS FOR PROCEDURE Fire_emp: LINE/COL ERROR -------------- -------------------------------------------- 3/27 PL/SQL-00103: Encountered the symbol "EMPNO" wh. . . 5/0 PL/SQL-00103: Encountered the symbol "END" when . . . 2 rows selected.
Notice that each line and column number where errors were found is listed by the SHOW ERRORS statement.
Alternatively, you can query the following data dictionary views to list errors when using any tool or application:
The error text associated with the compilation of a procedure is updated when the procedure is replaced, and it is deleted when the procedure is dropped.
Original source code can be retrieved from the data dictionary using the following views: ALL_SOURCE, USER_SOURCE, and DBA_SOURCE.
| See Also:
Oracle9i Database Reference for more information about these data dictionary views. |
Oracle 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. After received, the client application can handle the error based on the user-specified error number and message returned by Oracle.
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 procedure execution, rolls back any effects of the procedure, 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.
Error number -20000 should be used 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 Kbytes or less (longer messages are ignored). Keep_error_stack can be TRUE if you want to add the error to any already on the stack, or FALSE if you want to replace the existing errors. By default, this option is FALSE.
The RAISE_APPLICATION_ERROR procedure is often used in exception handlers or in the logic of PL/SQL code. For example, the following exception handler selects the string for the associated user-defined error message and calls 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); ...
| See Also:
For information on exception handling when calling remote procedures, see"Handling Errors in Remote Procedures". |
The following section includes an example of passing a user-specified error number from a trigger to a procedure.
User-defined exceptions are explicitly defined and signaled within the PL/SQL block to control processing of errors specific to the application. When an exception is raised (signaled), the usual execution of the PL/SQL block stops, and a routine called an exception handler is called. 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:
RAISE statement that names the appropriate exception. A RAISE statement stops the execution of the procedure, and control passes to an exception handler (if any).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. For example, Figure 9-2 illustrates the following:
Declare a user-defined exception in a procedure or package body (private exceptions), or in the specification of a package (public exceptions). Define an exception handler in the body of a procedure (standalone or package).
