Stored PL/SQL subprograms can be invoked from many different environments. For example:
Interactively, using an Oracle Database tool
From the body of another subprogram
From within an application (such as a SQL*Forms or a precompiler)
From the body of a trigger
Stored PL/SQL functions (but not procedures) can also be invoked from within SQL statements. For details, see Invoking Stored PL/SQL Functions from SQL Statements.
Topics:
|
See Also:
|
You do not need privileges to invoke:
Standalone subprograms that you own
Subprograms in packages that you own
Public standalone subprograms
Subprograms in public packages
To invoke a standalone or packaged subprogram owned by another user:
You must have the EXECUTE privilege for the standalone subprogram or for the package containing the subprogram, or you must have the EXECUTE ANY PROCEDURE system privilege.
If you are executing a remote subprogram, then you must be granted the EXECUTE privilege or EXECUTE ANY PROCEDURE system privilege directly, not through a role.
You must include the name of the owner in the invocation. For example:
EXECUTE jdoe.Fire_emp (1043); EXECUTE jdoe.Hire_fire.Fire_emp (1043);
If the subprogram is a definer's-rights (DR) subprogram, then it runs with the privileges of the owner. The owner must have all the necessary object privileges for any referenced objects.
If the subprogram is an invoker's-rights (IR) subprogram, then it runs with your privileges. You must have all the necessary object privileges for any referenced objects; that is, all objects accessed by the subprogram through external references that are resolved in your schema. You can hold these privileges either directly or through a role. Roles are enabled unless an IR subprogram is invoked directly or indirectly by a DR subprogram.
You can invoke a subprogram interactively from an Oracle Database tool, such as SQL*Plus. Example: Invoking a Subprogram Interactively with SQL*Plus uses SQL*Plus to create a procedure and then invokes it in two different ways.
Invoking a Subprogram Interactively with SQL*Plus
CREATE OR REPLACE PROCEDURE salary_raise ( employee EMPLOYEES.EMPLOYEE_ID%TYPE, increase EMPLOYEES.SALARY%TYPE ) IS BEGIN UPDATE EMPLOYEES SET SALARY = SALARY + increase WHERE EMPLOYEE_ID = employee; END; /
Invoke procedure from within PL/SQL block:
BEGIN
salary_raise(205, 200);
END;
/
Result:
PL/SQL procedure successfully completed.
Invoke procedure with EXECUTE statement:
EXECUTE salary_raise(205, 200);
Result:
PL/SQL procedure successfully completed.
Some interactive tools allow you to create session variables, which you can use for the duration of the session. Using SQL*Plus, Example: Creating and Using a Session Variable with SQL*Plus creates, uses, and prints a session variable.
Creating and Using a Session Variable with SQL*Plus
-- Create function for later use: CREATE OR REPLACE FUNCTION get_job_id ( emp_id EMPLOYEES.EMPLOYEE_ID%TYPE ) RETURN EMPLOYEES.JOB_ID%TYPE IS job_id EMPLOYEES.JOB_ID%TYPE; BEGIN SELECT JOB_ID INTO job_id FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; RETURN job_id; END; / -- Create session variable: VARIABLE job VARCHAR2(10); -- Execute function and store returned value in session variable: EXECUTE :job := get_job_id(204); PL/SQL procedure successfully completed.
SQL*Plus command:
PRINT job;
Result:
JOB -------------------------------- PR_REP
A subprogram or a trigger can invoke another stored subprogram. In Example: Invoking a Subprogram from Within Another Subprogram, the procedure print_mgr_name invokes the procedure print_emp_name.
Recursive subprogram invocations are allowed (that is, a subprogram can invoke itself).
Invoking a Subprogram from Within Another Subprogram
-- Create procedure that takes employee's ID and prints employee's name: CREATE OR REPLACE PROCEDURE print_emp_name ( emp_id EMPLOYEES.EMPLOYEE_ID%TYPE ) IS fname EMPLOYEES.FIRST_NAME%TYPE; lname EMPLOYEES.LAST_NAME%TYPE; BEGIN SELECT FIRST_NAME, LAST_NAME INTO fname, lname FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; DBMS_OUTPUT.PUT_LINE ( 'Employee #' || emp_id || ': ' || fname || ' ' || lname ); END; / -- Create procedure that takes employee's ID and prints manager's name: CREATE OR REPLACE PROCEDURE print_mgr_name ( emp_id EMPLOYEES.EMPLOYEE_ID%TYPE ) IS mgr_id EMPLOYEES.MANAGER_ID%TYPE; BEGIN SELECT MANAGER_ID INTO mgr_id FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; DBMS_OUTPUT.PUT_LINE ( 'Manager of employee #' || emp_id || ' is: ' ); print_emp_name(mgr_id); END; /
Invoke procedures:
BEGIN print_emp_name(200); print_mgr_name(200); END; /
Result:
Employee #200: Jennifer Whalen Manager of employee #200 is: Employee #101: Neena Kochhar
A 3GL database application, such as a precompiler or an OCI application, can invoke a subprogram from within its own code.
Assume that the procedure Fire_emp1 was created as follows:
CREATE OR REPLACE PROCEDURE fire_emp1 (Emp_id NUMBER) AS
BEGIN
DELETE FROM Emp_tab WHERE Empno = Emp_id;
END;
To run a subprogram within the code of a precompiler application, you must use the EXEC call interface. For example, this statement invokes the Fire_emp procedure in the code of a precompiler application:
EXEC SQL EXECUTE
BEGIN
Fire_emp1(:Empnum);
END;
END-EXEC;