Functions in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Functions in Oracle with Examples

In this article, I am going to discuss Functions in Oracle with Examples. Please read our previous article where we discussed Clauses in Oracle with Examples. At the end of this article, you will understand what are functions, why do we need functions, and the different categories of oracle functions with examples.

What are Functions in Oracle?

A function in Oracle is a subprogram that is used to perform an action such as complex calculations and returns the result of the action as a value. There are two types of functions in Oracle. They are as follows.

1. System Defined Function / Pre-Defined Functions / Built-in Functions
2. User-Defined Function

The functions which are already defined by the oracle system and ready to be used by the user or developer are called system-defined functions or pre-defined functions whereas if the function is defined by the user or developer then such types of functions are called the user-defined functions.

Some functions take parameters; do some processing and return some results back. For example SELECT POWER(2,3) FROM DUAL; OUTPUT: 12

Some functions may not take any parameters, but returns some result, for example, SELECT SYSDATE FROM DUAL; OUTPUT: 06-NOV-21

Here, the POWER function takes two parameters and returns a value while the SYSDATE function does not take any parameter but returns a value. So, we can say that a function in Oracle can have a parameter(s) that is optional but a function should always return a value that is mandatory. With this kept in mind lets us proceed and understand functions in detail.

Functions in Oracle:

Oracle Functions are is used to perform tasks or some complex calculation and must return value. Oracle supports two types of functions. Those are as follows.

1. Pre-define / Built in functions (use in SQL & PL/SQL)
2. User define functions (use in PL/SQL)

Pre-define functions / Built-in functions in Oracle

The functions which are already defined by the oracle system and ready to be used by the user or developer are called system-defined functions or pre-defined functions. These are again classified into two categories.

1. Single row functions (scalar functions)
2. Multiple row functions (grouping functions)

Single Row Functions in Oracle:

These functions are returning a single row (or) a single value. Following are the examples.

1. Numeric functions
2. String functions
3. Date functions
4. Conversion functions

How to call a function in Oracle?

Following is the syntax to call a function in Oracle:

SELECT <FNAME>(VALUES) FROM DUAL;

What is Dual in Oracle?

1. It is a pre-define table in oracle.
2. It is having single column & single row
3. It is called a dummy table in oracle.
4. It is used for testing functions (pre-define & user define) functionalities.

Structure of Dual table:

DESC DUAL;

Data of DUAL table:

SELECT * FROM DUAL;

In the next article, I am going to discuss Numeric Functions in Oracle with Examples. Here, in this article, I try to explain Functions in Oracle and I hope you enjoy this Functions in Oracle article.