Skip to Main Content
  • Questions
  • How to get a complete list of analytic functions

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Yi.

Asked: August 19, 2020 - 5:41 am UTC

Last updated: August 21, 2020 - 7:57 am UTC

Version: 12c

Viewed 1000+ times

You Asked

There are many Analytic Functions listed in the doc ( https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Analytic-Functions.html#GUID-527832F7-63C0-4445-8C16-307FA5084056 ), however I cannot find them in ALL_PROCEDURES view ( https://docs.oracle.com/database/121/REFRN/GUID-3DED0AD5-4220-4C77-AE16-07060A19E6DF.htm#REFRN20168 ). How do I get a complete list of analytic functions, together with its signature (i.e. argument type, return type, etc.)?

and Chris said...

The *_procedures views list PL/SQL programs. Analytic functions are SQL functions.

I don't know of a view that contains a list of all of these and their signature. I believe you'll have to work through the docs if you want this information.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Shaoyi, August 20, 2020 - 2:14 am UTC

Hi Chris,

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

Have a nice day,
Shaoyi

V$SQLFN_METADATA

Mikhail Velikikh, August 20, 2020 - 9:17 am UTC

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
Chris Saxon
August 20, 2020 - 9:54 am UTC

Didn't know about that one, thanks for sharing!

Shaoyi, August 21, 2020 - 6:18 am UTC

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
Chris Saxon
August 21, 2020 - 7:57 am UTC

Note these don't tell you whether the functions support the full windowing clause. Depending on what you're doing, this may give an incomplete picture.

Number of functions for Oracle version

A reader, August 21, 2020 - 8:27 am UTC

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



More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library