SQL

What Oracle Functions are there


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:

FUNC_ID NAME MINARGS MAXARGS DATATYPE VERSION ANALYTIC AGGREGATE
32 LOWER 1 1 STRING V6 ORACLE NO NO

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:

DATATYPE
STRING

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!

Comments




Please sign-in to post a comment