| General Information |
| Description |
The functions described on this page are owned by SYS and created by Oracle for various undocumented purposes.
This listing is not complete. If you want a complete list run this statement:
SELECT object_name
FROM dba_objects
WHERE object_type = 'FUNCTION'
AND owner = 'SYS'
ORDER BY 1;
This is my page for tracking those that become objects of interest. Many are just wrappers for SYSTEM EVENTS (linked at page bottom). |
| |
| CHECK_UPGRADE |
| Returns TRUE if an upgrade is in progress |
Source: {ORACLE_HOME}/rdbms/admin/prvtxdb.plb
CHECK_UPGRADE RETURN BOOLEAN; |
set serveroutput on
BEGIN
IF check_upgrade THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
END;
/ |
| |
| CLIENT_IP_ADDRESS |
| See SYS_CONTEXT and SYSTEM EVENTS link below |
| |
| CUBE_TABLE |
| Undocumented |
Source: {ORACLE_HOME}/rdbms/admin/dbmstrig.sql
CUBE_TABLE(cube IN VARCHAR2) RETURN ANYDATASET; |
| TBD |
| |
| DATABASE_NAME |
| See SYS_CONTEXT and SYSTEM EVENTS link below |
| |
| DBJ_LONG_NAME |
| Undocumented |
Source: {ORACLE_HOME}/rdbms/admin/initdbj.sql
DBJ_LONG_NAME(shortname IN VARCHAR2) RETURN VARCHAR2; |
| Source code unwrapped |
| |
| DBJ_SHORT_NAME |
| Undocumented |
Source: {ORACLE_HOME}/rdbms/admin/initdbj.sql
DBJ_SHORT_NAME(longname IN VARCHAR2) RETURN VARCHAR2; |
| Source code unwrapped |
| |
| DES_ENCRYPTED_PASSWORD |
| See SYS_CONTEXT and SYSTEM EVENTS link below |
| |
| DICTIONARY_OBJ_NAME |
| See SYS_CONTEXT and SYSTEM EVENTS link below |
| |
| DICTIONARY_OBJ_NAME_LIST |
| See SYS_CONTEXT and SYSTEM EVENTS link below |
| |
| DICTIONARY_OBJ_OWNER |
| See SYS_CONTEXT and SYSTEM EVENTS link below |
| |
| DICTIONARY_OBJ_OWNER_LIST |
| See SYS_CONTEXT and SYSTEM EVENTS link below |
| |
| DICTIONARY_OBJ_TYPE |
| See SYS_CONTEXT and SYSTEM EVENTS link below |
| |
| GETTVOID |
| SELECTs tvoid from type$ based on the toid equals the type oid and the version number equals the vsn |
Source: {ORACLE_HOME}/rdbms/admin/dbmsany.sql
GetTvoid(type_oid IN RAW, VSN IN BINARY_INTEGER) RETURN RAW; |
SELECT toid, tvoid, version
FROM type$
WHERE toid <> tvoid and rownum < 10;
SELECT getTvoid(utl_raw.cast_to_raw('6A730E7CFAE04737B2D0D90DDA3993AC'), 8.0)
FROM dual; |
| |
| GET_MAX_CHECKPOINT |
| Returns the maximum valid checkpoint for a session. Only valid for Streams. Used by the DBA_LOGMNR_SESSION view. |
Source: {ORACLE_HOME}/rdbms/admin/catlmnr.sql
GET_MAX_CHECKPOINT(sessionnum IN NUMBER) RETURN NUMBER; |
| SELECT get_max_checkpoint(160) FROM dual; |
| |
| GET_OLDVERSION_HASHCODE |
| Undocumented |
Source: {ORACLE_HOME}/rdbms/admin/dbmsobj.sql
GET_OLDVERSION_HASHCODE(
schema IN VARCHAR2,
slen IN BINARY_INTEGER, -- bytes in first string
typename IN VARCHAR2,
tlen IN BINARY_INTEGER) -- bytes in second string
RETURN RAW |
conn pm/pm
SELECT get_oldversion_hashcode(USER, 2, 'TEXTDOC_TYP',11) FROM dual; |
| |
| GET_STATS_EXTENSION |
| Undocumented |
Source: {ORACLE_HOME}/rdbms/admin/catost.sql
GET_STATS_EXTENSION(colrowid IN ROWID) RETURN CLOB; |
SELECT rowid FROM tab$ WHERE rownum = 1;
SELECT get_stats_extension('AAAAACAABAAAACRAAA') FROM dual; |
| |
| GET_TABLE_NAME |
|
Undocumented: Seems as though if you provide a VPD (DBMS_RLS) policy name it should return the table or view to which it relates.
Unfortuantely it does not seem to do that. |
Source: {ORACLE_HOME}/rdbms/admin/prvtxdz0.sql
GET_TABLE_NAME(
obj_schema IN VARCHAR2,
pol_name IN VARCHAR2,
table_name OUT VARCHAR2)
RETURN BOOLEAN; |
SELECT object_owner, object_name, pf_owner
FROM dba_policies
ORDER BY 1,2;
set serveroutput on
DECLARE
tname user_tables.table_name%TYPE;
BEGIN
IF get_table_name('XDB', 'SERVLET', tname) THEN
dbms_output.put_line('Found: ' || tname);
ELSE
dbms_output.put_line('Not Found: ' || tname);
END IF;
END;
/ |
| |
| GRANTEE |
| See SYS_CONTEXT and SYSTEM EVENTS link below |
| |
| INSTANCE_NUM |
| See SYS_CONTEXT and SYSTEM EVENTS link below |
| |
| INTERACTIONEXECUTE |
| Undocumented: But appears to validate XML. |
Source: Not Found
INTERACTIONEXECUTE(input IN CLOB) RETURN VARCHAR2; |
-- fails
DECLARE
c CLOB := 'This is a sample clob';
v VARCHAR2(1024);
BEGIN
v := interactionexecute(c);
dbms_output.put_line(v);
END;
/
-- succeeds
DECLARE
c CLOB := '<?xml version="1.0"?>
<bk:book xmlns:bk="http://nosuchsite.com">
<bk:tr>
<bk:td>
<bk:chapter>
Chapter 1.
</bk:chapter>
</bk:td>
<bk:td>
<bk:chapter>
Chapter 2.
</bk:chapter>
</bk:td>
</bk:tr>
</bk:book>';
v VARCHAR2(1024);
BEGIN
v := interactionexecute(c);
dbms_output.put_line(v);
END;
/ |
| |
| IS_ALTER_COLUMN |
| See SYS_CONTEXT and SYSTEM EVENTS link below |
| |
| IS_CREATING_NESTED_TABLE |
| See SYS_CONTEXT and SYSTEM EVENTS link below |
| |
| IS_DROP_COLUMN |
| See SYS_CONTEXT and SYSTEM EVENTS link below |
| |
| IS_SERVERERROR |
| See SYS_CONTEXT and SYSTEM EVENTS link 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 link 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 is built into 11gR1 and R2, owned by SYS, and does this.
Good thing we have LISTAGG and WM_CONCAT. |
Source: {ORACLE_HOME}/rdbms/admin/dbmsxidx.sql
STRAGG(input IN VARCHAR2) RETURN VARCHAR2 AGGREGATE USING string_agg_type; |
SQL> conn scott/tiger
Connected.
Session altered.
Session altered.
SQL> SELECT deptno, sys.stragg(ename) AS employees
2 FROM emp
3 GROUP BY deptno;
SELECT deptno, sys.stragg(ename) AS employees
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 13260
Session ID: 128 Serial number: 200 |
| |
| 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 |
| Undocumented |
v_listbackuppipe RETURN v_lbrecset_t; |
set serveroutput on
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 |
| |
