| 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 |
| |
