|Oracle® Database PL/SQL Language Reference
11g Release 2 (11.2)
Part Number E17126-03
A standalone stored procedure is a procedure (a subprogram that performs a specific action) that is stored in the database.
Note:A standalone stored procedure that you create with the
PROCEDUREstatement differs from a procedure that you declare and define in a PL/SQL block or package. For information about the latter, see "Procedure Declaration and Definition".
A call specification declares a Java method or a third-generation language (3GL) subprogram so that it can be called from PL/SQL. You can also use the SQL
CALL statement to invoke such a method or subprogram. The call specification tells the database which Java method, or which named procedure in which shared library, to invoke when an invocation is made. It also tells the database what type conversions to make for the arguments and return value.
To create or replace a standalone stored procedure in your own schema, you must have the
PROCEDURE system privilege. To create or replace a standalone stored procedure in another user's schema, you must have the
PROCEDURE system privilege.
To invoke a call specification, you may need additional privileges, for example, the
EXECUTE object privilege on the C library for a C call specification.
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.
See Also:For more information about such prerequisites:
Re-creates the procedure if it exists, and recompiles it.
Users who were granted privileges on the procedure before it was redefined can still access the procedure without being regranted the privileges.
If any function-based indexes depend on the procedure, then the database marks the indexes
The name of the schema containing the procedure. The default is your own schema.
The name of the procedure to be created.
AUTHID property of the procedure. For information about the
AUTHID property, see "Invoker's Rights and Definer's Rights (AUTHID Property)".
The required executable part of the procedure and, optionally, the exception-handling part of the procedure.
The optional declarative part of the procedure. Declarations are local to the procedure, can be referenced in
body, and cease to exist when the procedure completes execution.
Maps a C procedure or Java method name, parameter types, and return type to their SQL counterparts. In
string identifies the Java implementation of the method.
Oracle Database Java Developer's Guide to learn how to write Java call specifications
Oracle Database Advanced Application Developer's Guide to learn how to write C call specifications
Deprecated way of declaring a C procedure, supported only for backward compatibility. Oracle recommends that you use the
Creating a Procedure: Example This statement creates the procedure
remove_emp in the schema
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 invoke the procedure, you must specify the
employee_id of the employee to be removed.
The procedure uses a
DELETE statement to remove from the
employees table the row of
See Also:"Creating a Package Body: Example" to see how to incorporate this procedure into a package
In this example, external procedure
c_find_root expects a pointer as a parameter. Procedure
find_root passes the parameter by reference using the
CREATE PROCEDURE find_root ( x IN REAL ) IS LANGUAGE C NAME c_find_root LIBRARY c_utils PARAMETERS ( x BY REFERENCE );
In this chapter:
In other chapters:
Oracle Database SQL Language Reference for information about the
Oracle Database Advanced Application Developer's Guide for more information about call specifications