Oracle SYS Owned Functions
Version 19c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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. Use the link at page bottom to find system events and SYS_CONTEXT.
Functions By Name
AQ$_GET_CACHE_MSGBM HS$_DDTF_SQLFOREIGNKEYS ORA_COMPLEXITY_CHECK
AQ$_GET_SUBSCRIBERS HS$_DDTF_SQLPRIMARYKEYS ORA_DM_BUILD
AVTUNE_LOG_LISTAGGCLOB$ HS$_DDTF_SQLPROCEDURES ORA_DM_BUILD_FLAT
AV_CACHE_COL HS$_DDTF_SQLSTATISTICS ORA_FI_SUPERVISED_BINNING
CHECK_UPGRADE HS$_DDTF_SQLTABFORKEYS ORA_STRING_DISTANCE
CLIENT_IP_ADDRESS HS$_DDTF_SQLTABLES ORIGINAL_SQL_TXT
CUBE_TABLE HS$_DDTF_SQLTABPRIKEYS PARTITION_POS
DATABASE_NAME HS$_DDTF_SQLTABSTATS PNAME
DBJ_LONG_NAME INSTANCE_NUM PRIVILEGE_LIST
DBJ_SHORT_NAME INTERACTIONEXECUTE REVOKEE
DBMS_PDB_CHECK_LOCKDOWN ISXMLTYPETABLE SCHEDULER$_BATCHERR_PIPE
DBMS_PDB_IS_VALID_PATH ISXMLTYPETABLE_INTERNAL SCN_TO_TIMESTAMP
DES_ENCRYPTED_PASSWORD IS_ALTER_COLUMN SERVER_ERROR
DG$GETDGQUOTENAME IS_CREATING_NESTED_TABLE SERVER_ERROR_DEPTH
DG$HASDGINDEX IS_DROP_COLUMN SERVER_ERROR_MSG
DICTIONARY_OBJ_NAME IS_DV_SUPPORTED SERVER_ERROR_NUM_PARAMS
DICTIONARY_OBJ_NAME_LIST IS_OLS_SUPPORTED SERVER_ERROR_PARAM
DICTIONARY_OBJ_OWNER IS_SERVERERROR SPACE_ERROR_INFO
DICTIONARY_OBJ_OWNER_LIST IS_VPD_ENABLED SPATIALACTIVE
DICTIONARY_OBJ_TYPE JSON$CHECK_EXPR SQL_TXT
GETANYTYPEFROMPERSISTENT JSON_DATAGUIDE STRAGG
GETLONG JSON_HIERDATAGUIDE STRING_TO_GRANTPATH
GETTVOID KCISYS_BITMAPAGG SYS$RAWTOANY
GETXMLSCHEMADEPENDENCYLIST KCISYS_CATRSIAGG SYSEVENT
GET_APPLICATION_DIFF KCISYS_CTXAGG SYS_DGAGG
GET_AUD_PDB_LIST KCISYS_RSIAGG SYS_HIERDGAGG
GET_MAX_CHECKPOINT KUPC$_TAB_MT_COLS SYS_IXMLAGG
GET_OLDVERSION_HASHCODE LOGIN_USER SYS_IXQAGG
GET_OLDVERSION_HASHCODE2 LOGMNR$ALWSUPLOG_TABF_PUBLIC SYS_IXQAGGAVG
GET_REALM_PARAMETERS LOGMNR_GET_GT_PROTOCOL SYS_IXQAGGSUM
GET_STATS_EXTENSION MVAGGRAWBITOR SYS_NT_COLLECT
GET_SUPPORT_MODE NameFromLastDDL SYS_XMLAGG
GET_SUPPORT_REASON OLAPIBOOTSTRAP2 TIMESTAMP_TO_SCN
GET_TABLE_NAME OLAPRC_TABLE TKHCS_LOG_LISTAGGCLOB
GRANTEE OLAP_BOOL_SRF TSDP$VALIDATION_CHECK
GRANTPATH_TO_STRING OLAP_CONDITION USER_XML_PARTITIONED_TABLE_OK
HAEN_TXFM_TEXT OLAP_DATE_SRF V_LISTBACKUPPIPE
HS$_DDTF_OPTCOLUMNS OLAP_NUMBER_SRF V_LISTRSRANGEPIPE
HS$_DDTF_OPTFOREIGNKEYS OLAP_TABLE WITH_GRANT_OPTION
HS$_DDTF_OPTPROCEDURES OLAP_TEXT_SRF XMLSEQUENCEFROMREFCURSOR
HS$_DDTF_OPTSTATISTICS ONAME XMLSEQUENCEFROMREFCURSOR2
HS$_DDTF_OPTTABFORKEYS ORA12C_STIG_VERIFY_FUNCTION XMLSEQUENCEFROMXMLTYPE
HS$_DDTF_OPTTABPRIKEYS ORA12C_STRONG_VERIFY_FUNCTION XQSEQUENCEFROMXMLTYPE
HS$_DDTF_OPTTABSTATS ORA12C_VERIFY_FUNCTION XQWINDOWSEQUENCEFROMXMLTYPE
HS$_DDTF_SQLCOLUMNS    
 
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
 
ONAME
Returns action name from the current session environment using dbms_application_info.read_module oname RETURN VARCHAR2;
exec dbms_application_info.set_module('M', 'A');

SELECT oname FROM dual;

ONAME
------
A
 
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
 
PNAME
Returns module name from the current session environment using dbms_application_info.read_module pname RETURN VARCHAR2;
exec dbms_application_info.set_module('M', 'A');

SELECT pname
FROM dual;

PNAME
------
A
 
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;
 
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 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx