Hello,
This view V$SQLFN_METADATA is also very interresting to get the distribution of function for Oracle version :
SQL> select version, count(*) from V$SQLFN_METADATA group by version order by substr(version, 2, 2);
VERSION COUNT(*)
------------ ----------
V6 Oracle 132
V71 Oracle 1
V73 Oracle 4
V80 Oracle 49
V81 Oracle 72
V816 Oracle 117
V82 Oracle 123
V92 Oracle 39
V10 Oracle 326
V10R2 Oracle 22
V11R1 Oracle 80
INVALID 120
SQL/DS 41
The name of the function for an Oracle version :
SQL> select version, name, datatype from V$SQLFN_METADATA where version in ('V71 Oracle', 'V73 Oracle') order by version, name;
VERSION NAME DATATYPE
------------ ------------------------------ --------
V71 Oracle OPTPLS UNKNOWN
V73 Oracle ACOS NUMERIC
ASIN NUMERIC
ATAN NUMERIC
ATAN2 NUMERIC
You can have the multiple version of the same function with differents parameters.
SQL> select VERSION, NAME, MINARGS, MAXARGS, DATATYPE, ANALYTIC, AGGREGATE, OFFLOADABLE, DISP_TYPE, DESCR from V$SQLFN_METADATA where name = 'AVG';
VERSION NAME MINARGS MAXARGS DATATYPE ANA AGG OFF DISP_TYPE DESCR
------------ ------------------------------ ---------- ---------- -------- --- --- --- ------------- ------------------------------
V6 Oracle AVG 1 1 ARG 1 NO YES NO NORMAL AVG
V816 Oracle AVG 1 1 NUMERIC YES NO NO NORMAL AVG
V10 Oracle AVG 1 1 NUMERIC NO YES NO NORMAL AVG
AVG 1 1 NUMERIC NO YES NO NORMAL AVG
AVG 1 1 NUMERIC YES NO NO NORMAL AVG(FLOAT) (Windows)
AVG 1 1 NUMERIC YES NO NO NORMAL AVG(DOUBLE) (Windows)
The record is for SUM.
Le record de la fonction la plus présente est 9, pour SUM!
SQL> break on "COUNT(*)" skip 1
SQL> select count(*), name from V$SQLFN_METADATA group by name having count(*) > 1 order by count(*) DESC, name;
COUNT(*) NAME
---------- --------------------
9 SUM
6 AVG
COVAR_POP
COVAR_SAMP
STDDEV
TO_NUMBER
VARIANCE
5 TO_BINARY_DOUBLE
TO_BINARY_FLOAT
TO_CHAR
4 TRUNC
Have a nice day :-)