| Oracle7 Server Application Developer's Guide |  Library |
 Product |
 Contents |
 Index |
| Oracle7 Server Application Developer's Guide | Library |
Product |
Contents |
Index |
Note: If you are using Trusted Oracle, also see the Trusted Oracle7 Server Administrator's Guide for additional information.
You can directly issue 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 executes on the server, so using PL/SQL allows you to 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, and can execute 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).
There are several kinds of PL/SQL program units:
You use the declarative part to declare 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 table, using the DBMS_OUTPUT package (described ![[*]](jump.gif)
):
DECLARE
emp_name VARCHAR2(10);
CURSOR c1 IS SELECT ename FROM emp
WHERE deptno = 20;
BEGIN
LOOP
FETCH c1 INTO emp_name;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_name);
END LOOP;
END;Note: If you try this block out using SQL*Plus make sure to issue the command SET SERVEROUTPUT ON so that output using the DBMS_OUTPUT procedures such as PUT_LINE is activated. Also, terminate the example with a slash (/) to activate it.
Exceptions allow you to 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 abort. 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
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
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 the PL/SQL User's Guide and Reference for a complete treatment of exceptions.
Anonymous blocks are most often used either interactively, from a tool such as SQL*Plus, or in a precompiler, OCI, or SQL*Module application. They are normally used to call stored procedures, or to open cursor variables. (See page 7 - 26 for a description of cursor variables.)
Note: If you plan to call a stored procedure using a stub generated by SQL*Module, the stored procedure name must also be a legal identifier in the calling host 3GL language such as Ada or C.
Procedure and function names that are part of packages can be overloaded. That is, you can use the same name for different subprograms as long as their formal parameters differ in number, order, or datatype family. See the PL/SQL User's Guide and Reference for more information about subprogram name overloading.
:
PROCEDURE get_emp_names (dept_num IN NUMBER) IS
emp_name VARCHAR2(10);
CURSOR c1 (depno NUMBER) IS
SELECT ename FROM emp
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 the 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 parts:
name
The name of the parameter, which must be a legal PL/SQL identifier.
mode
The parameter mode, which indicates whether the parameter is an input-only parameter (IN), an output-only parameter (OUT), or is both an input and an output parameter (IN OUT). If the mode is not specified, IN is assumed.
datatype
The parameter datatype is a standard PL/SQL datatype.
Table 7 - 1 summarizes the information about parameter modes. Parameter modes are explained in detail in the PL/SQL User's Guide and Reference.
%TYPE and %ROWTYPE Attributes
However, you can use the type attributes %TYPE and %ROWTYPE to constrain the parameter. For example, the GET_EMP_NAMES procedure specification could be written as
PROCEDURE get_emp_names(dept_num IN emp.deptno%TYPE)
to have the DEPT_NUM parameter take the same datatype as the DEPTNO column in the EMP table. The column and table must be available when a declaration using %TYPE (or %ROWTYPE) is elaborated.
Using %TYPE is recommended, since 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, 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);
You 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, that returns all the columns of the EMP table in a PL/SQL record, for the given EMPNO:
PROCEDURE get_emp_rec (emp_number IN emp.empno%TYPE,
emp_ret OUT emp%ROWTYPE) IS
BEGIN
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
INTO emp_ret
FROM emp
WHERE empno = emp_number;
END;You could call this procedure from a PL/SQL block as follows:
DECLARE
emp_row emp%ROWTYPE; -- declare a record matching a
-- row in the EMP table
BEGIN
get_emp_rec(7499, emp_row); -- call for emp# 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.deptno%TYPE)
RETURN emp%ROWTYPE IS ...
could be written as
PROCEDURE get_emp_names (dept_num IN NUMBER DEFAULT 20) IS ...
or as
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.
CREATE PROCEDURE procedure_name AS ...
For example, to use the example , you can create a text (source) file called get_emp.sql containing the following code:
CREATE PROCEDURE get_emp_rec (emp_number IN emp.empno%TYPE,
emp_ret OUT emp%ROWTYPE) AS
BEGIN
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
INTO emp_ret
FROM emp
WHERE empno = emp_number;
END;Then, using an interactive too such as SQL*Plus, load the text file containing the procedure by entering the command
SQLPLUS> @get_emp
to load the procedure into the current schema. (.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.
Note: When developing a new procedure, it is usually much more convenient to use the CREATE OR REPLACE . . . PROCEDURE command. This replaces any previous version of that procedure in the same schema with the newer version. This is done with no warning.
You can use either the keyword IS or AS after the procedure parameter list.
Use the CREATE [OR REPLACE] FUNCTION . . . command to store functions. See the Oracle7 Server SQL Reference for the complete syntax of the CREATE PROCEDURE and CREATE FUNCTION commands.
The owner of the procedure or package must have been explicitly granted the necessary object privileges for all objects referenced within the body of the code; the owner cannot have obtained required privileges through roles.
If the privileges of a procedure's or package's owner change, the procedure must be reauthenticated before it is executed. If a necessary privilege to a referenced object is revoked from the owner of the procedure (or package), the procedure cannot be executed.
The EXECUTE privilege on a procedure gives a user the right to execute a procedure owned by another user. Privileged users execute the procedure under the security domain of the procedure's owner. Therefore, users never have 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.
The procedure or function is dropped with no warning.
The following example shows a package specification for a package named EMPLOYEE_MANAGEMENT. The package contains one stored function and two stored procedures.
CREATE PACKAGE employee_management AS
FUNCTION hire_emp (name VARCHAR2, job VARCHAR2,
mgr NUMBER, hiredate DATE, sal NUMBER, comm NUMBER,
deptno NUMBER) RETURN NUMBER;
PROCEDURE fire_emp (emp_id NUMBER);
PROCEDURE sal_raise (emp_id NUMBER, sal_incr NUMBER);
END employee_management;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
-- The 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.
new_empno NUMBER(10);
BEGIN
SELECT emp_sequence.NEXTVAL INTO new_empno FROM dual;
INSERT INTO emp VALUES (new_empno, name, job, mgr,
hiredate, sal, comm, deptno);
RETURN (new_empno);
END hire_emp;
PROCEDURE fire_emp(emp_id IN NUMBER) AS
-- The procedure deletes the employee with an employee
-- number that corresponds to the argument EMP_ID. If
-- no employee is found, an exception is raised.
BEGIN
DELETE FROM emp 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
-- The 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.
BEGIN
-- If employee exists, update salary with increase.
UPDATE emp
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;Note: If you want to try this example, first create the sequence number EMP_SEQUENCE. You can do this using the following SQL*Plus statement:
SQL> EXECUTE CREATE SEQUENCE emp_sequence > START WITH 8000 INCREMENT BY 10;
To create a package body, use the CREATE PACKAGE BODY command. The CREATE PACKAGE BODY command 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 within the package body. See ``Local Objects'' .
CREATE OR REPLACE PACKAGE package_name AS ...
and
CREATE OR REPLACE PACKAGE BODY package_name AS ...
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 as well as the return type must agree in name and type.
The following statement drops the OLD_SAL_RAISE procedure in your schema:
DROP PROCEDURE old_sal_raise;
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), 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.
Note: Oracle has been optimized to not return this message to the session calling the package that it invalidated. Thus, in the example above, session S would receive this message the first time it called package P2, but would not receive it when calling P1.
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, you might want to code your applications to detect for this error when package calls are made. For example, the user-side application might reinitialize any user-side state that depends on any session's package state (that was lost) and reissue the package call.
Figure 7 - 1. Dependency Relationships
If P3 is altered, 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. So if the procedure P3 is altered and recompiled, 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, the timestamp information is used to mark them as invalid at runtime.
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, since 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. First of all, 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 the server procedure is changed or automatically recompiled, 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.
The signature of a subprogram contains information about the
The user has control over whether signatures or timestamps govern remote dependencies. See the section ``Controlling Remote Dependencies'' for more information. If the signature dependency model is in effect, a dependency on a remote library unit causes an invalidation of the dependent unit if the dependent unit contains a call to a subprogram in the parent unit, and the signature of this subprogram has been changed in an incompatible manner.
For example, consider a procedure GET_EMP_NAME stored on a server BOSTON_SERVER. The procedure is defined as
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 the 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 DB link 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(hiredate);
END;When this California server code is compiled, the following actions take place:
If the timestamp dependency mode is in effect, a mismatch in timestamps causes an error status to be returned to the calling procedure.
However, if the signature mode is in effect, 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, the call succeeds. If they do not match, 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, due to, for example, 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.
PROCEDURE P1 (param1 NUMBER);
to
PROCEDURE P1 (param1 IN NUMBER);
does not change the signature. Any other change of parameter mode does change the signature.
Default Parameter Values: 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.
, if the procedure body is changed to
BEGIN
-- date format model changes
SELECT ename, to_char(hiredate, 'DD/MON/YYYY')
INTO emp_name, hire_date
FROM emp
WHERE empno = emp_number;
END;then the specification of the procedure has not changed, and so its signature has not changed.
But if the procedure specification is changed to
CREATE OR REPLACE PROCEDURE get_emp_name (
emp_number IN NUMBER,
hire_date OUT DATE,
emp_name OUT VARCHAR2) ASand 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, then 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
WHERE empno = emp_data.emp_number;
END;If the package specification is changed so that the record's field names are changed, but the types remain the same, 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;
If the initialization parameter file contains the specification
REMOTE_DEPENDENCIES_MODE = TIMESTAMP
and this is not explicitly overridden dynamically, then only timestamps are used to resolve dependencies. This is identical to the Oracle7 Server release 7.2 model.
If the initialization parameter file contains the parameter specification
REMOTE_DEPENDENCIES_MODE = SIGNATURE
and this not explicitly overridden dynamically, then signatures are used to resolve dependencies.
You can alter the mode dynamically by using the DDL commands
ALTER SESSION SET REMOTE_DEPENDENCIES_MODE =
{SIGNATURE | TIMESTAMP}to alter the dependency model for the current session, or
ALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE =
{SIGNATURE | TIMESTAMP}to alter the dependency model on a system-wide basis after startup.
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 commands, TIMESTAMP is the default value. So, unless you explicitly use the REMOTE_DEPENDENCIES_MODE parameter, or the appropriate DDL command, your server is operating using the release 7.2 timestamp dependency model.
When you use REMOTE_DEPENDENCIES_MODE=SIGNATURE you should be aware of the following:
In the timestamp dependency mode, signatures are not compared. If there is a local PL/SQL compiler, recompilation happens automatically when the calling procedure is executed.
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 normally. 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 ``What is a Signature'' , then an error is returned to the calling session.
Some additional advantages of cursor variables are
Starting with Oracle7 release 7.3, you can 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.
CREATE OR REPLACE PACKAGE emp_data AS
TYPE emp_val_cv_type IS REF CURSOR RETURN emp%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%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 WHERE deptno = dept_number;
END open_emp_cv;
PROCEDURE fetch_emp_data (emp_cv IN emp_val_cv_type,
emp_row OUT emp%ROWTYPE) IS
BEGIN
FETCH emp_cv INTO emp_row;
END fetch_emp_data;
END emp_data;
The following example shows how you can 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.deptno%TYPE;
emp_row emp%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;
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 WHERE sal > 2000;
ELSIF discrim = 2 THEN
OPEN cv FOR SELECT * FROM dept;
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 table or on the DEPT table. How would you use this? The following PL/SQL block shows that you can fetch using the cursor variable, then use the ROWTYPE_MISMATCH predefined exception to handle either fetch:
DECLARE
emp_rec emp%ROWTYPE;
dept_rec dept%ROWTYPE;
cv emp_dept_data.cv_type;
BEGIN
emp_dept_data.open_cv(cv, 1); -- open CV for EMP fetch
FETCH cv INTO dept_rec; -- but fetch into DEPT 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 data...');
FETCH cv into emp_rec;
DBMS_OUTPUT.PUT(emp_rec.deptno);
DBMS_OUTPUT.PUT_LINE(' ' || emp_rec.ename);
END;
END;
WRAP INAME=input_file [ONAME=ouput_file]
For complete instructions on using the PL/SQL Wrapper, see the PL/SQL User's Guide and Reference.
User-specified error messages are returned using the RAISE_APPLICATION_ERROR procedure:
RAISE_APPLICATION_ERROR(error_number, 'text', keep_error_stack)
This procedure terminates 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.
Attention: Some of the Oracle-supplied packages, such as DBMS_OUTPUT, DBMS_DESCRIBE, and DBMS_ALERT, use application error numbers in the range -20000 to -20005. See the descriptions of these pacakges for more information.
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.name = "NLS_LANGUAGE";
raise_application_error(-20101, message);
...Several examples earlier in this chapter also demonstrate the use of the RAISE_APPLICATION_ERROR procedure. The next section has an example of passing a user-specified error number from a trigger to a procedure. For information on exception handling when calling remote procedures, see page 7 - 33.
Application code can check for a condition that requires special attention using an IF statement. If there is an error condition, two options are available:
Figure 7 - 2. Exceptions and User-Defined Errors
Additionally, unhandled exceptions in database-stored PL/SQL program units propagate back to client-side applications that call the containing program unit. In such an application, only the application program unit call 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 program units are propagated back to database applications, the database PL/SQL code should be modified to handle the exceptions. Your application can also trap for unhandled exceptions when calling database program units and handle such errors appropriately. For more information, see ``Handling Errors in Remote Procedures.''
If a portion of a distributed statement fails, for example, due to an integrity constraint violation, Oracle returns error number ORA-02055. Subsequent statements or procedure calls return error number ORA-02067 until a rollback or rollback to savepoint is issued.
You should design your application to check for any returned error messages that indicate that a portion of the distributed update has failed. If you detect a failure, you should rollback the entire transaction (or rollback to a savepoint) before allowing the application to proceed.
EXCEPTION WHEN ZERO_DIVIDE THEN /* ...handle the exception */
Notice that 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, one can be assigned using PRAGMA_EXCEPTION_INIT, as shown in the following 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 calling a remote procedure, exceptions are also handled by creating a local exception handler. The remote procedure must return an error number to the local, calling procedure, which then handles the exception as shown in the previous example. Because PL/SQL user-defined exceptions always return ORA-06510 to the local procedure, these exceptions cannot be handled. All other remote exceptions can be handled in the same manner as local exceptions.
SVRMGR> @proc1
and there are one or more errors in the code, you receive a notice such as
MGR-00072: Warning: Procedure PROC1 created with compilation errors
In this case, use the SHOW ERRORS command 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 the SQL*Plus User's Guide and Reference for complete information about the SHOW ERRORS command.
Attention: Before issuing the SHOW ERRORS command, use the SET CHARWIDTH command to get long lines on output. For example:
SET CHARWIDTH 132
is usually a good choice.
For example, assume you want to create a simple procedure that deletes records from the employee table using SQL*Plus:
CREATE PROCEDURE fire_emp(emp_id NUMBER) AS
BEGIN
DELETE FROM emp 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 issued and an error is returned, a SHOW ERRORS statement would return the following lines:
SHOW ERRORS; ERRORS FOR PROCEDURE FIRE_EMP: LINE/COL ERROR -------------- -------------------------------------------- 3/24 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 command.
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 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 the Oracle7 Server Reference manual for more information about these data dictionary views.
for more information about the DBMS_OUTPUT package. A more convenient way to debug, if your platform supports it, is to use the Oracle Procedure Builder, which is part of the Oracle Developer/2000 tool set. Procedure Builder lets you execute PL/SQL procedures and triggers in a controlled debugging environment, and you can set breakpoints, list the values of variables, and perform other debugging tasks. See the Oracle Procedure Builder Developer's Guide for more information.
.
. . . sal_raise(emp_id, 200); . . .
This line calls the SAL_RAISE procedure. EMP_ID is a variable within the context of the procedure. Note that recursive procedure calls are allowed within PL/SQL; that is, a procedure can call itself.
BEGIN
sal_raise(1043, 200);
END;Note: Interactive tools such as SQL*Plus require that you follow these lines with a slash (/) to execute the PL/SQL block.
An easier way to execute a block is to use the SQL*Plus command EXECUTE, which effectively wraps BEGIN and END statements around the code you enter. For example:
EXECUTE sal_raise(1043, 200);
Some interactive tools allow session variables to be created. For example, when using SQL*Plus, the following statement creates a session variable:
VARIABLE assigned_empno NUMBER
Once defined, any session variable can be used for the duration of the session. For example, you might execute a function and capture the return value using a session variable:
EXECUTE :assigned_empno := hire_emp('JSMITH', 'President', \
1032, SYSDATE, 5000, NULL, 10);
PRINT assigned_empno;
ASSIGNED_EMPNO
--------------
2893See the SQL*Plus User's Guide and Reference for SQL*Plus information. See your tools manual for information about performing similar operations using your development tool.
To execute a procedure within a PL/SQL block in an application, simply call the procedure. The following line within a PL/SQL block calls the FIRE_EMP procedure:
fire_emp(:empno);
In this case, :EMPNO is a host (bind) variable within the context of the application.
To execute a procedure within the code of a precompiler application, you must use the EXEC call interface. For example, the following statement calls the FIRE_EMP procedure in the code of a precompiler application:
EXEC SQL EXECUTE
BEGIN
fire_emp(:empno);
END;
END-EXEC;:EMPNO is a host (bind) variable.
For more information about calling PL/SQL procedures from within 3GL applications, see the following manuals:
.
EXECUTE jward.fire_emp (1043); EXECUTE jward.hire_fire.fire_emp (1043);
Attention: A stored subprogram or package executes in the privilege domain of the owner of the procedure. The owner must have been explicitly granted the necessary object privileges to all objects referenced within the body of the code.
sal_raise(7369, 500); sal_raise(sal_incr=>500, emp_id=>7369); sal_raise(7369, sal_incr=>500);
The first statement identifies the argument values by listing them in the order in which they appear in the procedure specification.
The second statement identifies the argument values by name and in an order different from that of the procedure specification. If you use argument names, you can list the arguments in any order.
The third statement identifies the argument values using a combination of these methods. If you use a combination of order and argument names, values identified in order must precede values identified by name.
If you have used the DEFAULT option to define default values for IN parameters to a subprogram (see the PL/SQL User's Guide and Reference), you can pass different numbers of actual parameters to the 1subprogram, accepting or overriding the default values as you please. If an actual value is not passed, the corresponding default value is used. If you want to assign a value to an argument that occurs after an omitted argument (for which the corresponding default is used), you must explicitly designate the name of the argument, as well as its value.
EXECUTE fire_emp@NY(1043);
For information on exception handling when calling remote procedures, see page 7 - 34.
CREATE PROCEDURE fire_emp(emp_id NUMBER) IS
BEGIN
DELETE FROM emp@sales WHERE empno = emp_id;
END;The list below explains how to properly call remote procedures, depending on the calling environment.
CREATE PROCEDURE local_procedure(arg1, arg2) AS BEGIN ... remote_procedure@dblink(arg1, arg2); ... END;
CREATE PROCEDURE local_procedure(arg1, arg2) AS BEGIN ... synonym(arg1, arg2); ... END;
Warning: Unlike stored procedures, which use compile-time binding, when referencing remote procedures, runtime binding is used. The user account to which you connect depends on the database link.
All calls to remotely stored procedures are assumed to perform updates; therefore, this type of referencing always requires two-phase commit of that transaction (even if the remote procedure is read-only). Furthermore, if a transaction that includes a remote procedure call is rolled back, the work done by the remote procedure is also rolled back. A procedure called remotely cannot execute a COMMIT, ROLLBACK, or SAVEPOINT statement.
A distributed update modifies data on two or more nodes. A distributed update is possible using a procedure that includes two or more remote updates that access data on different nodes. Statements in the construct are sent to the remote nodes and the execution of the construct succeeds or fails as a unit. If part of a distributed update fails and part succeeds, a rollback (of the entire transaction or to a savepoint) is required to proceed. Consider this when creating procedures that perform distributed updates.
Pay special attention when using a local procedure that calls a remote procedure. If a timestamp mismatch is found during execution of the local procedure, the remote procedure is not executed and the local procedure is invalidated.
For example, they can be called from the following:
Note: Unlike functions, which are called as part of an expression, procedures are called as statements. Therefore, PL/SQL procedures are not directly callable from SQL statements. However, functions called from a PL/SQL statement or referenced in a SQL expression can call a PL/SQL procedure.
[[schema.]package.]function_name[@dblink][(param_1...param_n)]
For example, to reference a function that you have created that is called MY_FUNC, in the MY_FUNCS_PKG package, in the SCOTT schema, and that takes two numeric parameters, you could call it as
SELECT scott.my_funcs_pkg.my_func(10,20) from dual
CREATE TABLE emp(new_sal NUMBER ...); CREATE FUNCTION new_sal RETURN NUMBER IS ...;
Then in the following two statements, the reference to NEW_SAL refers to the column EMP.NEW_SAL:
SELECT new_sal FROM emp; SELECT emp.new_sal FROM emp;
To access the function NEW_SAL, you would enter the following:
SELECT scott.new_sal FROM emp;
Example
For example, to call the TAX_RATE PL/SQL function from schema SCOTT, execute it against the SS_NO and SAL columns in TAX_TABLE, and place the results in the variable INCOME_TAX, specify the following:
SELECT scott.tax_rate (ss_no, sal) INTO income_tax FROM tax_table WHERE ss_no = tax_id;
Listed below are sample calls to PL/SQL functions that are allowed in SQL expressions.
circle_area(radius) payroll.tax_rate(empno) scott.payroll.tax_rate(dependents, empno)@ny
The argument's datatypes and the function's return type are limited to those types that are supported by SQL. For example, you cannot call a PL/SQL function that returns a PL/SQL BINARY_INTEGER from a SQL statement.
CREATE FUNCTION gross_pay
(emp_id IN NUMBER,
st_hrs IN NUMBER DEFAULT 40,
ot_hrs IN NUMBER DEFAULT 0) RETURN NUMBER AS
...
When calling gross_pay from a procedural statement, you can always accept the default value of st_hrs. That is because you can use named notation, which lets you skip parameters, as in
IF gross_pay(eenum,ot_hrs => otime) > pay_limit THEN ...
However, when calling gross_pay from a SQL expression, you cannot accept the default value of st_hrs unless you accept the default value of ot_hrs. That is because you cannot use named notation.
CREATE FUNCTION gross_pay
(emp_id IN NUMBER,
st_hrs IN NUMBER DEFAULT 40,
ot_hrs IN NUMBER DEFAULT 0) RETURN NUMBER AS
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;
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 (which is not allowed). Therefore, the following rules apply to stored functions called from SQL expressions:
The pragma tells the PL/SQL compiler to deny the packaged function read/write access to database tables, packaged variables, or both. If you try to compile a function body that violates the pragma, you get a compilation error.
To code the pragma RESTRICT_REFERENCES, you use the syntax
PRAGMA RESTRICT_REFERENCES (
function_name, WNDS [, WNPS] [, RNDS] [, RNPS]);
where:
WNDS
means "writes no database state" (does not modify database tables)
WNPS
means "writes no package state" (does not change the values of packaged variables)
RNDS
means "reads no database state" (does not query database tables)
RNPS
means "reads no package state" (does not reference the values of packaged variables)
You can pass the arguments in any order, but you must pass the argument WNDS. No argument implies another. For instance, RNPS does not imply WNPS.
In the example below, the function compound neither reads nor writes database or package state, so you can assert the maximum purity level. Always assert the highest purity level a function allows. That way, the PL/SQL compiler will never reject the function unnecessarily.
CREATE PACKAGE finance AS -- package specification
...
FUNCTION compound
(years IN NUMBER,
amount IN NUMBER,
rate IN NUMBER) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (compound, WNDS, WNPS, RNDS, RNPS);
END finance;
CREATE PACKAGE BODY finance AS --package body
...
FUNCTION compound
(years IN NUMBER,
amount IN NUMBER,
rate IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN amount * POWER((rate / 100) + 1, years);
END compound;
-- no pragma in package body
END finance;
Later, you might call compound from a PL/SQL block, as follows:
BEGIN
...
SELECT finance.compound(yrs,amt,rte) -- function call INTO interest FROM accounts WHERE acctno = acct_id;
CREATE PACKAGE loans AS
prime_rate REAL; -- public packaged variable
...
END loans;
CREATE PACKAGE BODY loans AS
...
BEGIN -- initialization part
SELECT prime INTO prime_rate FROM rates;
END loans;
The initialization code is run only once--the first time the package is referenced. If the code reads or writes database state or package state other than its own, it can cause side effects. Moreover, a stored function that references the package (and thereby runs the initialization code) can cause side effects indirectly. So, to call the function from SQL expressions, you must use the pragma RESTRICT_REFERENCES to assert or imply the purity level of the initialization code.
To assert the purity level of the initialization code, you use a variant of the pragma RESTRICT_REFERENCES, in which the function name is replaced by a package name. You code the pragma in the package specification, where it is visible to other users. That way, anyone referencing the package can see the restrictions and conform to them.
To code the variant pragma RESTRICT_REFERENCES, you use the syntax
PRAGMA RESTRICT_REFERENCES (
package_name, WNDS [, WNPS] [, RNDS] [, RNPS]);
where the arguments WNDS, WNPS, RNDS, and RNPS have the usual meaning.
In the example below, the initialization code reads database state and writes package state. However, you can assert WNPS because the code is writing the state of its own package, which is permitted. So, you assert WNDS, WNPS, RNPS--the highest purity level the function allows. (If the public variable prime_rate were in another package, you could not assert WNPS.)
CREATE PACKAGE loans AS
PRAGMA RESTRICT_REFERENCES (loans, WNDS, WNPS, RNPS);
prime_rate REAL;
...
END loans;
CREATE PACKAGE BODY loans AS
...
BEGIN
SELECT prime INTO prime_rate FROM rates;
END loans;
You can place the pragma anywhere in the package specification, but placing it at the top (where it stands out) is a good idea.
To imply the purity level of the initialization code, your package must have a RESTRICT_REFERENCES pragma for one of the functions it declares. From the pragma, Oracle can infer the purity level of the initialization code (because the code cannot break any rule enforced by a pragma). In the next example, the pragma for the function discount implies that the purity level of the initialization code is at least WNDS:
CREATE PACKAGE loans AS
...
FUNCTION discount (...) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (discount, WNDS);
END loans;
...
To draw an inference, Oracle can combine the assertions of all RESTRICT_REFERENCES pragmas. For example, the following pragmas (combined) imply that the purity level of the initialization code is at least WNDS, RNDS:
CREATE PACKAGE loans AS
...
FUNCTION discount (...) RETURN NUMBER;
FUNCTION credit_ok (...) RETURN CHAR;
PRAGMA RESTRICT_REFERENCES (discount, WNDS);
PRAGMA RESTRICT_REFERENCES (credit_ok, RNDS);
END loans;
...
This happens when a packaged function is purer than the package initialization code. Remember, the first time a package is referenced, its initialization code is run. If that reference is a function call, any additional side effects caused by the initialization code occur during the call. So, in effect, the initialization code lowers the purity level of the function.
To avoid this problem, move the package initialization code into a subprogram. That way, your application can run the code explicitly (rather than implicitly during package instantiation) without affecting your packaged functions.
A similar problem arises when a packaged function is purer than a subprogram it calls. This lowers the purity level of the function. Therefore, the RESTRICT_REFERENCES pragma for the function must specify the lower purity level. Otherwise, the PL/SQL compiler will reject the function. In the following example, the compiler rejects the function because its pragma asserts RNDS but the function calls a procedure that reads database state:
CREATE PACKAGE finance AS
...
FUNCTION compound (years IN NUMBER,
amount IN NUMBER) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (compound, WNDS, WNPS, RNDS, RNPS);
END finance;
CREATE PACKAGE BODY finance AS
...
FUNCTION compound (years IN NUMBER,
amount IN NUMBER) RETURN NUMBER IS
rate NUMBER;
PROCEDURE calc_loan_rate (loan_rate OUT NUMBER) IS
prime_rate REAL;
BEGIN
SELECT p_rate INTO prime_rate FROM rates;
...
END;
BEGIN
calc_loan_rate(rate);
RETURN amount * POWER((rate / 100) + 1, years);
END compound;
END finance;
However, a RESTRICT_REFERENCES pragma can apply to only one function declaration. So, a pragma that references the name of overloaded functions always applies to the nearest foregoing function declaration. In the following example, the pragma applies to the second declaration of valid:
CREATE PACKAGE tests AS
FUNCTION valid (x NUMBER) RETURN CHAR;
FUNCTION valid (x DATE) RETURN CHAR;
PRAGMA RESTRICT_REFERENCES (valid, WNDS);
...
These packages run as the invoking user rather than the package owner. The packaged procedures are callable through public synonyms of the same name.
| Package | Procedure(Arguments) | SQL Command Equivalent |
| DBMS_SESSION | close_database_link( dblink varchar2) | ALTER SESSION CLOSE DATABASE dblink |
| reset_package (see note 5) | This procedure reinitializes the state of all packages; there is no SQL equivalent | |
| set_label(lbl varchar2) (note 4) | ALTER SESSION SET LABEL text | |
| set_mls_label_format( fmt varchar2) (note 4) | ALTER SESSION SET MLS_LABEL_FORMAT = fmt | |
| set_nls(param varchar2, value varchar2) (notes 1,4) | ALTER SESSION SET nls_param = nls_param_values | |
| set_role(role_cmd varchar2) (notes 1, 6) | SET ROLE ... | |
| set_sql_trace(sql_trace boolean) | ALTER SESSION SET SQL_TRACE = [TRUE | FALSE] | |
| unique_session_id return varchar2 | This function returns a unique session ID; there is no SQL equivalent. | |
| is_role_enabled return boolean | This function is used to determine if a role is enabled; there is no SQL equivalent. | |
| set_close_cached_open_cursors( close_cursors boolean) | ALTER SESSION SET CLOSE_CACHED_OPEN_CURSORS | |
| free_unused_user_memory | This procedure lets you reclaim unused memory; there is no SQL equivalent. | |
| DBMS_DDL | alter_compile(type varchar2, schema varchar2, name varchar2) (notes 1, 2, 3, 4 | ALTER PROCEDURE proc COMPILE |
| ALTER FUNCTION func COMPILE | ||
| ALTER PACKAGE pack COMPILE | ||
| analyze_object( type varchar2, schema varchar2, name varchar2, method varchar2, estimate_rows number default null, estimate_percent number default null) | ANALYZE INDEX | |
| ANALYZE TABLE | ||
| ANALYZE CLUSTER | ||
|
DBMS_
TRANSACTION | advise_commit | ALTER SESSION ADVISE COMMIT |
| advise_rollback | ALTER SESSION ADVISE ROLLBACK | |
| advise_nothing | ALTER SESSION ADVISE NOTHING | |
| commit (notes 1,2,4) | COMMIT | |
| commit_comment(cmnt varchar2) (notes 1,2,4) | COMMIT COMMENT text | |
| commit_force(xid varchar2, scn varchar2 default null) (notes 1,2,3,4) | COMMIT FORCE text ... | |
| read_only (notes 1,3,4) | SET TRANSACTION READ ONLY | |
| read_write (notes 1,3,4) | SET TRANSACTION READ WRITE | |
| rollback (notes 1,2,4) | ROLLBACK | |
| rollback_force(xid varchar2) (notes 1,2,3,4) | ROLLBACK ... FORCE text ... | |
| rollback_savepoint( svpt varchar2) (notes 1,2,4) | ROLLBACK ... TO SAVEPOINT ... | |
| savepoint(savept varchar2) (notes 1,2,4) | SAVEPOINT savepoint | |
| use_rollback_segment( rb_name varchar2) (notes 1,2,4) | SET TRANSACTION USE ROLLBACK SEGMENT segment | |
| purge_mixed(xid in number) | See Oracle7 Server Distributed Systems, Volume I for more information | |
| begin_discrete_transaction (notes 1,3,4,5) | See the Oracle7 Server Tuning manual for more information | |
| local_transaction_id( create_transaction BOOLEAN default FALSE) return VARCHAR2 | See Oracle7 Server Distributed Systems, Volume I for more information | |
| step_id return number | See Oracle7 Server Distributed Systems, Volume I for more information | |
| DBMS_UTILITY | compile_schema(schema varchar2) (notes 1,2,3,4) | This procedure is equivalent to calling alter_compile on all procedures, functions, and packages accessible by you. Compilation is completed in dependency order. |
| analyze_schema( schema varchar2, method varchar2, estimate_rows number default null, estimate_percent number default null) | This procedure is equivalent to calling analyze_object on all objects in the given schema. | |
| format_error_stack return varchar2 | This function formats the error stack into a variable. | |
| format_call_stack return varchar2 | This function formats the current call stack into a variable. | |
| is_parallel_server return boolean | This function returns TRUE when running in Parallel Server mode. | |
| get_time return number | This function returns the time in hundredths of a second. | |
| name_resolve( name in varchar2, context in number, schema out varchar2, part1 out varchar2, part2 out varchar2, dblink out varchar2, part1_type out number, object_number out number) | See Oracle7 Server Distributed Systems, Volume I for more information | |
For more details on each SQL command equivalent, refer to the Oracle7 Server SQL Reference manual. The COMMIT, ROLLBACK, ROLLBACK... TO SAVEPOINT, and SAVEPOINT procedures are directly supported by PL/SQL; they are included in the package for completeness.
| Package Name | Description | Cross-reference |
| DBMS_ALERT | Supports asynchronous notification of database events. | Chapter 12 |
| DBMS_DESCRIBE | Lets you describe the arguments of a stored procedure. | Page 7 - 57 |
| DBMS_JOB | Lets you schedule administrative procedures that you want performed at periodic intervals. | Oracle7 Server Administrator's Guide |
| DBMS_LOCK | Lets you use the Oracle Lock Management services for your applications. | Page 3 - 19 |
| DBMS_OUTPUT | Lets you output messages from triggers, procedures, and packages. | Page 8 - 21 |
| DBMS_PIPE | Allows sessions in the same instance to communicate with each other. | Chapter 8 |
| DBMS_SHARED_POOL | Lets you keep objects in shared memory, so that they will not be aged out with the normal LRU mechanism. | Oracle7 Server Tuning |
| DBMS_APPLICATION_ INFO | Lets you register an application name with the database for auditing or performance tracking purposes. | Oracle7 Server Tuning |
| DBMS_SYSTEM | Provides system-level utilities, such as letting you enable SQL trace for a session. | Oracle7 Server Tuning |
| DBMS_SPACE | Provides segment space information not available through standard views. | Oracle7 Server Administrator's Guide |
| DBMS_SQL | Lets you write stored procedures and anonymous PL/SQL blocks using dynamic SQL; lets you parse any DML or DDL statement. | Chapter 10 |
| DBMS_REFRESH | Lets you create groups of snapshots that can be refreshed together to a transactionally consistent point in time. Use of this feature requires the distributed option. | Oracle7 Server Distributed Systems, Volume II |
| DBMS_SNAPSHOT | Lets you refresh one or more snapshots that are not part of the same refresh group, purge snapshot log. Use of this feature requires the distributed option. | Oracle7 Server Distributed Systems, Volume II |
| DBMS_DEFER, DMBS_DEFER_SYS, DBMS_DEFER_QUERY | Lets you build and administer deferred remote procedure calls. Use of this feature requires the replication option. | Oracle7 Server Distributed Systems, Volume II |
| DBMS_REPCAT | Lets you use Oracle's symmetric replication facility. Use of this feature requires the replication option. | Oracle7 Server Distributed Systems, Volume II |
| DBMS_REPCAT_AUTH, DBMS_REPCAT_ADMIN | Lets you create users with the privileges needed by the symmetric replication facility. Use of this feature requires the replication option. | Oracle7 Server Distributed Systems, Volume II |
This package provides the same functionality as the Oracle Call Interface ODESSP call. The procedure DESCRIBE_PROCEDURE in this package accepts the name of a stored procedure, and a description of the procedure and each of its parameters. For more information on ODESSP, see the Programmer's Guide to the Oracle Call Interface.
TYPE VARCHAR2_TABLE IS TABLE OF VARCHAR2(30)
INDEX BY BINARY_INTEGER;
TYPE NUMBER_TABLE IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
-20000: ORU 10035: cannot describe a package ('X') only a
procedure within a package
-20001: ORU-10032: procedure 'X' within package 'Y' does not
exist
-20002: ORU-10033 object 'X' is remote, cannot describe; expanded
name 'Y'
-20003: ORU-10036: object 'X' is invalid and cannot be described
-20004: syntax error attempting to parse 'X'
PROCEDURE DESCRIBE_PROCEDURE(
object_name IN VARCHAR2,
reserved1 IN VARCHAR2,
reserved2 IN VARCHAR2,
overload OUT NUMBER_TABLE,
position OUT NUMBER_TABLE,
level OUT NUMBER_TABLE,
argument_name OUT VARCHAR2_TABLE,
datatype OUT NUMBER_TABLE,
default_value OUT NUMBER_TABLE,
in_out OUT NUMBER_TABLE,
length OUT NUMBER_TABLE,
precision OUT NUMBER_TABLE,
scale OUT NUMBER_TABLE,
radix OUT NUMBER_TABLE
spare OUT NUMBER_TABLE);
| Parameter | Mode | Description |
| object_name | IN | The name of the procedure being described. The syntax for this parameter follows the rules used for identifiers in SQL. The name can be a synonym. This parameter is required and may not be null. The total length of the name cannot exceed 197 bytes. An incorrectly specified OBJECT_NAME can result in one of the following exceptions: ORA-20000 - A package was specified. You can only specify a stored procedure, stored function, packaged procedure, or packaged function. ORA-20001 - The procedure or function that you specified does not exist within the given package. ORA-20002 - The object that you specified is a remote object. This procedure cannot currently describe remote objects. ORA-20003 - The object that you specified is invalid and cannot be described. ORA-20004 - The object was specified with a syntax error. |
| reserved1 reserved2 | IN | Reserved for future use. Must be set to null or the empty string. |
| overload | OUT | A unique number assigned to the procedure's signature. If a procedure is overloaded, this field holds a different value for each version of the procedure. |
| position | OUT | Position of the argument in the parameter list. Position 0 returns the values for the return type of a function. |
| level | OUT | If the argument is a composite type, such as record, this parameter returns the level of the datatype. See the Programmer's Guide to the Oracle Call Interface write-up of the ODESSP call for an example of its use. |
| argument_name | OUT | The name of the argument associated with the procedure that you are describing. |
| datatype | OUT | The Oracle datatype of the argument being described. The datatypes and their numeric type codes are: 0 placeholder for procedures with no arguments 1 VARCHAR, VARCHAR, STRING 2 NUMBER, INTEGER, SMALLINT, REAL, FLOAT, DECIMAL 3 BINARY_INTEGER, PLS_INTEGER, POSITIVE, NATURAL 8 LONG 11 ROWID 12 DATE 23 RAW 24 LONG RAW 96 CHAR (ANSI FIXED CHAR), CHARACTER 106 MLS LABEL 250 PL/SQL RECORD 251 PL/SQL TABLE 252 PL/SQL BOOLEAN |
| default_value | OUT | 1 if the argument being described has a default value; otherwise, the value is 0. |
| in_out | OUT | Describes the mode of the parameter: 0 IN 1 OUT 2 IN OUT |
| length | OUT | The data length, in bytes, of the argument being described. |
| precision | OUT | If the argument being described is of datatype 2 (NUMBER), this parameter is the precision of that number. |
| scale | OUT | If the argument being described is of datatype 2 (NUMBER, etc.), this parameter is the scale of that number. |
| radix | OUT | If the argument being described is of datatype 2 (NUMBER, etc.), this parameter is the radix of that number. |
| spare | OUT | Reserved for future functionality. |
For example, consider a client that provides an OBJECT _NAME of SCOTT.ACCOUNT_UPDATE where ACCOUNT_UPDATE is an overloaded function with specification:
table account (account_no number, person_id number,
balance number(7,2))
table person (person_id number(4), person_nm varchar2(10))
function ACCOUNT_UPDATE (account_no number,
person person%rowtype,
amounts dbms_describe.number_table,
trans_date date)
return accounts.balance%type;
function ACCOUNT_UPDATE (account_no number,
person person%rowtype,
amounts dbms_describe.number_table,
trans_no number)
return accounts.balance%type;The describe of this procedure might look similar to the output shown below.
overload position argument level datatype length prec scale rad
-----------------------------------------------------------------
1 0 0 2 22 7 2 10
1 1 ACCOUNT 0 2 0 0 0 0
1 2 PERSON 0 250 0 0 0 0
1 1 PERSON_ID 1 2 22 4 0 10
1 2 PERSON_NM 1 1 10 0 0 0
1 3 AMOUNTS 0 251 0 0 0 0
1 1 1 2 22 0 0 0
1 4 TRANS_DATE 0 12 0 0 0 0
2 0 0 2 22 7 2 10
2 1 ACCOUNT_NO 0 2 22 0 0 0
2 2 PERSON 0 2 22 4 0 10
2 3 AMOUNTS 0 251 22 4 0 10
2 1 1 2 0 0 0 0
2 4 TRANS_NO 0 2 0 0 0 0The following PL/SQL procedure has as its parameters all of the PL/SQL datatypes:
CREATE OR REPLACE PROCEDURE p1 (
pvc2 IN VARCHAR2,
pvc OUT VARCHAR,
pstr IN OUT STRING,
plong IN LONG,
prowid IN ROWID,
pchara IN CHARACTER,
pchar IN CHAR,
praw IN RAW,
plraw IN LONG RAW,
pbinint IN BINARY_INTEGER,
pplsint IN PLS_INTEGER,
pbool IN BOOLEAN,
pnat IN NATURAL,
ppos IN POSITIVE,
pposn IN POSITIVEN,
pnatn IN NATURALN,
pnum IN NUMBER,
pintgr IN INTEGER,
pint IN INT,
psmall IN SMALLINT,
pdec IN DECIMAL,
preal IN REAL,
pfloat IN FLOAT,
pnumer IN NUMERIC,
pdp IN DOUBLE PRECISION,
pdate IN DATE,
pmls IN MLSLABEL) AS
BEGIN
NULL;
END;If you describe this procedure using the package below:
CREATE OR REPLACE PACKAGE describe_it AS
PROCEDURE desc_proc (name VARCHAR2);
END describe_it;
CREATE OR REPLACE PACKAGE BODY describe_it AS
PROCEDURE prt_value(val VARCHAR2, isize INTEGER) IS
n INTEGER;
BEGIN
n := isize - LENGTHB(val);
IF n < 0 THEN
n := 0;
END IF;
DBMS_OUTPUT.PUT(val);
FOR i in 1..n LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;
END prt_value;
PROCEDURE desc_proc (name VARCHAR2) IS
overload DBMS_DESCRIBE.NUMBER_TABLE;
position DBMS_DESCRIBE.NUMBER_TABLE;
c_level DBMS_DESCRIBE.NUMBER_TABLE;
arg_name DBMS_DESCRIBE.VARCHAR2_TABLE;
dty DBMS_DESCRIBE.NUMBER_TABLE;
def_val DBMS_DESCRIBE.NUMBER_TABLE;
p_mode DBMS_DESCRIBE.NUMBER_TABLE;
length DBMS_DESCRIBE.NUMBER_TABLE;
precision DBMS_DESCRIBE.NUMBER_TABLE;
scale DBMS_DESCRIBE.NUMBER_TABLE;
radix DBMS_DESCRIBE.NUMBER_TABLE;
spare DBMS_DESCRIBE.NUMBER_TABLE;
idx INTEGER := 0;
BEGIN
DBMS_DESCRIBE.DESCRIBE_PROCEDURE(
name,
null,
null,
overload,
position,
c_level,
arg_name,
dty,
def_val,
p_mode,
length,
precision,
scale,
radix,
spare);
DBMS_OUTPUT.PUT_LINE('Position Name DTY Mode');
LOOP
idx := idx + 1;
prt_value(TO_CHAR(position(idx)), 12);
prt_value(arg_name(idx), 12);
prt_value(TO_CHAR(dty(idx)), 5);
prt_value(TO_CHAR(p_mode(idx)), 5);
DBMS_OUTPUT.NEW_LINE;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.NEW_LINE;
END desc_proc;
END describe_it;
then the results, as shown below, list all the numeric codes for the PL/SQL datatypes:
Position Name Datatype_Code Mode 1 PVC2 1 0 2 PVC 1 1 3 PSTR 1 2 4 PLONG 8 0 5 PROWID 11 0 6 PCHARA 96 0 7 PCHAR 96 0 8 PRAW 23 0 9 PLRAW 24 0 10 PBININT 3 0 11 PPLSINT 3 0 12 PBOOL 252 0 13 PNAT 3 0 14 PPOS 3 0 15 PPOSN 3 0 16 PNATN 3 0 17 PNUM 2 0 18 PINTGR 2 0 19 PINT 2 0 20 PSMALL 2 0 21 PDEC 2 0 22 PREAL 2 0 23 PFLOAT 2 0 24 PNUMER 2 0 25 PDP 2 0 26 PDATE 12 0 27 PMLS 106 0
The following statements are used in Examples 1 through 3:
CREATE PROCEDURE fire_emp(emp_id NUMBER) AS
BEGIN
DELETE FROM em WHERE empno = emp_id;
END;
/
CREATE PROCEDURE hire_emp (name VARCHAR2, job VARCHAR2,
mgr NUMBER, hiredate DATE, sal NUMBER,
comm NUMBER, deptno NUMBER)
IS
BEGIN
INSERT INTO emp VALUES (emp_sequence.NEXTVAL, name,
job, mgr, hiredate, sal, comm, deptno);
END;
/The first CREATE PROCEDURE statement has an error in the DELETE statement. (The 'p' is absent from 'emp'.)
Example 1 Listing Compilation Errors for Objects
The following query returns all the errors for the objects in the associated schema:
SELECT name, type, line, position, text
FROM user_errors;The following results are returned:
NAME TYPE LIN POS TEXT
-------- ---- --- --- -------------------------------------
FIRE_EMP PROC 3 15 PL/SQL-00201: identifier 'EM' must be
declared
FIRE_EMP PROC 3 3 PL/SQL: SQL Statement ignoredExample 2 Listing Source Code for a Procedure
The following query returns the source code for the HIRE_EMP procedure created in the example statement at the beginning of this section:
SELECT line, text FROM user_source
WHERE name = 'HIRE_EMP';
The following results are returned:
LINE TEXT
------ -----------------------------------------------------
1 PROCEDURE hire_emp (name VARCHAR2, job VARCHAR2,
2 mgr NUMBER, hiredate DATE, sal NUMBER,
3 comm NUMBER, deptno NUMBER)
4 IS
5 BEGIN
6 INSERT INTO emp VALUES (emp_seq.NEXTVAL, name,
7 job, mgr, hiredate, sal, comm, deptno);
8 END;Example 3 Listing Size Information for a Procedure
The following query returns information about the amount of space in the SYSTEM tablespace that is required to store the HIRE_EMP procedure:
SELECT name, source_size + parsed_size + code_size +
error_size "TOTAL SIZE"
FROM user_object_size
WHERE name = 'HIRE_EMP';The following results are returned:
NAME TOTAL SIZE ------------------------------ ---------- HIRE_EMP 3897
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |