Oracle SYS Owned Functions
Version 12.1.0.1

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.
 
IS_ALTER_COLUMN
See SYS_CONTEXT and SYSTEM EVENTS links below
 
IS_CREATING_NESTED_TABLE
See SYS_CONTEXT and SYSTEM EVENTS links below
 
IS_DROP_COLUMN
See SYS_CONTEXT and SYSTEM EVENTS links below
 
IS_SERVERERROR
See SYS_CONTEXT and SYSTEM EVENTS links below
 
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;
/
 
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;
/
 
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;
 
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 below
 
REVOKEE
See ORA_REVOKEE under SYSTEM EVENTS linked below
 
SCN_TO_TIMESTAMP
See Conversion Functions linked below
 
SERVER_ERROR
See ORA_SERVER_ERROR under System Events linked below
 
SERVER_ERROR_DEPTH
See ORA_SERVER_ERROR_DEPTH under System Events linked below
 
SERVER_ERROR_MSG
See ORA_SERVER_ERROR_MSG under System Events linked below
 
SERVER_ERROR_NUM_PARAMS
See ORA_SERVER_ERROR_NUM_PARAMS under System Events linked below
 
SERVER_ERROR_PARAM
See ORA_SERVER_ERROR_PARAM under System Events linked below
 
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
 
SYSEVENT
See ORA_SYSEVENT under System Events linked below
 
SYS$RAWTOANY
Converts RAW to ANYDATA Source: {ORACLE_HOME}/rdbms/admin/???

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;
/
 
TIMESTAMP_TO_SCN
See Conversion Functions linked below
 
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

Related Topics
Built-in Functions
Conversion Functions
SYS_CONTEXT
System Events
Undocumented Oracle

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