13. Dezember 2012

Welche SQL-Funktionen gibt es? Die Antwort hat eine Dictionary View!

Which SQL functions are there? The answer is in a dictionary view
Vor einiger Zeit; ich weiss nicht mehr genau, wann das war, erreichte mich die Frage, ob man im Dictionary der Datenbank irgendwo die verfügbaren SQL-Funktionen finden könnte. Für in PL/SQL geschriebene Prozeduren und Funktionen gibt es ja ALL_OBJECTS und für die Funktionen und Prozeduren in einem Package gibt es ALL_PROCEDURES. Aber wo findet man die Information, dass es eine Funktion namens NVL oder LISTAGG gibt?
Und erst kürzlich habe ich den Abschnitt Metadata for SQL Operators and Functions im Advanced Application Developer's Guide gefunden. Demnach sind die SQL-Funktionen in der View V$SQLFN_METADATA Informationen über die Argumente in V$SQLFN_ARG_METADATA enthalten. Da gucken wir doch gleich mal rein ...
SQL> select func_id, name, ANALYTIC, AGGREGATE, version, datatype  from v$sqlfn_metadata where name='LISTAGG';

   FUNC_ID NAME                           ANA AGG VERSION      DATATYPE
---------- ------------------------------ --- --- ------------ --------
       890 LISTAGG                        NO  YES V11R1 Oracle ARG 1
       891 LISTAGG                        YES NO  V11R1 Oracle ARG 1

2 Zeilen ausgewählt.
Aha: Die LISTAGG-Funktion gibt es also als Aggregatsfunktion (zur Verwendung mit GROUP BY) und als analytische Variante. Und es gibt sie seit 11g Release 1. Schauen wir in die Argumente ...
SQL> select * from v$sqlfn_arg_metadata where func_id=890;

   FUNC_ID     ARGNUM DATATYPE DESCR
---------- ---------- -------- ------------------------------
       890          1 EXPR
       890          2 EXPR

2 Zeilen ausgewählt.
Die Aggregatsfunktion von LISTAGG nimmt also zwei Parameter vom Typ "Expression" entgegen. Zurückgegeben wird ein Wert vom Datentyp des ersten Arguments (siehe oben). Diese Beschreibungen sind zwar etwas rudimentär, aber man bekommt was raus. Die nun naheliegende Frage ist natürlich: Was ist da noch alles drin ...?
SQL> select func_id, name, version from v$sqlfn_metadata where version like 'V11%';

   FUNC_ID NAME                           VERSION
---------- ------------------------------ ------------
       112 XMLCAST                        V11R1 Oracle
       756 XMLEXISTS2                     V11R1 Oracle
         : :                              : 
       780 REGEXP_COUNT                   V11R1 Oracle
       781 OPTLRXCOUNT                    V11R1 Oracle
       782 XMLDIFF                        V11R1 Oracle
       783 XMLPATCH                       V11R1 Oracle
       784 SYS_OP_VERSION                 V11R1 Oracle
         : :                              :
Man findet viele, viele Funktionen. Manche erkennt man gut wieder, denn sie sind dokumentiert. Und viele andere sind eben nicht dokumentiert. Das Verwenden nicht dokumentierter Funktionen ist nur wenig sinnvoll, wie das folgende Beispiel zeigt:
SQL> select func_id, name, version, datatype from v$sqlfn_metadata where name ='LOBNVL';

   FUNC_ID NAME                           VERSION      DATATYPE
---------- ------------------------------ ------------ --------
       755 LOBNVL                         V10R2 Oracle UNKNOWN

1 Zeile wurde ausgewählt.

SQL> select * from v$sqlfn_arg_metadata where func_id=755;

Es wurden keine Zeilen ausgewählt

SQL> select lobnvl(null, empty_blob()) from dual;
ERROR:
ORA-03001: Funktion nicht implementiert

Es wurden keine Zeilen ausgewählt
Die Hoffnung, in dieser View neue, coole SQL-Funktionen zu finden, dürfte sich kaum erfüllen - denn es gilt natürlich auch weiterhin: Was nicht dokumentiert ist, kann auch wieder verschwinden oder sich in der Funktionalität komplett ändern. Viele der SQL-Funktionen verwendet die Datenbank nur intern - und man selbst sollte die Finger davon lassen.
Interessant sind die Views dennoch: Denn zum einen kann man nachsehen, ab welcher Datenbankversion eine SQL-Funktion verfügbar ist (wirklich nützlich) und zum anderen kann man nachsehen, welche Namen man nicht selbst verwenden sollte. Man sieht, dass Oracle die Namen seiner internen SQL-Funktionen gerne mit SYS_ oder OP_ beginnen lässt. Und trotz alledem darf man mal nachsehen, welche (zum Beispiel) "analytischen" Funktionen es überhaupt gibt ...
SQL> select name, version, descr from v$sqlfn_metadata where analytic='YES'

NAME                           VERSION      DESCR
------------------------------ ------------ ----------------------------------------
SUM                            V816 Oracle  SUM
AVG                            V816 Oracle  AVG
COUNT                          V816 Oracle  COUNT
VARIANCE                       V816 Oracle  VARIANCE
STDDEV                         V816 Oracle  STDDEV
MIN                            V816 Oracle  MIN
MAX                            V816 Oracle  MAX
FIRST_VALUE                    V816 Oracle  FIRST_VALUE
LAST_VALUE                     V816 Oracle  LAST_VALUE
LAG                            V816 Oracle  LAG
LEAD                           V816 Oracle  LEAD
RANK                           V816 Oracle  RANK
DENSE_RANK                     V816 Oracle  DENSE_RANK
CUME_DIST                      V816 Oracle  Window Percentile
:
Some time ago (I can't remember exactly, when this was), I got the question whether there is a Dictionary View where one could query the existing SQL functions. For procedures and functions written in PL/SQL there is ALL_OBJECTS, and for procedures and functions within a package there is ALL_PROCEDURES. But where is the information, that there is a function named NVL or LISTAGG?
Recently, I found a documentation section titled Metadata for SQL Operators and Functions within the Advanced Application Developer's Guide. Therefore these functions are listed in V$SQLFN_METADATA. Information about the arguments is contained in V$SQLFN_ARG_METADATA. I instantly started SQL*Plus and looked into this ...
SQL> select func_id, name, ANALYTIC, AGGREGATE, version, datatype  from v$sqlfn_metadata where name='LISTAGG';

   FUNC_ID NAME                           ANA AGG VERSION      DATATYPE
---------- ------------------------------ --- --- ------------ --------
       890 LISTAGG                        NO  YES V11R1 Oracle ARG 1
       891 LISTAGG                        YES NO  V11R1 Oracle ARG 1

2 rows selected.
There it is: The LISTAGG function is available as aggregate (GROUP BY) as well as analytic (OVER clause) function. And its available since database version 11g Release 1.
SQL> select * from v$sqlfn_arg_metadata where func_id=890;

   FUNC_ID     ARGNUM DATATYPE DESCR
---------- ---------- -------- ------------------------------
       890          1 EXPR
       890          2 EXPR

2 rows selected.
The aggregate variant of LISTAGG takes two arguments which are "expressions". It returns the same datatype is "argument 1". This information is not very comprehensive, but these two views contain interesting information. The next question is (of course): Which SQL functions are there ...?
SQL> select func_id, name, version from v$sqlfn_metadata where version like 'V11%';

   FUNC_ID NAME                           VERSION
---------- ------------------------------ ------------
       112 XMLCAST                        V11R1 Oracle
       756 XMLEXISTS2                     V11R1 Oracle
         : :                              : 
       780 REGEXP_COUNT                   V11R1 Oracle
       781 OPTLRXCOUNT                    V11R1 Oracle
       782 XMLDIFF                        V11R1 Oracle
       783 XMLPATCH                       V11R1 Oracle
       784 SYS_OP_VERSION                 V11R1 Oracle
         : :                              :
You'll see many, many functions. Some of them are well-known, most of the others are totally unknown. That's because Oracle uses them only internally and the functions are therefore not documented. Just for fun: We try to use the function LOBNVL (sounds cool).
SQL> select func_id, name, version, datatype from v$sqlfn_metadata where name ='LOBNVL';

   FUNC_ID NAME                           VERSION      DATATYPE
---------- ------------------------------ ------------ --------
       755 LOBNVL                         V10R2 Oracle UNKNOWN

1 row selected.

SQL> select * from v$sqlfn_arg_metadata where func_id=755;

no rows selected.

SQL> select lobnvl(null, empty_blob()) from dual;
ERROR:
ORA-03001: function not implemented.

no rows selected.
So ... we'll most probably not find a bunch of cool, new functionality we always were looking for. Direct use of the undocumented functions does not make sense: we don't know what they do and how they behave. And as always with undocumented functions: They might vanish or change their behaviour in the next release or even patchset.
But the views are interesting, though. We can look up, since when (and that is really useful) a SQL function is available. And beyond that we could look up which SQL function names we should better not use for own applications. So we can see that Oracle likes to use names starting with SYS_ or OP_. And, of couse, we can just have an overview on, for instance, die existing analytic functions.
SQL> select name, version, descr from v$sqlfn_metadata where analytic='YES'

NAME                           VERSION      DESCR
------------------------------ ------------ ----------------------------------------
SUM                            V816 Oracle  SUM
AVG                            V816 Oracle  AVG
COUNT                          V816 Oracle  COUNT
VARIANCE                       V816 Oracle  VARIANCE
STDDEV                         V816 Oracle  STDDEV
MIN                            V816 Oracle  MIN
MAX                            V816 Oracle  MAX
FIRST_VALUE                    V816 Oracle  FIRST_VALUE
LAST_VALUE                     V816 Oracle  LAST_VALUE
LAG                            V816 Oracle  LAG
LEAD                           V816 Oracle  LEAD
RANK                           V816 Oracle  RANK
DENSE_RANK                     V816 Oracle  DENSE_RANK
CUME_DIST                      V816 Oracle  Window Percentile
:

1 Kommentar:

marogel hat gesagt…

Hallo Carsten,

Danke, wieder was nützliches gelernt heute

Grüße
Matthias

Beliebte Postings