Oracle SYS Owned Functions
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
Page Note This listing on this page is not intended to be complete but rather to highlight specific functions that have proven to be of interest.
Functions By Name
AQ$_GET_SUBSCRIBERS HS$_DDTF_SQLFOREIGNKEYS ORA_DM_BUILD_FLAT
AV_CACHE_COL HS$_DDTF_SQLPRIMARYKEYS ORA_FI_SUPERVISED_BINNING
CHECK_UPGRADE HS$_DDTF_SQLPROCEDURES ORA_STRING_DISTANCE
CLIENT_IP_ADDRESS HS$_DDTF_SQLSTATISTICS ORIGINAL_SQL_TXT_
CUBE_TABLE HS$_DDTF_SQLTABFORKEYS PARTITION_POS
DATABASE_NAME HS$_DDTF_SQLTABLES PRIVILEGE_LIST
DBJ_LONG_NAME HS$_DDTF_SQLTABPRIKEYS REVOKEE
DBJ_SHORT_NAME HS$_DDTF_SQLTABSTATS SCHEDULER$_BATCHERR_PIPE
DBMS_PDB_CHECK_LOCKDOWN INSTANCE_NUM SCN_TO_TIMESTAMP
DBMS_PDB_IS_VALID_PATH INTERACTIONEXECUTE SERVER_ERROR
DG$GETDGQUOTENAME ISXMLTYPETABLE SERVER_ERROR_DEPTH
DG$GETFLATDG ISXMLTYPETABLE_INTERNAL SERVER_ERROR_MSG
DG$HASDGINDEX IS_ALTER_COLUMN SERVER_ERROR_NUM_PARAMS
DES_ENCRYPTED_PASSWORD IS_CREATING_NESTED_TABLE SERVER_ERROR_PARAM
DICTIONARY_OBJ_NAME IS_DROP_COLUMN SPACE_ERROR_INFO
DICTIONARY_OBJ_NAME_LIST IS_DV_SUPPORTED SQL_TXT
DICTIONARY_OBJ_OWNER IS_OLS_SUPPORTED STRAGG
DICTIONARY_OBJ_OWNER_LIST IS_SERVERERROR STRING_TO_GRANTPATH
DICTIONARY_OBJ_TYPE IS_VPD_ENABLED SYS$RAWTOANY
GETANYTYPEFROMPERSISTENT JSON_DATAGUIDE SYSEVENT
GETLONG JSON_HIERDATAGUIDE SYS_DGAGG
GETTVOID KCISYS_CTXAGG SYS_HIERDGAGG
GETXMLSCHEMADEPENDENCYLIST KUPC$_TAB_MT_COLS SYS_IXMLAGG
GET_APPLICATION_DIFF LOGIN_USER SYS_IXQAGG
GET_AUD_PDB_LIST   SYS_IXQAGGAVG
GET_MAX_CHECKPOINT LOGMNR_GET_GT_PROTOCOL SYS_IXQAGGSUM
GET_OLDVERSION_HASHCODE LOGSTDBY$TABF SYS_NT_COLLECT
GET_OLDVERSION_HASHCODE2 LOGSTDBY$UTABF SYS_XMLAGG
GET_REALM_PARAMETERS MVAGGRAWBITOR TIMESTAMP_TO_SCN
GET_STATS_EXTENSION NameFromLastDDL TSDP$VALIDATION_CHECK
GET_TABLE_NAME OLAPIBOOTSTRAP2 USER_XML_PARTITIONED_TABLE_OK
GRANTEE OLAPRC_TABLE VERIFY_FUNCTION
GRANTPATH_TO_STRING OLAP_BOOL_SRF VERIFY_FUNCTION_11G
HAEN_TXFM_TEXT OLAP_CONDITION V_LISTBACKUPPIPE
HS$_DDTF_OPTCOLUMNS OLAP_DATE_SRF V_LISTRSRANGEPIPE
HS$_DDTF_OPTFOREIGNKEYS OLAP_NUMBER_SRF WITH_GRANT_OPTION
HS$_DDTF_OPTPROCEDURES OLAP_TABLE XMLSEQUENCEFROMREFCURSOR
HS$_DDTF_OPTSTATISTICS OLAP_TEXT_SRF XMLSEQUENCEFROMREFCURSOR2
HS$_DDTF_OPTTABFORKEYS ORA12C_STRONG_VERIFY_FUNCTION XMLSEQUENCEFROMXMLTYPE
HS$_DDTF_OPTTABPRIKEYS ORA12C_VERIFY_FUNCTION XQSEQUENCEFROMXMLTYPE
HS$_DDTF_OPTTABSTATS ORA_COMPLEXITY_CHECK XQWINDOWSEQUENCEFROMXMLTYPE
HS$_DDTF_SQLCOLUMNS ORA_DM_BUILD  
 
CHECK_UPGRADE
Returns TRUE if an upgrade is in progress check_upgrade RETURN BOOLEAN;
conn / as sysdba

BEGIN
  IF check_upgrade THEN
    dbms_output.put_line('An Upgrade Is Running');
  ELSE
    dbms_output.put_line('An Upgrade Is Not Running');
  END IF;
END;
/
 
CLIENT_IP_ADDRESS
See SYS_CONTEXT and SYSTEM EVENTS links at page bottom
 
DATABASE_NAME
See SYS_CONTEXT and SYSTEM EVENTS links at page bottom
 
DBMS_PDB_IS_VALID_PATH
Returns TRUE if the path provided is valid for a PDB dbms_pdb_is_valid_path(path_name IN VARCHAR2)
RETURN BOOLEAN;
conn / as sysdba

BEGIN
  IF dbms_pdb_is_valid_path('/u03/apps18') THEN
    dbms_output.put_line('Valid Path');
  ELSE
    dbms_output.put_line('Invalid Path');
  END IF;
END;
/
 
DES_ENCRYPTED_PASSWORD
See SYS_CONTEXT and SYSTEM EVENTS links at page bottom
 
DICTIONARY_OBJ_NAME
See SYS_CONTEXT and SYSTEM EVENTS links at page bottom
 
DICTIONARY_OBJ_NAME_LIST
See SYS_CONTEXT and SYSTEM EVENTS links at page bottom
 
DICTIONARY_OBJ_OWNER
See SYS_CONTEXT and SYSTEM EVENTS links at page bottom
 
DICTIONARY_OBJ_OWNER_LIST
See SYS_CONTEXT and SYSTEM EVENTS links at page bottom
 
DICTIONARY_OBJ_TYPE
See SYS_CONTEXT and SYSTEM EVENTS links at page bottom
 
GET_AUD_PDB_LIST
Returns an array with valid PDBs get_aud_pdb_list RETURN aud_pdb_list;
SQL> desc aud_pdb_list
aud_pdb_list VARRAY(4098) OF VARCHAR2(128)

SQL> DECLARE
  2   retArray sys.aud_pdb_list;
  3  BEGIN
  4    retArray := get_aud_pdb_list;
  5    FOR i IN 1 .. retArray.count LOOP
  6      dbms_output.put_line(retArray(i));
  7    END LOOP;
  8  END;
  9  /
PDBDEV
CDB$ROOT
 
GET_REALM_PARAMETERS
Undocumented: Presumably with Database Vault enabled the return string would contain one or more parameters get_realm_parameters(realm IN VARCHAR2) RETURN XS$REALM_PARAMETER_TABLE;
SQL> SELECT get_realm_parameters('Oracle Database Vault')
  2  FROM dual;

GET_REALM_PARAMETERS('ORACLEDATABASEVAULT')
--------------------------------------------
XS$REALM_PARAMETER_TABLE;
 
GRANTEE
See SYS_CONTEXT and SYSTEM EVENTS links at page bottom
 
INSTANCE_NUM
Returns the number of the current instance, always 1 except in a RAC cluster instance_num RETURN BINARY_INTEGER;
SQL> SELECT instance_num
  2  FROM dual;

INSTANCE_NUM
------------
           1
 
ISXMLTYPETABLE
Returns TRUE if the table is of XML type

Source: {ORACLE_HOME}/rdbms/admin/prvtxdbz.sql
ISXMLTYPETABLE(owner IN VARCHAR2, table_name IN VARCHAR2) RETURN BOOLEAN;
SELECT owner, table_name
FROM dba_all_tables
WHERE table_type = 'XMLTYPE'
ORDER BY 1,2;

set serveroutput on

BEGIN
  IF isxmltypetable('OE', 'PURCHASEORDER') THEN
    dbms_output.put_line('PURCHASEORDER Is XMLTYPE');
  ELSE
    dbms_output.put_line('PURCHASEORIs Not XMLTYPE');
  END IF;

  IF isxmltypetable('UWCLASS', 'SERVERS') THEN
    dbms_output.put_line('SERVERS Is XMLTYPE');
  ELSE
    dbms_output.put_line('SERVERS Is Not XMLTYPE');
  END IF;
END;
/
 
IS_ALTER_COLUMN
See SYS_CONTEXT and SYSTEM EVENTS links at page bottom
 
IS_CREATING_NESTED_TABLE
See SYS_CONTEXT and SYSTEM EVENTS links at page bottom
 
IS_DROP_COLUMN
See SYS_CONTEXT and SYSTEM EVENTS links at page bottom
 
IS_DV_SUPPORTED
Returns 1 if Database Vault is supported in the current database is_dv_supported RETURN BOOLEAN;
BEGIN
  IF is_dv_supported = TRUE THEN
    dbms_output.put_line('Database Vault Supported');
  ELSE
    dbms_output.put_line('DB Vault Is Not Supported');
  END IF;
END;
/
 
IS_OLS_SUPPORTED
Returns 1 if Oracle Label Security is supported in the current database is_ols_supported RETURN BOOLEAN;
BEGIN
  IF is_ols_supported = TRUE THEN
    dbms_output.put_line('Oracle Label Security Is Supported');
  ELSE
    dbms_output.put_line('Oracle Label Security Is Is Not Supported');
  END IF;
END;
/
 
IS_SERVERERROR
See SYS_CONTEXT and SYSTEM EVENTS links at page bottom
 
IS_VPD_ENABLED
Relates to DBMS_FGAC (Fine Grained Access Control) Source: {ORACLE_HOME}/rdbms/admin/prvtxdz0.sql

IS_VPD_ENABLED (
obj_schema     IN VARCHAR2,
obj_name       IN VARCHAR2,
hierarchy_type IN BINARY_INTEGER)
RETURN BOOLEAN;
set serveroutput on

BEGIN
  IF is_vpd_enabled('UWCLASS', 'SERVERS') THEN
    dbms_output.put_line('Enabled');
  ELSE
    dbms_output.put_line('Disabled');
  END IF;
END;
/
 
LOGIN_USER
Makes a call to dbms_standard.login_user and seems like a completely wasted effort.

Source: {ORACLE_HOME}/rdbms/admin/dbmstrig.sql
LOGIN USER RETURN VARCHAR2;
SELECT user FROM dual;

SELECT login_user FROM dual;
 
NameFromLastDDL
Undocumented and yes it is in mixed case so you must use double quotes

Source: {ORACLE_HOME}/rdbms/admin/initdbj.sql
NameFromLastDDL(longp IN NUMBER) RETURN VARCHAR2;
Source code unwrapped

SELECT "NameFromLastDDL"(1) FROM dual;
 
OLAP_CONDITION
See OLAP Functions link at page bottom
 
ORA12C_STRONG_VERIFY_FUNCTION
Validates a proposed password against the stronger verify function

Source: {ORACLE_HOME}/rdbms/admin/utlpwdmg.sql
ora12c_strong_verify_function(
username     IN VARCHAR2,
password     IN VARCHAR2,
old_password IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
  IF ora12c_strong_verify_function('UWCLASS', 'ora123', 'ora123') THEN
    dbms_output.put_line('T');
  END IF;
END;
/

BEGIN
  IF ora12c_strong_verify_function('UWCLASS', 'ora123ora', 'ora123') THEN
    dbms_output.put_line('T');
  END IF;
END;
/

BEGIN
  IF ora12c_strong_verify_function('UWCLASS', 'Ora123orA', 'ora123') THEN
    dbms_output.put_line('T');
  END IF;
END;
/

BEGIN
  IF ora12c_strong_verify_function('UWCLASS', 'Ora!23$rA', 'ora123') THEN
    dbms_output.put_line('T');
  END IF;
END;
/
 
ORA12C_VERIFY_FUNCTION
Validates a proposed password against the weaker verify function

Source: {ORACLE_HOME}/rdbms/admin/utlpwdmg.sql
ora12c_verify_function(
username     IN VARCHAR2,
password     IN VARCHAR2,
old_password IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
  IF ora12c_verify_function('UWCLASS', 'ora123', 'ora123') THEN
    dbms_output.put_line('T');
  END IF;
END;
/

BEGIN
  IF ora12c_verify_function('UWCLASS', 'ora123ora', 'ora123') THEN
    dbms_output.put_line('T');
  END IF;
END;
/

BEGIN
  IF ora12c_verify_function('UWCLASS', 'Ora123orA', 'ora123') THEN
    dbms_output.put_line('T');
  END IF;
END;
/

BEGIN
  IF ora12c_verify_function('UWCLASS', 'Ora!23$rA', 'ora123') THEN
    dbms_output.put_line('T');
  END IF;
END;
/
 
ORA_COMPLEXITY_CHECK
Called by ora12c_strong_verify_function checks a proposed password for complexity based on the number of characters (length), letters (upper and lower case) digits, and special characters

Source: {ORACLE_HOME}/rdbms/admin/catpvf.sql
ora_complexity_check(
PASSWORD IN VARCHAR2,
CHARS    IN NUMBER(38),
LETTER   IN NUMBER(38),
UPPER    IN NUMBER(38),
LOWER    IN NUMBER(38),
DIGIT    IN NUMBER(38),
SPECIAL  IN NUMBER(38))
RETURN BOOLEAN;
BEGIN
  IF ora_complexity_check('Ora!23$rA', 9, 2, 2, 2, 2, 2) THEN
    dbms_output.put_line('T');
  END IF;
END;
/

BEGIN
  IF ora_complexity_check('Ora!23$rA', 9, 2, 2, 2, 2, 4) THEN
    dbms_output.put_line('T');
  END IF;
END;
/
 
ORA_STRING_DISTANCE
From the name it appears to be a form of INSTRing but it also seems to be broken as the answers returned to not seem to support a consistent logic ora_string_distance(
s IN VARCHAR2,
t IN VARCHAR2)
RETURN NUMBER(38);
SQL> SELECT ora_string_distance('Morgan', 'Margon')
  2  FROM dual;

ORA_STRING_DISTANCE('MORGAN','MARGON')
--------------------------------------
                                     2

SQL> SELECT ora_string_distance('Dan Morgan', 'daniel Margon')
  2  FROM dual;

ORA_STRING_DISTANCE('DANMORGAN','DANIELMARGON')
-----------------------------------------------
                                              6
 
ORIGINAL_SQL_TXT
See SYS_CONTEXT and SYSTEM EVENTS links at page bottom
 
PARTITION_POS
Returns the position of a partition. Seems like a total waste of effort writing this on several counts. Without a schema name and table name ... what's the point? -- source code
CREATE OR REPLACE FUNCTION partition_pos RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.partition_pos;
END;
/
SQL> SELECT partition_pos FROM dual;

PARTITION_POS
-------------
            0
 
PRIVILEGE_LIST
See SYS_CONTEXT and SYSTEM EVENTS links at page bottom
 
REVOKEE
See SYS_CONTEXT and SYSTEM EVENTS links at page bottom
 
SCN_TO_TIMESTAMP
See Conversion Functions link at page bottom
 
SERVER_ERROR
See ORA_SERVER_ERROR under System Events links at page bottom
 
SERVER_ERROR_DEPTH
See ORA_SERVER_ERROR_DEPTH under System Events links at page bottom
 
SERVER_ERROR_MSG
See ORA_SERVER_ERROR_MSG under System Events links at page bottom
 
SERVER_ERROR_NUM_PARAMS
See ORA_SERVER_ERROR_NUM_PARAMS under System Events links at page bottom
 
SERVER_ERROR_PARAM
See ORA_SERVER_ERROR_PARAM under System Events links at page bottom
 
SPACE_ERROR_INFO
Boolean wrapper for the dbms_resumable space_error_info procedure. Not sure why anyone bothered ... but they did

Source: {ORACLE_HOME}/rdbms/admin/dbmsres.sql
CREATE OR REPLACE FUNCTION space_error_info(
 error_type       OUT VARCHAR2,
 object_type      OUT VARCHAR2,
 object_owner     OUT VARCHAR2,
 table_space_name OUT VARCHAR2,
 object_name      OUT VARCHAR2,
 sub_object_name  OUT VARCHAR2)
 RETURN BOOLEAN IS
BEGIN
  RETURN dbms_resumable.space_error_info(error_type, object_type, object_owner, table_space_name, object_name, sub_object_name);
END;
/
set serveroutput on

DECLARE
 etype    VARCHAR2(30);
 otype    VARCHAR2(30);
 oowner   VARCHAR2(30);
 tsname   VARCHAR2(30);
 objname  VARCHAR2(30);
 subobj   VARCHAR2(30);

 RetVal   BOOLEAN;
BEGIN
  IF space_error_info(etype, otype, oowner, tsname, objname, subobj) THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/
 
SQL_TXT
See ORA_SQL_TXT under System Events linked below
 
STRAGG
String Aggregate function as discussed numerous times by Tom Kyte at asktom.oracle.com. The function was built in as of 11gR1and does this: Good thing we have LISTAGG because I'm not impressed.

Source: {ORACLE_HOME}/rdbms/admin/dbmsxidx.sql
STRAGG(input IN VARCHAR2)
RETURN VARCHAR2 AGGREGATE USING string_agg_type;
SQL> conn scott/tiger@pdbdev
Connected.

col employees format a50

SELECT sys.stragg(ename) AS employees
FROM emp
WHERE deptno = 10;

-- and as you will now see this aggregate function is essentially worthless
SELECT deptno, sys.stragg(ename) AS employees
FROM emp
GROUP BY deptno;

 DEPTNO    EMPLOYEES
---------- ------------------------------------
        10 CLARKKINGTURNERADAMSJAMESFORDMILLER
        20 CLARKKINGTURNERADAMSJAMESFORDMILLER
        30 CLARKKINGTURNERADAMSJAMESFORDMILLER
 
SYS$RAWTOANY
Converts RAW to ANYDATA SYS$RAWTOANY(
r      IN RAW,
dty    IN BINARY_INTEGER,
csform IN BINARY_INTEGER,
csid   IN BINARY_INTEGER)
RETURN ANYDATA;
set serveroutput on

DECLARE
 ad ANYDATA;
 l_num      NUMBER;
 l_date     DATE;
 l_varchar2 VARCHAR2(4000);
BEGIN
  ad := sys$rawtoany(utl_raw.cast_to_raw('Dan Morgan'), 1, 1, 1);
  CASE ad.gettypeName
  WHEN 'SYS.NUMBER' THEN
    IF (ad.getNumber(l_num) = dbms_types.success) THEN
      dbms_output.put_line('NUMBER');
    END IF;
  WHEN 'SYS.DATE' THEN
    IF (ad.getDate(l_date) = dbms_types.success) THEN
      dbms_output.put_line('DATE');
    END IF;
  WHEN 'SYS.VARCHAR2' THEN
    IF (ad.getVarchar2(l_varchar2) = dbms_types.success) THEN
      dbms_output.put_line('STRING');
    END IF;
  ELSE
    l_varchar2 := '** unknown **';
  END CASE;
  dbms_output.put_line(ad.gettypeName);
  dbms_output.put_line(l_varchar2);
END;
/
 
SYSEVENT
See ORA_SYSEVENT under System Events linked below
 
SYS_XMLAGG
See XML Functions linked at page bottom
 
TIMESTAMP_TO_SCN
See Conversion Functions linked below
 
TSDP$VALIDATION_CHECK
Undocumented tsdp$validation_check RETURN BOOLEAN;
BEGIN
  IF tsdp$validation_check THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
 
USER_XML_PARTITIONED_TABLE_OK
Undocumented user_xml_partitioned_table_ok(
schema_name     IN VARCHAR2,
object_name     IN VARCHAR2,
lob_column_name IN VARCHAR2)
RETURN NUMBER;
desc dba_xml_tables

SELECT owner, table_name
FROM dba_xml_tables
WHERE storage_type = 'CLOB';

desc xdb.xs$rolesets

SELECT column_name, data_type
FROM dba_tab_cols
WHERE table_name = 'XS$ROLESETS';

SELECT user_xml_partitioned_table_ok('XDB', 'XS$ROLESETS', 'XMLDATA')
FROM dual;
 
VERIFY_FUNCTION
Validates a proposed password against the weakest verify function

Source: {ORACLE_HOME}/rdbms/admin/utlpwdmg.sql
ora12c_verify_function(
username     IN VARCHAR2,
password     IN VARCHAR2,
old_password IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
  IF verify_function('UWCLASS', 'ora123', 'ora123') THEN
    dbms_output.put_line('T');
  END IF;
END;
/

BEGIN
  IF verify_function('UWCLASS', 'ora123ora', 'ora123') THEN
    dbms_output.put_line('T');
  END IF;
END;
/

BEGIN
  IF verify_function('UWCLASS', 'Ora123orA', 'ora123') THEN
    dbms_output.put_line('T');
  END IF;
END;
/

BEGIN
  IF verify_function('UWCLASS', 'Ora!23$rA', 'ora123') THEN
    dbms_output.put_line('T');
  END IF;
END;
/
 
VERIFY_FUNCTION_11G
Validates a proposed password against the 11g verify function

Source: {ORACLE_HOME}/rdbms/admin/utlpwdmg.sql
ora12c_verify_function_11g(
username     IN VARCHAR2,
password     IN VARCHAR2,
old_password IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
  IF verify_function_11g('UWCLASS', 'ora123', 'ora123') THEN
    dbms_output.put_line('T');
  END IF;
END;
/

BEGIN
  IF verify_function_11g('UWCLASS', 'ora123ora', 'ora123') THEN
    dbms_output.put_line('T');
  END IF;
END;
/

BEGIN
  IF verify_function_11g('UWCLASS', 'Ora123orA', 'ora123') THEN
    dbms_output.put_line('T');
  END IF;
END;
/

BEGIN
  IF verify_function_11g('UWCLASS', 'Ora!23$rA', 'ora123') THEN
    dbms_output.put_line('T');
  END IF;
END;
/
 
V_LISTBACKUPPIPE
Returns the name of the most recent/current archived redo log v_listbackuppipe RETURN v_lbrecset_t;
set serveroutput on

desc v_lbrecset_t

DECLARE
 retVal v_lbrecset_t;
BEGIN
  SELECT v_listbackuppipe
  INTO retVal
  FROM dual;

  dbms_output.put_line(retVal(1).fname);
END;
/
 
WITH_GRANT_OPTION
See ORA_WITH_GRANT_OPTION under System Events linked below
 
XMLSEQUENCEFROMREFCURSOR
See XML Functions linked at page bottom

Related Topics
Built-in Functions
Conversion Functions
OLAP Functions
SYS_CONTEXT
System Events
Undocumented Oracle
XML Functions
What's New In 12cR2
What's New In 18cR3

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved