Problem

What Oracle Functions are there

You might have used the data dictionary views like ALL_OBJECTS or ALL_PROCEDURES to get a list of objects you've created but did you ever wonder about the built-in functions like NVL or LOWER?

Solution

Recipe #1 - Data Dictionary to get list of Oracle functions

No surprise, there's a data dictionary for that as well. Let's have a look at one simple query:

SELECT func_id, name, minargs, maxargs, datatype, version, analytic, aggregate
FROM v$sqlfn_metadata
WHERE name='LOWER'

This will show you a result like this:

It's pretty easy to spot the minimum and maximum number of arguments. It also tells you if the function is an analytics or aggregate functions to be used in a group by statement. What's also handy, is the column version which tells you the Oracle Version in which the function has been introduced.

You might want to know about the arguments of the function, have a look at this query:

SELECT datatype FROM v$sqlfn_arg_metadata WHERE func_id=32 ORDER BY argnum

This will print a result like this:

Now you know where to get all the information about Oracle functions. No need to leave your Oracle Query tool to know what functions you can use. Built-in and always up to date documentation!