|Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-02
PROCEDURE statement to create a standalone stored procedure or a call specification.
A procedure is a group of PL/SQL statements that you can call by name. A call specification ("call spec") declares a Java method or a third-generation language (3GL) routine so that it can be called from SQL and PL/SQL. The call spec tells Oracle which Java method to invoke when a call is made. It also tells Oracle what type conversions to make for the arguments and return value.
Stored procedures offer advantages in the areas of development, integrity, security, performance, and memory allocation.
Before creating a procedure, the user
SYS must run a SQL script commonly called
DBMSSTDX.SQL. The exact name and location of this script depends on your operating system.
To create a procedure in your own schema, you must have the
PROCEDURE system privilege. To create a procedure in another user's schema, you must have
PROCEDURE system privilege. To replace a procedure in another schema, you must have the
PROCEDURE system privilege.
To invoke a call spec, you may need additional privileges (for example,
EXECUTE privileges on the C library for a C call spec).
To embed a
PROCEDURE statement inside an Oracle precompiler program, you must terminate the statement with the keyword
END-EXEC followed by the embedded SQL statement terminator for the specific language.
REPLACE to re-create the procedure if it already exists. Use this clause to change the definition of an existing procedure without dropping, re-creating, and regranting object privileges previously granted on it. If you redefine a procedure, Oracle recompiles it.
Users who had previously been granted privileges on a redefined procedure can still access the procedure without being regranted the privileges.
If any function-based indexes depend on the package, Oracle marks the indexes
ALTER PROCEDURE for information on recompiling procedures
Specify the schema to contain the procedure. If you omit
schema, Oracle creates the procedure in your current schema.
Specify the name of the procedure to be created.
If creating the procedure results in compilation errors, Oracle returns an error. You can see the associated compiler error messages with the SQL*Plus command
Specify the name of an argument to the procedure. If the procedure does not accept arguments, you can omit the parentheses following the procedure name.
IN to indicate that you must specify a value for the argument when calling the procedure.
OUT to indicate that the procedure passes a value for this argument back to its calling environment after execution.
OUT to indicate that you must specify a value for the argument when calling the procedure and that the procedure passes a value back to its calling environment after execution.
If you omit
OUT, the argument defaults to
NOCOPY to instruct Oracle to pass this argument as fast as possible. This clause can significantly enhance performance when passing a large value like a record, an index-by table, or a varray to an
OUT parameter. (
IN parameter values are always passed
NOCOPY, assignments made to a package variable may show immediately in this parameter (or assignments made to this parameter may show immediately in a package variable) if the package variable is passed as the actual assignment corresponding to this parameter.
These effects may or may not occur on any particular call. You should use
NOCOPY only when these effects would not matter.
Specify the datatype of the argument. An argument can have any datatype supported by PL/SQL.
Datatypes cannot specify length, precision, or scale. For example,
VARCHAR2(10) is not valid, but
VARCHAR2 is valid. Oracle derives the length, precision, and scale of an argument from the environment from which the procedure is called.
invoker_rights_clause lets you specify whether the procedure executes with the privileges and in the schema of the user who owns it or with the privileges and in the schema of
This clause also determines how Oracle resolves external names in queries, DML operations, and dynamic SQL statements in the procedure.
CURRENT_USER to indicate that the procedure executes with the privileges of
CURRENT_USER. This clause creates an invoker-rights procedure.
This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of
CURRENT_USER. External names in all other statements resolve in the schema in which the procedure resides.
DEFINER to indicate that the procedure executes with the privileges of the owner of the schema in which the procedure resides, and that external names resolve in the schema where the procedure resides. This is the default and creates a definer-rights procedure.
Declare the procedure in a PL/SQL subprogram body.
Oracle9i Application Developer's Guide - Fundamentals for more information on PL/SQL subprograms
call_spec to map a Java or C method name, parameter types, and return type to their SQL counterparts.
string' identifies the Java implementation of the method.
EXTERNAL clause is an alternative way of declaring a C method. This clause has been deprecated and is supported for backward compatibility only. Oracle Corporation recommends that you use the
The following statement creates the procedure
remove_emp in the schema
hr (PL/SQL is shown in italics):
CREATE PROCEDURE remove_emp (employee_id NUMBER) AS tot_emps NUMBER; BEGIN DELETE FROM employees WHERE employees.employee_id = remove_emp.employee_id; tot_emps := tot_emps - 1; END; /
remove_emp procedure removes a specified employee. When you call the procedure, you must specify the
employee_id of the employee to be removed. The argument's datatype is
The procedure uses a
DELETE statement to remove from the
employees table the row of
"Creating a Package Body: Example" to see how to incorporate this procedure into a package
In the following example, external procedure
c_find_root expects a pointer as a parameter. Procedure
find_root passes the parameter by reference using the
REFERENCE phrase (PL/SQL is shown in italics):