Oracle built-in functions metadata

问题:

Is there a way to get metadata for the Oracle built-in aggeregate and other functions such as AVG, STDDEV, SQRT, etc.? I need to know object id and arguments meta.

In the SYS.ALL_OBJECTS view I couldn't find anything useful. I also tried to search in SYS.ALL_ARGUMENTS view by object_name.

Is there views or tables with built-in functions with data similar to SYS.ALL_OBJECTS and SYS.ALL_ARGUMENTS?

回答1:

You have tagged Oracle 10g, but from what I can find, you'll need Oracle 11g r1 to find out metadata on built-in SQL functions.

You can see metadata for SQL built-in functions with the dynamic performance views V$SQLFN_METADATA (which has general metadata) and V$SQLFN_ARG_METADATA (which has metadata about arguments).

You can join these views on the column FUNCID. For functions with unlimited arguments, such as LEAST and GREATEST, V$SQLFN_ARG_METADATA has only one row for each repeating argument.

回答2:

The straight SQL (non-aggregation) built-in functions can be described in Oracle10g in SQL*Plus by issuing describe SYS.STANDARD.

SQL> desc sys.standard;
FUNCTION  SYS$DSINTERVALSUBTRACT RETURNS INTERVAL DAY TO SECOND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LEFT                           TIMESTAMP               IN
 RIGHT                          TIMESTAMP               IN
FUNCTION  SYS$DSINTERVALSUBTRACT RETURNS INTERVAL DAY TO SECOND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LEFT                           TIMESTAMP WITH TIME ZONE IN
 RIGHT                          TIMESTAMP WITH TIME ZONE IN

... snip for brevity

FUNCTION VSIZE RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 E                              DATE                    IN
FUNCTION VSIZE RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 E                              VARCHAR2                IN
FUNCTION XOR RETURNS BOOLEAN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LEFT                           BOOLEAN                 IN
 RIGHT                          BOOLEAN                 IN