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

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Yi.

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

Answered by: Chris Saxon - Last updated: August 21, 2020 - 7:57 am UTC

Category: SQL - Version: 12c

Viewed 100+ 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 we 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.

and you rated our response

  (4 ratings)

Reviews

August 20, 2020 - 2:14 am UTC

Reviewer: Shaoyi

Hi Chris,

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

Have a nice day,
Shaoyi

V$SQLFN_METADATA

August 20, 2020 - 9:17 am UTC

Reviewer: Mikhail Velikikh from Dublin, Ireland

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

Followup  

August 20, 2020 - 9:54 am UTC

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

August 21, 2020 - 6:18 am UTC

Reviewer: Shaoyi

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

Followup  

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

August 21, 2020 - 8:27 am UTC

Reviewer: A reader

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

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