Functions, procedures, and packages will be stored in subprograms. These subprograms can be stored inside the database by a name and can be called by different client applications. Oracle also provides the ability to provide local subprograms. These procedures and functions are local to another stored procedure or function or an anonymous block. A single call to a modular program will execute the same logic consistently at all the places. Whenever the code is unique to a program, it might create a local modular unit to be used within the program only. And, it is required to learn how to create such programs to maintain the existing piece of code that might have local subprograms. When local subprograms are defined, the following functions will be defined: The stored subprograms can possibly be pinned in the shared pool and cached if they are used most often and improve the performance. However, the local subprogram cannot be pinned. These PLSQL Local Subprograms will be defined in the declaration section or are declared in the end, else oracle will raise an exception.

In stored procedure and stored functions,local procedure or local functions can be defined. By defining a local procedure within a stored procedure consisting of same procedure update_dept and a local variable L_dept_id as departments,  dept_id%TYPE will be declared first followed by local subprogram message. And, these PLSQL Local Subprograms messages will take two parameters, the location of type VARCHAR2 to indicate the location when it is called in the code and the p_msg parameter in which the message will be passed. In this type, create keyword will not be available and declared as PROCEDURE display_message. The display_message is another identifier in the code, which has the visibility from the end of the procedure. The execution section will execute all these lines in a single call to the display_message.

The below is an example of PLSQL Local Subprograms: [c]SQL> create or replace procedure update_dept(p_emp_id employee.emp_id%type) _dept_id departments.dept_id%type:=2; 2 procedure display_message(p_location IN VARCHAR2,p_msg VARCHAR2) IS 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE('***'||P_Location||'***'); 5 DBMS_OUTPUT.PUT_LINE(P_MSG); 6 END display_message; 7 BEGIN 8 display_message('BEFORE UPDATING','INPUT EMPLOYEE ID:'||p_emp_id); 9 UPDATE employee 10 SET emp_dept_id=l_dept_id 11 WHERE emp_id=p_emp_id; 12 display_message('After Updating','Rows Updated:'||SQL%ROWCOUNT); 13 EXCEPTION 14 WHEN OTHERS THEN 15 DBMS_OUTPUT.PUT_LINE(SQLERRM); 16 DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); 17 RAISE; 18 END update_dept; 19 / Procedure successfully compiled execute update_dept(10); ***Before Updating*** Input Employee ID:10 ***After Updating*** Rows Updated:1[/c] The above example will update the department table such as updating the dept id (10) to dept id( 1).

The tables, functions and other objects get executed at runtime and these can be defined in our own schema and access can be granted to the objects with the same name in another schema. The AUTHID clause is used for defining a stored subprogram, in which the schema context and the privilege is set and run under the definer of the procedure or the executor of the procedure. These are very important concepts and one can easily understand the execution flow that consists the code in which it effects the result. Name resolution of an object is very important to understand when multiple objects have the same name. Namespaces are used to define objects of the same name and parameters as long as they are in different namespaces and do not conflict with each other. The objects with the same name in another schema can be accessed by prefixing it with a schema name and the dot indicates the namespace in which the object is located in the current schema. For instance, consider that we have the procedure update_emp in our schema and a procedure with the same name(update_emp) exists in a package hr_mgnt. When logged in as a demo user and when exec update_emp is issued, the standalone procedure update_emp is executed/ resolved. But, if you desire to access the packaged procedure, then qualify it as exec hr_mgmt. update_emp to be resolved to the package procedure. Hr_mgnt acts as a namespace for this procedure and contains the same package procedure in multiple schemas. One can access each one by prefixing the right package name followed by a dot. We have another schema and creating this procedure using the statement grant execute on update_emp to test and the session is logged in as user test. Update_emp to be able to access this standalone procedure. Synonyms help us assign a name of our choice to any object containing the alias names. We create public synonym as create public synonym privilege and need to log in as a DBA user to run. The order of resolution is always from the lowest level to highest level and executes the resolved conditions. Now, the text session issues exec update_emp. Since there is a local update_emp procedure, it will resolve and then follows the next level of public synonym update_emp. The schema name and the package name serve as namespaces for us to define and invoke multiple objects with the same name.

• PLSQL Local Subprograms- Is an encapsulation of a computation in some form.
• Defining a local subprogram - Is a stored procedure and stored function.
• Roles and privileges - Grants privileges to the user.