Oracle Functions and Procedures

Encapsulation and modularity are the few of the generic and recommended language features, which enhance efficiency and scalability of the language in the application. Oracle database language implements these features through stored subprograms. These subprograms (or subroutines) are the program units which hold a business or computational component and can be invoked at any junction in the application. This promotes code reusability and logical sampling of code.

In Oracle, subprograms can be of two types; namely, Procedures and Functions. These are named PL/SQL blocks, which physically occupy disk space. They can be called from other program units, packages and anonymous PL/SQL blocks in database. These subprograms can also be declared locally to a PL/SQL block. In such cases, the subprogram carries the scope of its parent block only.

In this tutorial, we shall study the architecture, usage and benefits of Oracle subprograms in schema and real time applications.

Subprograms

As discussed above, subprograms are the named PL/SQL blocks, whose definition consists of Specification and body. The ‘specification’ or header is the mandatory part which contains subprogram info like name, prototype and parameter list. And the ‘body’ is optional part that contains the variable declarations and programmable logic.

Subprograms can accept list of parameters, which can be used within its body. Parameters can be passed in two modes i.e. Pass by Value and Pass by Reference.

Oracle identifies two types of subprograms in a schema, Procedures and Functions.

Procedures and functions have the same structure and physical architecture. But they both achieve different motives. While procedures are meant for the outlining a process or functional logic in the application, functions are meant for computational tasks. Besides this, they have few usage differences, which we shall discuss later in this tutorial.

Properties of subprograms

Before going in details of each subprogram, we shall study the similar properties of schema subprograms. Both procedures and functions follow the below listed features.

Subprogram Namespace

Namespace is a category of the schema objects, which uniquely identifies their name. The schema objects which fall in a category must have a unique name. Procedures and functions share their namespace with tables, views, materialized views, sequences, packages, and object types. This implies that a procedure and a table cannot have same name.

Example [1]: A function F_GET_ID is available as standalone function in the schema. User attempts to create a table with the same name. Oracle identifies the violation and prevents such action by raising ORA-0955 error.

Other namespace available is the one which can hold unique names for Indexes, triggers, constraints, db links, and clusters.

Storing subprogram metadata

Oracle schema contains multiple dictionary views to hold the metadata of all the objects created within the schema. The metadata of the Oracle subprograms is contained with two views i.e. USER_OBJECTS and USER_PROCEDURES

USER_OBJECTS holds metadata of all the existing schema objects, while USER_PROCEDURES holds the metadata for subprograms i.e. procedures and functions. Refer the screen dumps.

A procedure P_UPD_SALARY is queried in USER_PROCEDURES and USER_OBJECTS respectively. Note the columns in the views; schema level columns in USER_OBJECTS and object level columns in USER_PROCEDURES.

Removal of subprograms

Obsolete functions and procedures can be removed from the database using DROP command. Note that DROP is a DDL command and carries the feature of ‘Auto Commit’.

Syntax [1]

Example [2]

Passing parameters in a subprogram

Parameters can be passed to a subprogram in three distinct modes. These modes can be ‘pass by value’ and ‘pass by reference’. Oracle implements the passage of parameters in the form of IN, OUT and IN OUT modes. While IN mode is way to pass parameters as ‘Pass by Reference’, OUT and IN OUT modes follow ‘Pass by Value’ method.

Syntax [2]

I shall list the key differences between the two passage methods.

Calling subprograms

Before the release of Oracle 11g version, there used to be two methods of subprogram call i.e. Positional or Named. Oracle 11g has introduced hybrid method of calling subprograms, which is known as Mixed notation. Mixed notation allows making use of both positional and named methods of subroutine call. This enhances the feature flexibility and proves quite useful when a subprogram has mandatory placed at the start and optional parameters at the end.

However, we shall have look at all the three notations of subprogram call as below.

Positional Notation – Every parameter appears at the same position as that in the prototype.

Example [3a]

Named Notation – Parameters can be passed in jumbled order by explicitly specifying the actual parameter along with its value.

Example [3b]

Mixed Notation – Oracle 11g opened the gates for the mixed usage of positional and named notation.

Example [3c]