Table of Contents |

Using Built-In SQL And PL/SQL Functions

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.)

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 miscellaneous.

Examples of each function are included. In some instances, the functions are illustrated like this:

chr (97)

This is a call to the function that can appear in one of two places:

**•**Inside a DML statement:SELECT chr (97) FROM <table>;

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.

Oracle provides conversion functions so values of one datatype can be easily converted to another datatype. While Oracle will perform implicit conversions of datatypes for you, this could lead to performance problems in your applications and to compatibility problems in future versions of Oracle. I strongly recommend that you use these conversion functions instead of relying on Oracle’s implicit conversions.

**to_char()**

The **to_char()** function is probably the most commonly used conversion function. The function converts both numerical data and date data to datatype **varchar2**.

The simplest way of using **to_char()** to convert date information is as follows:

to_char ('02/14/97')

This converts the date into a character string in the default date format (numerical month, numerical day, and a two-digit year, separated by slashes). However, the most common use of **to_char()** is to convert dates to type **varchar2** in a specific format, as shown in this example,

to_char ('14-FEB-97', 'DD-MON-YYYY')

which returns the following string:

14-FEB-1997

Using **to_char()** with numerical data is very similar to using the function with dates. One common use is to simply convert data from a numerical datatype to type **varchar2**. For example,

to_char (25000)

returns

25000

When converting numerical data, **to_char()** can also take a second argument, a format model for the output of the function. For example,

to_char (25000, '$99,999.99')

returns

$25,000.00

Chapter 3 lists the elements that can be used in format models for numerical and date data.

**to_date()**

The **to_date()** function is used to convert character data to the **date** datatype. Like **to_char()**, this function can be called with a single parameter, much like

to_date ('02-MAY-97')

which returns a value of type **date**. **to_date()** may also be called with a second parameter, which instructs the function to convert the specified string from the specified format into a standard date. For example,

to_date ('02 May 1997', 'DD MONTH YYYY')

returns

02-MAY-97

Valid elements of the format model are discussed in Chapter 3. The examples provided here have all used the default Oracle date format.

**to_number()**

The **to_number()** function converts data of type **char** or **varchar2** to type **number**. The function can accept a single parameter. For example,

to_number ('25000')

returns

25000

The function can also accept a format model as the second parameter, like the **to_char()** and **to_date()** functions. For example,

to_number ('25000', '$99,999.99')

returns

$25,000.00

Valid elements of the format model are discussed in Chapter 3.

The alphabetic group of functions deals primary with operations on strings but also provides some other interesting functions, such as functions that deal with converting characters to and from their ASCII equivalents.

**ascii()**

The **ascii()** function returns the ASCII value of a specified character. For example,

ascii ('a')

returns

97

If you call **ascii()** using a string longer than a single character, only the ASCII value of the first character will be returned. For example,

ascii ('abcdefg')

returns

97

**chr()**

The **chr()** function is the counterpart of the **ascii()** function. **chr()** returns the character associated with a specific ASCII value. For example,

chr (97)

returns

a

**initcap()**

The **initcap()** function accepts a string as a parameter and returns the string with the first letter of each word in the string capitalized. For example,

initcap ('the quick brown fox...')

returns

The Quick Brown Fox...

**instr()**

The **instr()** function is one of the most useful character functions. In its simplest incarnation, the function accepts two parameters: a string to be searched (let’s call this *string1*) and a string to be searched for (let’s call this *string2*) within string1. The syntax for **instr()** is as follows:

instr ('JELLY BEANS AND APPLE TREES', 'E')

The **instr()** function returns an **integer** value. In our example, the result of the function call is

2

indicating that the first occurrence of string2 in string1 occurs at the second character.

The function can accept a third parameter (let’s call this parameter *x*), which is used to indicate the starting position for the search within string1. For example,

instr ('JELLY BEANS AND APPLE TREES', 'E', 3)

returns

8

X can be negative, which instructs Oracle to count backwards from the end of string1 by x characters before starting the search. For example,

instr ('JELLY BEANS AND APPLE TREES', 'E', -3)

returns

25

As you can see, the value returned by this call to **instr()** is the position of string2 based on the total length of string1 and not on the starting position for the search.

But wait, there’s more! **instr()** can also accept a fourth parameter (let’s call this parameter *y*), which tells Oracle which occurrence of the string2 should be returned. For example,

instr ('JELLY BEANS AND APPLE TREES', 'E', 3, 2)

returns

21

The values of the x and y parameters default to 1 (which means the values start with the first character in string1 and find the first occurrence of string2).

**length()**

The **length()** function accepts a string as a parameter and returns an **integer** value. For example,

length ('HORSES')

returns

6

**lower()**

The **lower()** function accepts a string and converts each character in the string to its lowercase equivalent. For example,

lower ('APPLE')

returns

apple

**lpad()**

The **lpad()** function accepts two parameters: a string (let’s call this *string1*) and an integer value that represents the desired width of the string. Using this information, the function returns the value of string1 padded on the left side until string1 has the desired length. For example,

lpad ('apple', 10)

returns

apple

**lpad()** can also accept a third parameter, string2, which should be used to pad string1 to the desired width. For example,

lpad ('apple', 10, '*')

returns

*****apple

The third parameter of **lpad()** defaults to a single space (‘ ’).

**ltrim()**

In its simplest incarnation, the **ltrim()** function removes all spaces from the left side of a specified string. For example,

ltrim (' apple')

returns

apple

The **ltrim()** function can also accept a second parameter, a set of characters that should be trimmed from the left side of the string. The string is trimmed until the function reaches the first character not appearing in the specified set. For example,

ltrim ('apple', 'ap')

returns

le

The second parameter of **ltrim()** defaults to a single space (‘ ’).

**replace()**

In its simplest form, **replace()** accepts two parameters: a string to be searched (let’s call it *string1*) and a string that should be removed from string1 (let’s call it *string2*). For example,

replace ('applesauce and marinara sauce', 'sauce')

returns

apple and marinara

**replace()** can also accept a third parameter (let’s call it *string3*). When string3 is provided, all occurrences of string2 are replaced with string3. For example,

replace ('applesauce and marinara sauce', 'sauce', ' tree')

returns

apple tree and marinara tree

The third parameter of **replace()** defaults to **NULL**.

**rpad()**

The **rpad()** function is identical to the **lpad()** function, with the exception that string1 is padded on the right side instead of the left.

**rtrim()**

The **rtrim()** function is identical to the **ltrim()** function, with the exception that the string is trimmed on the right side instead of the left.

**substr()**

The **substr()** function is another extremely useful function for parsing strings. Most calls to **substr()** use three parameters. For example,

substr ('apples and oranges', 1, 6)

returns

apples

The call to **substr()** in this example instructs the function to start with the first character of the specified string and return the first six characters of the string. The third parameter can be left out, in which case, the function starts reading at the position indicated by the second parameter and reads until the end of the string is reached.

The value of the second parameter can be negative, which instructs the function to read backwards from the end of the string to find the starting position.

**translate()**

The **translate()** function accepts three parameters. For example,

translate ('xyz', 'x', 'w')

returns

wyz

Every occurrence of the second parameter within the string is replaced with the third parameter. A common use of this is function is to remove nonprinting or special characters from a string. For example,

replace (<string variable>, chr (9), NULL)

removes all the tabs from a string of text.

**upper()**

The **upper()** function is identical to the **lower()** function, with the exception that every character in the string is converted to its uppercase equivalent.

There aren’t very many commonly used date functions. However, the one function that is commonly used— **SYSDATE()** —is, perhaps, the most commonly used function provided by SQL*Plus.

**SYSDATE()**

The **SYSDATE()** function returns the current date and time in the default Oracle date format. The default format for the date returned is

MM-DD-YY

It’s very common to use **SYSDATE()** in conjunction with **to_char()**. For example,

to_char (SYSDATE, 'MM-DD-YYYY HH:MI:SS');

returns a string containing not only the current date, but also the current time down to the second. The most common uses of **SYSDATE()** don’t use a date format model:

dStartDate := SYSDATE;

The following functions all accept numeric values as parameters and return numeric values. Each of the functions listed in this section is accurate to 38 significant digits.

**abs()**

The **abs()** function accepts a single number as a parameter and returns the absolute value of that number. For example,

abs (-9.37)

returns

9.37

**mod()**

The **mod()** function accepts two numbers as parameters, x and y, and returns the remainder of x divided by y. For instance, calling the function to get the remainder of 5 divided by 2,

mod (5, 2)

returns

1

If y is zero, the function returns x. For instance,

mod (5, 0)

returns

5

**power()**

The **power()** function accepts two numbers as parameters, x and y, and returns the value of x raised to the power of y. For instance, calling the function to get the value of 10 raised to the 6th power,

power (10, 6)

returns

1000000

If the value of x is negative, the value of y must be an integer. For instance,

power (-3.54, 2)

returns

12.5316

**round()**

The **round()** function accepts a single number, x, as a parameter and rounds that number to the nearest integer value. For instance, calling the function with a value of 15.37,

round (15.37)

returns

15

The function can also take an integer parameter, y, following the number parameter. This parameter tells the function how many significant digits should be left in place. Using the same value as the previous example while passing 1 for the y parameter,

round (15.37, 1)

returns

15.4

If y is a negative value, the function rounds backwards from the decimal. For example,

round (153.17, -1)

returns

150

which has been rounded at the ones position in the final value.

The function can be called with only one parameter. In this case, y defaults to 0, and the function returns an integer value.

**sqrt()**

The **sqrt()** function accepts a single number as a parameter and returns the square root of that number. For instance, passing 4 to the function,

sqrt (4)

returns

2

This function can’t accept a negative value as a parameter, but the function can return a decimal value. For example,

sqrt (5.25)

returns

2.29128785

**trunc()**

The **trunc()** function accepts a single number, x, as a parameter and returns that number truncated to 0 decimal places. For instance,

trunc (15.37)

returns

15

The function’s second parameter, y, tells the function how many digits of the number should be left intact. This second parameter defaults to 0 and must be an integer value. Using the same value as the previous example, but passing 1 for y,

trunc (15.37, 1)

returns

15.3

There are some commonly used functions that don’t fall neatly into any of the other categories—namely, **decode()** and **nvl()**.

**decode()**

Of all the functions provided by SQL*Plus, **decode()** is perhaps the most useful. A call to **decode()** can accept up to 255 parameters. Calls to **decode()** must follow this basic syntax:

decode (expression, value, new value, default value);

The **decode()** call can only have one expression and one default value. Value and new value must be a pair of parameters. Consider the following call to **decode()**:

SELECT decode (course_grade, 'A', 4, 'B', 3, 'C', 2, 'D', 1, 0) FROM SCHEDULED_CLASSES WHERE ssn = '999999999' AND course_number = 2103;

This function is the closest equivalent to a **case** statement that can be found in SQL. The value and new value parameters don’t have to be literal values; however, they can be expressions such as mathematical operations and function calls.

In the event that you try to port SQL statements between Oracle and another relational database, you should be aware that **decode()** is not an ANSI standard SQL function.

**nvl()**

The **nvl()** function is used to substitute a **NULL** value with another value. The most common example of a call to **nvl()** looks like this:

SELECT nvl (base_salary, 20000) FROM EMPLOYEES;

This would replace any **NULL** value retrieved by the query with the number 20000.

Multi-row SQL functions (also called *group* or *aggregate* functions) work with groups of rows. These functions ignore **NULL** values, except where noted in this section. The most commonly used multi-row SQL functions fall into the numeric group. All functions listed in this section are numeric functions.

The **avg()** function returns the average value of a numeric field from a group of rows. For example,

SELECT avg (base_salary) FROM EMPLOYEES;

returns the average salary of all employees.

The **count()** function counts the number of rows in a group of rows. This function counts all rows in the group, including those for which a **NULL** value is present. There are two ways of calling **count()**, as follows:

SELECT count (*) FROM EMPLOYEE_HISTORY WHERE employee_number = 90213 AND warning = 'Y'; SELECT count (married) FROM EMPLOYEE_HISTORY WHERE employee_number = 90213 AND warning = 'Y';

The first example returns the total number of rows that match the query’s **WHERE** clause. The second example returns the total number of rows that have a non-**NULL** value in the specified column.

The **max()** function returns the highest value of a specified column from a group of rows. For example,

SELECT max (base_salary) FROM EMPLOYEES;

returns the salary of the highest paid employee.

The **min()** function returns the lowest value of a specified column from a group of rows. For example,

SELECT min (base_salary) FROM EMPLOYEES;

returns the salary of the lowest paid employee.

The **sum()** function returns the total of all values for a specified column in a group of rows. For example,

SELECT sum (vacation_days_used) FROM EMPLOYEES;

returns the total number of vacation days taken by employees this year.

PL/SQL provides two important error reporting functions that are not provided by SQL: **SQLCODE()** and **SQLERRM()**.

The **SQLCODE()** function provides the number of the latest Oracle error that has occurred. Following is an example of calling the **SQLCODE()** function:

vErrorCode := SQLCODE;

The **SQLERRM()** function provides the complete text of the most recent Oracle error that has occurred, including the error number provide by the **SQLCODE()** function. Following is an example of calling the **SQLERRM()** function:

vErrorText := SQLERRM;

The following functions aren’t provided by Oracle, but I’ve chosen to include them because they are useful in certain situations or are cleverly written examples of the types of work that a function can do.

Projects that involve the conversion of data from legacy systems may have to deal with numerical data in a nondecimal base. The stored functions described in this section were designed to handle these conversions.

Author’s Note:The following functions were contributed by Shawn Ramsey of AT&T Wireless Services in Seattle, Washington. Many thanks to Shawn for allowing me to include these functions here.

**Converting Base10 Numbers To Another Base**

The **Decimal_2_Any_Base()** function converts numbers from base10 to any base between base2 and base36 (inclusive). Listing A.1 shows the complete source code for the function.

**Listing A.1** The **Decimal_2_Any_Base()** function.

FUNCTION Decimal_2_Any_Base (p_DecNo IN integer, p_Base IN integer) RETURN varchar2 IS -- ***************************************************************** -- DESCRIPTION: Takes a decimal number and converts the base to -- any base from 2 to 36 -- -- Parameters: p_DecNo = Decimal Number to convert. -- p_Base = Base to convert the number to. -- -- Returns: Character string of the number converted to the -- desired base. NULL if invalid conversion or -- a conversion error occurs. -- -- AUTHOR: Shawn M. Ramsey -- -- REVISION HISTORY -- Date Reviser Change -- ---------------------------------------------------------------- -- 28 JAN 1997 S. Ramsey Function creation. -- -- ***************************************************************** ---- Characters for conversion v_AnyNo varchar2(36) := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'; ---- Variables for conversion n_DecNo integer := p_DecNo; n_Base integer := p_Base; v_Return varchar2 (100) := NULL; n_Cnt integer := 0; n_Val integer := 0; BEGIN ---- Check Base Number and Number to convert IF (NOT n_Base BETWEEN 2 AND 36) OR (n_DecNo < 0) THEN RETURN NULL; ELSIF (n_DecNo = 0) THEN RETURN '0'; END IF; ---- Initialize Variables v_Return := ''; n_Cnt := 0 ; ---- Loop and convert WHILE (n_DecNo <> 0) LOOP n_Val := mod (n_DecNo, (n_Base**(n_Cnt+1)))/(n_Base**n_cnt); n_DecNo := n_DecNo - (n_Val * (n_Base**n_Cnt)); v_Return := substr (v_AnyNo, n_Val+1, 1) || v_Return; n_Cnt := n_Cnt + 1; END LOOP; RETURN v_Return; EXCEPTION WHEN OTHERS THEN RETURN NULL; END;

**Converting Numbers Of Odd Bases To Base10**

The **Any_2_Base10()** function can be used to convert a number in any base between base2 and base36 to its base10 equivalent. Listing A.2 shows the complete source code for the function.

**Listing A.2** The **Any_2_Base10()** function.

FUNCTION ANY_2_Base10() (p_Conv IN varchar2, p_Base IN integer) RETURN varchar2 IS -- ***************************************************************** -- DESCRIPTION: Takes a number in any base from 2 to 36 and -- converts it to an integer number. -- -- Parameters: p_Conv = Number to convert. -- p_Base = Base to convert the number from. -- -- Returns: Number converted to the decimal. NULL if invalid -- conversion or a conversion error occurs. -- -- AUTHOR: Shawn M. Ramsey -- -- REVISION HISTORY -- Date Reviser Change -- ---------------------------------------------------------------- -- 28 JAN 1997 S. Ramsey Function creation. -- -- ***************************************************************** ---- Characters for conversion v_AnyNo varchar2 (36) := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'; ---- Exception e_BadData exception; ---- Variables for conversion v_Conv varchar2 (200) := upper (p_Conv); v_ValChar varchar2 (200) := NULL; n_Base integer := p_Base; n_Return integer := NULL; n_Cnt integer := 0; n_Val integer := 0; BEGIN ---- Check Base Number and Number to convert IF (NOT n_Base BETWEEN 2 AND 36) OR (v_Conv IS NULL) THEN RETURN NULL; END IF; ---- Initialize Variables n_Return := 0; n_Cnt := 0; v_ValChar := substr (v_AnyNo, 1, n_Base); ---- Validate String FOR n_Cnt IN 1..length (v_Conv) LOOP IF (v_ValChar NOT LIKE '%' || substr(v_Conv, n_Cnt, 1) || '%') THEN RAISE e_BadData; END IF; END LOOP; ---- Loop and convert FOR n_Cnt IN 1..length (v_Conv) LOOP IF (v_ValChar NOT LIKE '%' || substr (v_Conv, n_Cnt, 1) || '%') THEN RAISE e_BadData; END IF; END LOOP; ---- Convert Number FOR n_Cnt IN REVERSE 1..length (v_Conv) LOOP n_Val := instr (v_ValChar, substr (v_Conv, n_Cnt, 1)) - 1; n_Return := n_Return + (n_Val * (n_Base**(length (v_Conv) - n_Cnt))); END LOOP; RETURN n_Return; EXCEPTION WHEN OTHERS THEN RETURN NULL; END;

The **Is_Number()** function is a relatively simple function that depends on an exception being raised to work properly. A function like this can be useful when data occasionally contains characters mixed with numerals. Listing A.3 gives a complete listing for the function.

**Listing A.3** The **Is_Number()** function.

FUNCTION Is_Number (vValue IN number) RETURN boolean IS nValue number; BEGIN nValue := to_number (vValue); RETURN TRUE; EXCEPTION WHEN INVALID_NUMBER THEN RETURN FALSE; END Is_Number;

This has been a discussion of the most commonly used SQL and PL/SQL functions. There are several more functions that haven’t been discussed, but these functions aren’t commonly encountered. If you encounter a call to a function that you don’t recognize and you can’t locate the source code, it might be a function that wasn’t discussed here. Your best bet is to check for the function in the *PL/SQL User’s Guide And Reference* provided by Oracle.

Table of Contents |