Oracle SYS Owned Functions
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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_OPTTABFORKEYS ORA_FI_DECISION_TREE_HORIZ
CHECK_UPGRADE HS$_DDTF_OPTTABPRIKEYS ORA_FI_EXP_MAX
CLIENT_IP_ADDRESS HS$_DDTF_OPTTABSTATS ORA_FI_SUPERVISED_BINNING
CUBE_TABLE HS$_DDTF_SQLCOLUMNS ORA_STRING_DISTANCE
DATABASE_NAME HS$_DDTF_SQLFOREIGNKEYS ORIGINAL_SQL_TXT
DBJ_LONG_NAME HS$_DDTF_SQLPRIMARYKEYS PARTITION_POS
DBJ_SHORT_NAME HS$_DDTF_SQLPROCEDURES PRIVILEGE_LIST
DEPENDENCIES HS$_DDTF_SQLSTATISTICS REVOKEE
DES_ENCRYPTED_PASSWORD HS$_DDTF_SQLTABFORKEYS SCHEDULER$_BATCHERR_PIPE
DICTIONARY_OBJ_NAME HS$_DDTF_SQLTABLES SCN_TO_TIMESTAMP
DICTIONARY_OBJ_NAME_LIST HS$_DDTF_SQLTABPRIKEYS SERVER_ERROR
DICTIONARY_OBJ_OWNER HS$_DDTF_SQLTABSTATS SERVER_ERROR_DEPTH
DICTIONARY_OBJ_OWNER_LIST INSTANCE_NUM SERVER_ERROR_MSG
DICTIONARY_OBJ_TYPE INTERACTIONEXECUTE SERVER_ERROR_NUM_PARAMS
DM_CL_APPLY ISXMLTYPETABLE SERVER_ERROR_PARAM
DM_CL_BUILD IS_ALTER_COLUMN SPACE_ERROR_INFO
DM_FE_BUILD IS_CREATING_NESTED_TABLE SQL_TXT
DM_GLM_BUILD IS_DROP_COLUMN STRAGG
DM_MOD_BUILD IS_SERVERERROR STRING_TO_GRANTPATH
DM_NMF_BUILD IS_VPD_ENABLED SYS$RAWTOANY
DM_SVM_APPLY KUPC$_TAB_MT_COLS SYSEVENT
DM_SVM_BUILD LOGIN_USER SYS_IXMLAGG
GETLONG LOGMNR$ALWSUPLOG_TABF_PUBLIC SYS_IXQAGG
GETTVOID LOGMNR_GET_GT_PROTOCOL SYS_IXQAGGAVG
GETXMLSCHEMADEPENDENCYLIST LOGSTDBY$TABF SYS_IXQAGGSUM
GET_MAX_CHECKPOINT LOGSTDBY$UTABF SYS_NT_COLLECT
GET_OLDVERSION_HASHCODE MVAGGRAWBITOR SYS_XMLAGG
GET_OLDVERSION_HASHCODE2 NameFromLastDDL TIMESTAMP_TO_SCN
GET_REALM_PARAMETERS OLAPIBOOTSTRAP2 TSDP$VALIDATION_CHECK
GET_STATS_EXTENSION OLAPRC_TABLE USER_XML_PARTITIONED_TABLE_OK
GET_TABLE_NAME OLAP_BOOL_SRF V_LISTBACKUPPIPE
GRANTEE OLAP_CONDITION V_LISTRSRANGEPIPE
GRANTPATH_TO_STRING OLAP_DATE_SRF WITH_GRANT_OPTION
HAEN_TXFM_TEXT OLAP_NUMBER_SRF XMLSEQUENCEFROMREFCURSOR
HS$_DDTF_OPTCOLUMNS OLAP_TABLE XMLSEQUENCEFROMREFCURSOR2
HS$_DDTF_OPTFOREIGNKEYS OLAP_TEXT_SRF XMLSEQUENCEFROMXMLTYPE
HS$_DDTF_OPTPROCEDURES ORA12C_STRONG_VERIFY_FUNCTION XQSEQUENCEFROMXMLTYPE
HS$_DDTF_OPTSTATISTICS ORA_COMPLEXITY_CHECK XQWINDOWSEQUENCEFROMXMLTYPE
 
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
 
DEPENDENCIES
Returns an array of objects dependent upon the named object dependencies('object_name') RETURN name_t
conn / as sysdba

SELECT dependencies('DBA_OBJECTS')
FROM dual;

SELECT dependencies('OBJ$')
FROM dual;
 
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_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
 
ISXMLTYPETABLE
Undocumented 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 the SYSTEM EVENTS link at page bottom
 
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_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

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
The password verify function for user profiles isinstalled by default

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;
/
 
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);
x
 
ORIGINAL_SQL_TXT
See SYS_CONTEXT and SYSTEM EVENTS links at page bottom
 
PARTITION_POS
Returns the position of a partition -- source code
CREATE OR REPLACE FUNCTION partition_pos RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.partition_pos;
END;
/
TBD
 
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@pdborcl
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 almost worthless as such
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

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