All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

v$sqlfn_metadata: interesting view to check the functions that introduced

Hello,

While reading some documentation on dynamic views , I came across the v$sqlfn_metadata view which shows the function (ex: min,max,avg,> etc) that introduced in which version. May be all knows it but those who does not know about it.

Here is the snippet (edited one) for your reference.

	SQL> select name,version,disp_type, descr from v$sqlfn_metadata;

	NAME                           VERSION      DISP_TYPE     DESCR
	------------------------------ ------------ ------------- --------------------------------------------------
	>                              V6 Oracle    REL-OP        >
	< V6 Oracle    REL-OP        <
	>=                             V6 Oracle    REL-OP        >=
	< =                             V6 Oracle    REL-OP        <=
	=                              V6 Oracle    REL-OP        =
	!=                             V6 Oracle    REL-OP        <>
	OPTTIS                         V6 Oracle    NORMAL        INTERSECT
	OPTTUN                         V6 Oracle    NORMAL        UNION
	OPTTMI                         V6 Oracle    NORMAL        MINUS
	OPTTAD                         V6 Oracle    ARITHMATIC    +
	OPTTSU                         V6 Oracle    ARITHMATIC    -
	OPTTMU                         V6 Oracle    ARITHMATIC    *
	OPTTDI                         V6 Oracle    ARITHMATIC    /
	OPTTNG                         V6 Oracle    ARITHMATIC    -
	AVG                            V6 Oracle    NORMAL        AVG
	SUM                            V6 Oracle    NORMAL        SUM
	COUNT                          V6 Oracle    NORMAL        COUNT
	MIN                            V6 Oracle    NORMAL        MIN
	MAX                            V6 Oracle    NORMAL        MAX
	OPTDESC                        V6 Oracle    NORMAL         DESC
	TO_NUMBER                      V6 Oracle    NORMAL        TO_NUMBER
	TO_CHAR                        V6 Oracle    NORMAL        TO_CHAR
	NVL                            V6 Oracle    NORMAL        NVL
	CHARTOROWID                    V6 Oracle    NORMAL        CHARTOROWID
	ROWIDTOCHAR                    V6 Oracle    NORMAL        ROWIDTOCHAR
	OPTTLK                         V6 Oracle    REL-OP         LIKE
	....
	..

	OPTDSU                         V6 Oracle    ARITHMATIC    -
	ADD_MONTHS                     V6 Oracle    NORMAL        ADD_MONTHS
	MONTHS_BETWEEN                 V6 Oracle    NORMAL        MONTHS_BETWEEN
	TO_DATE                        V6 Oracle    NORMAL        TO_DATE
	OPTDSY                         V6 Oracle    NOPARENTHESIS SYSDATE
	LAST_DAY                       V6 Oracle    NORMAL        LAST_DAY
	NEW_TIME                       V6 Oracle    NORMAL        NEW_TIME
	NEXT_DAY                       V6 Oracle    NORMAL        NEXT_DAY
	OPTDSD                         V6 Oracle    NORMAL        STRING=>DATE
	OPTDDS                         V6 Oracle    NORMAL        DATE=>STRING
	OPTDSI                         V6 Oracle    NORMAL        STRING=>INTERNAL DATE
	OPTDIS                         V6 Oracle    NORMAL        TO_CHAR
	OPTDID                         V6 Oracle    NORMAL        INTERNAL DATE=>DATE
	OPTDDI                         V6 Oracle    NORMAL        DATE=>INTERNAL DATE
	OPTDJN                         V6 Oracle    NORMAL        JULIAN=>NUMBER
	OPTDNJ                         V6 Oracle    NORMAL        JULIAN=>NUMBER
	OPTDDJ                         V6 Oracle    NORMAL        DATE=>JULIAN
	OPTDIJ                         V6 Oracle    NORMAL        INTERNAL DATE=>JULIAN
	OPTDJS                         V6 Oracle    NORMAL        JULIAN=>STRING
	OPTDIF                         V6 Oracle    NORMAL        STRING=>INPUT FORMAT
	OPTDOF                         V6 Oracle    NORMAL        STRING=>OUTPUT FORMAT
	OPTNTI                         V6 Oracle    NORMAL        NUMBER=>INTERNAL INTEGER
	OPTCTZ                         V6 Oracle    NORMAL        DTYCHR=>DTYDTZ
	OPTCDY                         V6 Oracle    NORMAL        DTYCHR=>DTYDYN
	OPTNDY                         V6 Oracle    NORMAL        DTYNUM=>DTYDYN
	OPTDPC                         V6 Oracle    NORMAL        DTYCHR=>DTYDPC
	DUMP                           V6 Oracle    NORMAL        DUMP
	OPTTNS                         V6 Oracle    NORMAL        Nested Select
	OPTDRO                         V6 Oracle    NORMAL        ROUND
	TRUNC                          V6 Oracle    NORMAL        TRUNC
	FLOOR                          V6 Oracle    NORMAL        FLOOR
	CEIL                           V6 Oracle    NORMAL        CEIL
	DECODE                         V6 Oracle    CASELIKE      DECODE
	...
	...
	...
	LOBNVL                         V10R2 Oracle NORMAL        LOBNVL
	XMLEXISTS2                     V11R1 Oracle NORMAL        XMLEXISTS2
	XS_SYS_CONTEXT                 V11R1 Oracle NORMAL        XS_SYS_CONTEXT
	SYS_ROW_DELTA                  V11R1 Oracle NORMAL        SYS_ROW_DELTA
	SYS_GET_PRIVILEGES             V11R1 Oracle NORMAL        SYS_GET_PRIVILEGES
	OPTXPATHOUT                    V11R1 Oracle NORMAL        VALUE
	SYS_XMLTYPE2SQL                V11R1 Oracle NORMAL        SYS_XMLTYPE2SQL
	OPTXLOBSNAP                    V11R1 Oracle NORMAL        SYS_OP_LOBSNAP
	SYS_GET_ACLIDS                 V11R1 Oracle NORMAL        SYS_GET_ACLIDS
	SYS_XMLT_2_SC                  V11R1 Oracle NORMAL        SYS_XMLT_2_SC
	SYS_XQCASTABLEERRH             V11R1 Oracle NORMAL        SYS_XQCASTABLEERRH
	SYS_OPTXQCASTASNQ              V11R1 Oracle NORMAL        SYS_OPTXQCASTASNQ
	OPTCSXUPD                      V11R1 Oracle NORMAL        SYS_OP_CSX_UPD
	SYS_OP_CSX_PATCH               V11R1 Oracle NORMAL        SYS_OP_CSX_PATCH
	XMLTOOBJECT                    V11R1 Oracle NORMAL        XMLTOOBJECT
	OBJECTTOXML                    V11R1 Oracle NORMAL        OBJECTTOXML
	OPTXMLTOREDO                   V11R1 Oracle NORMAL        XMLTOREDO
	SYS_SYNRCIREDO                 V11R1 Oracle NORMAL        SYS_SYNRCIREDO
	DATAOBJ_TO_PARTITION           V6 Oracle    NORMAL        DATA0BJ_TO_PARTITION
	SYS_OP_COMBINED_HASH           V11R1 Oracle NORMAL        SYS_OP_COMBINED_HASH
	SYS_OP_LOBLOC2ID               V11R1 Oracle NORMAL        SYS_OP_LOBLOC2ID
	SYS_OP_LOBLOC2TYP              V11R1 Oracle NORMAL        SYS_OP_LOBLOC2TYP
	SYS_OP_LOBLOC2BLOB             V11R1 Oracle NORMAL        SYS_OP_LOBLOC2BLOB
	SYS_OP_LOBLOC2CLOB             V11R1 Oracle NORMAL        SYS_OP_LOBLOC2CLOB
	SYS_OP_LOBLOC2NCLOB            V11R1 Oracle NORMAL        SYS_OP_LOBLOC2NCLOB
	REGEXP_COUNT                   V11R1 Oracle NORMAL         REGEXP_COUNT
	OPTLRXCOUNT                    V11R1 Oracle NORMAL         REGEXP_COUNT
	XMLDIFF                        V11R1 Oracle NORMAL        XMLDIFF
	XMLPATCH                       V11R1 Oracle NORMAL        XMLPATCH
	SYS_OP_VERSION                 V11R1 Oracle NORMAL        SYS_OP_VERSION
	XMLTRANSFORMBLOB               V10 Oracle   NORMAL        XMLTRANSFORMBLOB
	SYS_XMLTRANSLATE               V11R1 Oracle NORMAL        SYS_XMLTRANSLATE
	SYS_XMLNODEID                  V10 Oracle   NORMAL        SYS_XMLNODEID
	SYS_XMLNODEID_GETPATHID        V10 Oracle   NORMAL        SYS_XMLNODEID_GETPATHID
	SYS_XMLNODEID_GETLOCATOR       V10 Oracle   NORMAL        SYS_XMLNODEID_GETLOCATOR
	OPTDMGETO                      V11R1 Oracle NORMAL        Internal Data Mining Function: GET value
	SYS_DM_RXFORM_NUM              V11R1 Oracle NORMAL        SYS_DM_RXFORM_NUM
	SYS_DM_RXFORM_CHR              V11R1 Oracle NORMAL        SYS_DM_RXFORM_CHR
	OPT793                         V11R1 Oracle NORMAL        Unused
	...
	SYS_GETTOKENID                 V10 Oracle   NORMAL        SYS_GETTOKENID
	SYS_XMLNODEID_GETPTRID         V11R1 Oracle NORMAL        SYS_XMLNODEID_GETPTRID
	NTH_VALUE                      V11R1 Oracle NORMAL        NTH_VALUE
	LISTAGG                        V11R1 Oracle NORMAL        LISTAGG
	LISTAGG                        V11R1 Oracle NORMAL        LISTAGG (window)
	OPTOICSCNV                     V80 Oracle   NORMAL        SYS_OP_CSCONV
	SYS_XQDELETE                   V10 Oracle   NORMAL        SYS_XQDELETE
	SYS_XQINSERT                   V10 Oracle   NORMAL        SYS_XQINSERT
	SYS_XQRENAME                   V10 Oracle   NORMAL        SYS_XQRENAME
	SYS_XQREPLACE                  V10 Oracle   NORMAL        SYS_XQREPLACE
	SYS_XQXFORM                    V10 Oracle   NORMAL        SYS_XQXFORM
	SYS_XQFTCONTAIN                V10 Oracle   NORMAL        SYS_XQXFORM
	ORA_DST_AFFECTED               V11R1 Oracle NORMAL        ORA_DST_AFFECTED
	ORA_DST_ERROR                  V11R1 Oracle NORMAL        ORA_DST_ERROR
	ORA_DST_CONVERT                V11R1 Oracle NORMAL        ORA_DST_CONVERT
	SYS_XQTOKENIZE                 V11R1 Oracle NORMAL        SYS_XQTOKENIZE
	SYS_SC_2_XMLT                  V11R1 Oracle NORMAL        SYS_SC_2_XMLT
	OPTGETXMLSCHEMATYPE            V11R1 Oracle NORMAL        SYS_GETXMLSCHEMATYPE
	SYS_OP_BLOOM_FILTER_LIST       V11R1 Oracle NORMAL        SYS_OP_BLOOM_FILTER_LIST
	SYS_XQEXLOBVAL                 V11R1 Oracle NORMAL        SYS_XQEXLOBVAL
	PART$NUM$INST                  V6 Oracle    NORMAL        PART$NUM$INST
	SYS_OPTLOBPRBSC                V11R1 Oracle NORMAL        SYS_LOBPRBSC
	OPTCSXMERGE                    V11R1 Oracle NORMAL        SYS_OP_CSX_MERGE
	SYS_XMLANALYZE                 V92 Oracle   NORMAL        REWRITEANALYS
	OPTADIX                        V81 Oracle   NORMAL        SYS_OP_ADIX
	SYS_XQFORMATNUM                V11R1 Oracle NORMAL        SYS_XQFORMATNUM
	SYS_EXTPDTXT                   V11R1 Oracle NORMAL        SYS_EXTPDTXT
	OPTTUC                         V6 Oracle    NORMAL        Unused
	SYS_XMLINSTR                   V11R1 Oracle NORMAL        SYS_XMLINSTR
	SYS_XQDURDIV                   V11R1 Oracle NORMAL        SYS_XQDURDIV
	OPTDM                          V10R2 Oracle NORMAL        Internal Data Mining Function
	OPTDMGET                       V11R1 Oracle NORMAL        Internal Data Mining Function: GET value
	SYS_OP_DV_CHECK                V11R1 Oracle NORMAL        SYS_OP_DV_CHECK
	OPTCONSXMLFORCSX               V11R1 Oracle NORMAL        SYS_OP_XMLCONS_FOR_CSX
	SYS_GETXTIVAL                  V11R1 Oracle NORMAL        SYS_GETXTIVAL

	921 rows selected.

-Thanks
Geek DBA

Comments are closed.