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?

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';

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 function 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;

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!