Oracle Feature Embedded Metadata

Question

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

In the SYS.ALL_OBJECTS I did not find anything useful. I also tried searching in the SYS.ALL_ARGUMENTS by object_name.

Are there any views or tables with built-in functions with data like SYS.ALL_OBJECTS and SYS.ALL_ARGUMENTS ?

Answer 1

You noted Oracle 10g, but from what I can find , you'll need Oracle 11g r1 to search for metadata in embedded SQL.

You can see metadata for built-in SQL functions with dynamic performance representations of V$SQLFN_METADATA (with common metadata) and V$SQLFN_ARG_METADATA (which have metadata about arguments).

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

Answer 2

Direct built-in SQL functions (without aggregation) 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 short

 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