Built-In SQL and PL/SQL Functions
Oracle Tips by
One of the nice things about SQL and PL/SQL is the number of
built-in functions that Oracle provides. This appendix provides
explanations and examples for the most commonly used built-in
functions. (You’ll also find some other useful functions that Oracle
didn’t take the time to include.)
Single-Row SQL Functions
This is the largest and most varied group of SQL functions.
Functions within this group can operate only on the values contained
within a single-row. Single-row functions can be sorted by
functionality and the datatype of their parameters. In most aspects,
single-row functions closely resemble stored functions. For purposes
of this appendix, I’ve divided the most commonly used single-row SQL
functions into five groups: conversion, alphabetic, date, numeric, and
Examples of each function are included. In some instances, the
functions are illustrated like this:
This is a call to the function that can appear in one of two
• Inside a DML
SELECT chr (97)
In this instance, <table> can be the
name of any table to which you have access. You can also use DUAL
here. DUAL is a dummy table recognized by the system for the
express purpose of retrieving a value.
• On the right
side of an expression in a PL/SQL block:
vDelimiter := chr (25);
In this instance, the output of the function
will be assigned to the variable vDelimiter.
In other instances, the examples utilize a DML statement or excerpt
from a PL/SQL block. This indicates that the function calls must be
used in the context shown in the example.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor.