Hi Chris,

Thank you for the explanation, it is very clear and helpful!

Have a nice day,
Shaoyi

I would not use it as my primary source for getting information on analytic functions - Oracle SQL Language Reference is a way better than that, but there is V$SQLFN_METADATA and its child table V$SQLFN_ARG_METADATA:

select name from v$sqlfn_metadata where analytic='YES';

These views are documented in Oracle Database Reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SQLFN_METADATA.html#GUID-D1A7E52B-D7EF-493C-B7EA-EA7EA9D60DE2

Hi Mikhail,

I'm able to use these two dynamic performance views to retrieve the information I need to build the analytic function signatures. Much appreciated for your help!

Best regards,
Shaoyi

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