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.
Find undocumented functions. This statement produces a list that must be further edited by hand
col usage format a30
SELECT name, minargs, maxargs, datatype, version, analytic, aggregate
FROM v$sqlfn_metadata vsm
WHERE name IN (
SELECT name
FROM v$sqlfn_metadata
MINUS
SELECT DISTINCT procedure_name
FROM dba_procedures)
ORDER BY 1;
Undocumented Aggregate I suspect relates to the Model Clause
Introduced in 9.0.1
cume_distm(<arg>) RETURN NUMBER;
SELECT cume_distm(15500, .05)
WITHIN GROUP
(ORDER BY salary, commission_pct) cume_dist_of_15500
FROM employees;
*
ERROR at line 1:
ORA-00909: invalid number of arguments
-- note the year values
SELECT emp_id, prd_type_id, year, month, amount
FROM uw_sales
ORDER BY 2,3,4;
SELECT prd_type_id, year, month, sales_amount
FROM uw_sales
WHERE prd_type_id BETWEEN 1 AND 2
AND emp_id = 21
MODEL
PARTITION BY (prd_type_id)
DIMENSION BY (month, year)
MEASURES (amount sales_amount) (
sales_amount[11, 2012] = ROUND(sales_amount[CURRENTV(), 2010] * 1.25, 2))
ORDER BY 1, 2, 3;
Undocumented Aggregate I suspect relates to the Model Clause.
Introduced in 9.0.1
dense_rankm(<arg>) RETURN NUMBER;
conn oe/oe@pdbdev
SELECT d.department_name, e.last_name, e.salary, DENSE_RANKM()
OVER (PARTITION BY e.department_id ORDER BY e.salary) AS DENSE_RANK
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id IN (30, 60);
SELECT d.department_name, e.last_name, e.salary, DENSE_RANKM()
*
ERROR at line 1:
ORA-00909: invalid number of arguments
The Federal Information Processing Standard for SQL (FIPS 127-2) requires a way to identify SQL statements that use vendor-supplied extensions
ALTER SESSION SET FLAGGER=<ENTRY | FULL | INTERMEDIATE | OFF>;
conn uwclass/uwclass@pdbdev
CREATE OR REPLACE FUNCTION test(x IN VARCHAR2) RETURN VARCHAR2
AUTHID DEFINER IS
BEGIN
RETURN x;
END;
/
Function created.
ALTER SESSION SET flagger=FULL;
CREATE OR REPLACE FUNCTION test(x IN VARCHAR2) RETURN VARCHAR2
AUTHID DEFINER IS
BEGIN
RETURN x;
END;
/
CREATE OR REPLACE FUNCTION test(x VARCHAR2) RETURN VARCHAR2
*
ERROR at line 1:
ORA-00097: use of Oracle SQL feature not in SQL92 Full Level
ALTER SESSION SET flagger=OFF;
CREATE OR REPLACE FUNCTION test(x VARCHAR2) RETURN VARCHAR2
AUTHID DEFINER IS
BEGIN
RETURN x;
END;
/
Function created.
Undocumented but appears to returns the lag between a primary database and a remote database
LAG_DIFF(
<primary_database> IN VARCHAR2,
<remote_database> IN VARCHAR2)
RETURN NUMBER
SELECT lag_diff('ORABASE','CONN_LINK') FROM dual;
SELECT lag_diff(2,1) FROM dual
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
Undocumented but appears to returns the lead between a primary database and a remote database
LEAD_DIFF(
<primary_database> IN VARCHAR2,
<remote_database> IN VARCHAR2)
RETURN NUMBER
SELECT lead_diff('ORABASE','CONN_LINK') FROM dual;
SELECT lead_diff('ORABASE','CONN_LINK') FROM dual
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
Undocumented but appears to returns the lead between a primary database and a remote database
LEAD_DIFF_PERCENT(
<primary_database> IN VARCHAR2,
<remote_database> IN VARCHAR2)
RETURN NUMBER
SELECT lead_diff_percent('ORABASE', 'CONN_LINK') FROM dual;
SELECT lead_diff('ORABASE','CONN_LINK') FROM dual
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
Returns a string with 'B' in position n if arg1[n] <> arg2[n], otherwise returns the matched character.
Why? I have no idea. And how you would tell a "B" in your string from one indicating a mismatch? Again no idea.
Introduced in 8i
merge$actions(<arg1> IN VARCHAR2, <arg2> IN VARCHAR2) RETURN VARCHAR2;
merge$actions(<arg1> IN NUMBER, <arg2> IN NUMBER) RETURN VARCHAR2;
SELECT merge$actions('ABC', 'ABD') FROM dual;
SELECT merge$actions('ABC', 'ABC') FROM dual;
SELECT merge$actions('ABCDDD', 'ABCEDD') FROM dual;
SELECT merge$actions(1234, 1264) FROM dual;
SELECT SYSDATE, SYSDATE+1, merge$actions(SYSDATE, SYSDATE+1) FROM dual;
Undocumented but likely returns a BOOLEAN or a 1 if the schema in the remote database has the SYSDBA privilege
ora_check_sys_priv(
<remote_db> IN VARCHAR2,
<schema_name> IN VARCHAR2)
RETURN <UNKNOWN>;
SELECT ora_check_sys_priv('PDBDEV', 'UWCLASS') FROM dual;
SELECT ora_check_sys_priv('PDBDEV', 'UWCLASS') FROM dual
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
CREATE OR REPLACE TRIGGER test
AFTER GRANT
ON DATABASE
DECLARE
user_list dbms_standard.ora_name_list_t;
number_of_grantees PLS_INTEGER;
BEGIN
IF (ora_sysevent = 'GRANT') THEN
number_of_grantees := ora_grantee(user_list);
dbms_output.put_line(number_of_grantees);
FOR i IN 1 .. number_of_grantees LOOP
dbms_output.put_line(user_list(i));
END LOOP;
END IF;
END;
/
set serveroutput on
GRANT select ON servers TO scott;
GRANT all ON servers TO scott;
Undocumented and not sure how to use it but clearly it exists
ora_partition_validation(<rowid> IN ROWID) RETURN BINARY_INTEGER;
DECLARE
rid ROWID;
x NUMBER;
BEGIN
SELECT rowid
INTO rid
FROM sys.wrh$_seg_stat
WHERE rownum = 1;
SELECT ora_partition_validation(rid)
INTO x
FROM dual;
END;
/
DECLARE
*
ERROR at line 1:
ORA-14144: arguments to tbl$or$idx$part$num must not be bind variables
ORA-06512: at line 5
How often do you get the opportunity to find something not yet indexed by Google? As of this date, 28-June-2017, google has never seen this string before. How long before it is indexed?
Now that's a question for the hour. Uploading at 00:00:39 CDT.
This undocumented syntax element can be found in only two scripts in Oracle: rdbms/admin/cdcore.sql and rdbms/admin/cdplsql.sql.
Pay close attention to the verbiage at right, written by Oracle developers that provides some insight into how this syntax performs.
To the best of my ability I resisted the urge to improve the developer's formatting but in the end I succumbed to making it more readable. That said I applaud their proper use of parentheses around the final conditions in the WHERE clause.
CREATE OR REPLACE VIEW [<schema_name>.]<view_name>
PDB_LOCAL_ONLY
SHARING EQUALS EXTENDED DATA
(<comma_delimited_column_name_list>) AS
<SELECT statement>;
Define the base view that is used to define DBA, ALL, and USER flavors of *_stored_settings. This base view is defined as Common Data so that Common object information is fetched from ROOT when this view is queried in a PDB.
Note that this base view has an object_type# column whose value is passed to the OBJ_ID function in the definition of all_stored_settings.
Proj 47234: settings$ in PDB stores information about common TYPE objects. In order to prevent selecting rows corresponding to these common objects, we set the attribute pdb_local_only.
CREATE OR REPLACE VIEW int$dba_stored_settings
PDB_LOCAL_ONLY SHARING=EXTENDED DATA
(owner, object_name, object_id, object_type, object_type#, param_name, param_value, sharing, origin_con_id) AS
SELECT u.name, o.name, o.obj#,
DECODE(o.type#,
7, 'PROCEDURE',
8, 'FUNCTION',
9, 'PACKAGE',
11, 'PACKAGE BODY',
12, 'TRIGGER',
13, 'TYPE',
14, 'TYPE BODY',
'UNDEFINED'),
o.type#, p.param, p.value,
CASE WHEN bitand(o.flags, &sharing_bits)>0 THEN 1 ELSE 0 END,
TO_NUMBER(sys_context('USERENV', 'CON_ID'))
FROM sys."_CURRENT_EDITION_OBJ" o, sys.user$ u, sys.settings$ p
WHERE o.owner# = u.user#
AND o.linkname IS NULL
AND p.obj# = o.obj#
AND (o.type# in (7, 8, 9, 11, 12, 14) OR (o.type# = 13 AND o.subname IS NULL));
-- so I gave a try at writing my own
conn / as sysdba
@@?/rdbms/admin/sqlsessstart.sql
CREATE OR REPLACE VIEW int$pdb_local
PDB_LOCAL_ONLY SHARING=EXTENDED DATA AS
SELECT * FROM dual;
SELECT sys_et_blob_to_image(ad_photo, ad_composite, 'TEST')
FROM pm.print_media;
*
ERROR at line 1:
ORA-30175: invalid type given for an argument
DECLARE
retVal BLOB;
strVal VARCHAR2(30) := 'TEST';
BEGIN
SELECT sys_et_blob_to_image(ad_photo, strVal, 'TEST')
INTO retVal
FROM pm.print_media
WHERE rownum = 1;
END;
/
ERROR:
ORA-03114: not connected to ORACLE
DECLARE
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 22020
Session ID: 252 Serial number: 17085
SELECT sys_et_image_to_blob(ad_photo)
FROM pm.print_media;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got BLOB
SYS_ET_RAW_TO_BFILE
Introduced in 10.1
sys_et_raw_to_bfile(<arg1> IN RAW) RETURN BFILE;
SELECT sys_et_raw_to_bfile(utl_raw.cast_to_raw('00094D454449415F44495200096D6F64656D2E6A7067'))
FROM dual;
*
ERROR at line 1:
ORA-22298: length of directory alias name or file name too long
SYS_FBT_INSDEL
Undocumented but possibly relate to DBMS_FBT which is used for Flashback Table. The Return value: string can be turned on/off by running the first demo statement.
sys_fbt_insdel RETURN UNKNOWN;
SELECT sys_fbt_insdel FROM dual;
CREATE TABLE t (
testcol VARCHAR2(20));
DROP TABLE t;
Return value:
Table dropped.
SELECT object_name, original_name, type
FROM recyclebin;
SELECT sys_fbt_insdel FROM "BIN$UROsNzMoQtykMyUcIHae0A==$0";
SELECT EXTRACT(VALUE(j),'/n-document').getClobVal() res
FROM jnl_docs j
WHERE (EXTRACTVALUE(SYS_MAKEXML("XMLDATA"),'/n-document/@guid') = 'I0050092942E540D0BD4B898F70448E97')
OR (EXTRACTVALUE(SYS_MAKEXML("XMLDATA"),'/n-document/n-docbody/metadata/cit-wlde') = 'WLDE2001-0005938');
SELECT PATH
FROM PATH_VIEW
WHERE XMLCast(
XMLQuery(
'declare namespace ns="http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
$r/ns:Resource/ns:DisplayName'
PASSING RES AS "r" RETURNING CONTENT)
AS VARCHAR2(100))
LIKE 'S%'
AND under_path(RES, '/home/QUINE/PurchaseOrders/2002/Apr') = 1;
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 2568289845
Overloaded and converts whatever it receives into Data Type 23 which is not in DBMS_TYPES
Introduced 8.1.5
Overload 1
/* An internal function that takes a value and returns the form that would be stored for that value in a descending index.
Essentially doing a one's complement on the bytes and appending an 0xFF byte */
sys_op_descend(<expression> IN VARCHAR2) RETURN CHAR(6);
SELECT sys_op_descend('0A'), dump(sys_op_descend('0A'))
FROM dual;
SELECT sys_op_descend('Dan Morgan'), dump(sys_op_descend('Dan Morgan'))
FROM dual;
Overload 2
SELECT sys_op_descend(1), dump(sys_op_descend(1))
FROM dual;
Overload 3
SELECT sys_op_descend(SYSDATE), dump(sys_op_descend(SYSDATE))
FROM dual;
Overload 4
SELECT sys_op_descend(SYSTIMESTAMP), dump(sys_op_descend(SYSTIMESTAMP))
FROM dual;
Returns 0 if the column values are identical, 1 if they are not
Overload 1
Introduced 9.0.1
sys_op_distinct(col1 IN NUMBER, col2 IN NUMBER) RETURN SIGNTYPE;
conn uwclass/uwclass@pdbdev
CREATE TABLE t (
col1 NUMBER(3),
col2 NUMBER(3));
INSERT INTO t VALUES (1,1);
INSERT INTO t VALUES (1,2);
INSERT INTO t VALUES (2,1);
INSERT INTO t VALUES (2,2);
INSERT INTO t VALUES (1,NULL);
SELECT * FROM t;
SELECT sys_op_distinct(col1, col2)
FROM t;
Overload 2
sys_op_distinct(col1 IN VARCHAR2, col2 IN VARCHAR2) RETURN SIGNTYPE;
CREATE TABLE t (
col1 VARCHAR2(3),
col2 VARCHAR2(3));
INSERT INTO t VALUES ('a','a');
INSERT INTO t VALUES ('a','b');
INSERT INTO t VALUES ('b','a');
INSERT INTO t VALUES ('b','b');
INSERT INTO t VALUES ('a',NULL);
SELECT * FROM t;
SELECT sys_op_distinct(col1, col2)
FROM t;
Overload 3
sys_op_distinct(col1 IN DATE, col2 IN DATE) RETURN SIGNTYPE;
CREATE TABLE t (
col1 DATE,
col2 DATE);
INSERT INTO t VALUES (SYSDATE,SYSDATE);
INSERT INTO t VALUES (SYSDATE,SYSDATE+1);
INSERT INTO t VALUES (SYSDATE+1,SYSDATE);
INSERT INTO t VALUES (SYSDATE+1,SYSDATE+1);
INSERT INTO t VALUES (SYSDATE,NULL);
COMMIT;
sys_op_map_nonnull(value IN VARCHAR2) RETURN VARCHAR2;
sys_op_map_nonnull(value IN NUMBER) RETURN VARCHAR2;
sys_op_map_nonnull(value IN DATE) RETURN VARCHAR2;
sys_op_map_nonnull(value IN TIMESTAMP) RETURN VARCHAR2;
-- appears to handle any SQL data type
conn scott/tiger@pdbdev
set linesize 121
SELECT * FROM emp;
SELECT comm, sys_op_map_nonnull(comm)
FROM emp;
conn uwclass/uwclass@pdbdev
CREATE TABLE t (
col1 VARCHAR2(20),
col2 VARCHAR2(20),
col3 VARCHAR2(20));
INSERT INTO t VALUES ('ABC', 'ABC', NULL);
INSERT INTO t VALUES ('ABC', 'ABc', NULL);
INSERT INTO t VALUES ('123', NULL, 'ABC');
INSERT INTO t VALUES ('TRUE', 'FALSE', NULL);
INSERT INTO t VALUES (NULL, NULL, 'ABC');
COMMIT;
SELECT *
FROM t
WHERE sys_op_map_nonnull(col1) = sys_op_map_nonnull(col2);
SYS_OP_MSR
Unknown
Introduced in 8.1.5
SYS_OP_MSR(<arg>) RETURN UNKNOWN;
Found in an Explain Plan Report posted on the net by James Morle as follows:
sys_op_numtoraw(<expression> IN NUMBER) RETURN VARCHAR2;
SELECT sys_op_numtoraw(10) FROM dual;
SELECT sys_op_numtoraw(255) FROM dual;
SYS_OP_OIDVALUE
May related to object views
Introduced 8.0
sys_op_oidvalue(object_view_name IN UDT, <arg2>, <arg3>) RETURN UNKNOWN;
SELECT sys_op_oidvalue(AW_PROP$, 1, 1) FROM dual;
SELECT sys_op_oidvalue(AW_PROP$, 1, 1) FROM dual
*
ERROR at line 1:
ORA-22970: name does not correspond to an object view
SELECT sys_op_oidvalue(KU$_10_1_IND_STATS_VIEW, 1, 1) FROM dual;
SELECT sys_op_oidvalue(KU$_10_1_IND_STATS_VIEW, 1, 1) FROM dual
*
ERROR at line 1:
ORA-22819: scope of input value does not correspond to the scope of the target
-- further attempts such as this resulted in a disconnection as did playing with arg3
SELECT sys_op_oidvalue(KU$_10_1_IND_STATS_VIEW, 1, 1) FROM KU$_10_1_IOTABLE_VIEW;
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 8404
Session ID: 69 Serial number: 6377
CREATE TABLE t AS
SELECT object_id, data_object_id
FROM dba_objects
WHERE rownum < 101;
SELECT xx, yy, TO_CHAR(sys_op_par(0, GROUPING_ID(xx, yy), xx, yy)) SYS_OP_PAR_COL
FROM (
SELECT t1.object_id xx, t2.object_id yy
FROM t t1, t t2
WHERE t1.object_id = t2.data_object_id)
GROUP BY xx, ROLLUP(yy)
HAVING GROUPING_ID(xx,yy) = 1;
SYS_OP_PARGID
Value of the first parameter must be between 0 and 255. One or more additional parameters, while mandatory, appears irrelevant.
Overload 1
Introduced 9.2.0.1
sys_op_pargid(value IN INTEGER, <arg2>) RETURN INTEGER;
SELECT sys_op_pargid(1) FROM dual;
SELECT sys_op_pargid(1) FROM dual
*
ERROR at line 1:
ORA-00938: not enough arguments for function
Unknown but likely intended for Explain Plan, ASH, or AWR
sys_plsql_count(<arg1> IN VARCHAR2>) RETURN NUMBER;
SELECT sys_plsql_count('ZZZ') FROM dual;
SELECT sys_plsql_cpu('ZZZ') FROM dual
*
ERROR at line 1:
ORA-39966: This form is allowed only in WITH clause context
Unknown but likely intended for Explain Plan, ASH, or AWR
sys_plsql_cpu(<arg1> IN VARCHAR2>) RETURN NUMBER;
SELECT sys_plsql_cpu('ZZZ') FROM dual;
SELECT sys_plsql_cpu('ZZZ') FROM dual
*
ERROR at line 1:
ORA-39966: This form is allowed only in WITH clause context
Unknown but likely intended for Explain Plan, ASH, or AWR
sys_plsql_io(<arg1> IN VARCHAR2>) RETURN NUMBER;
SELECT sys_plsql_io('ZZZ') FROM dual;
SELECT sys_plsql_io('ZZZ') FROM dual
*
ERROR at line 1:
ORA-39966: This form is allowed only in WITH clause context
sys_op_rawtonum(<expression> IN RAW) RETURN INTEGER;
SELECT sys_op_rawtonum('0A') FROM dual;
SELECT sys_op_rawtonum('0B') FROM dual;
SELECT sys_op_rawtonum('0E') FROM dual;
SELECT sys_op_rawtonum('AE') FROM dual;
SELECT sys_op_rawtonum('FF') FROM dual;
SYS_ORDERKEY_DEPTH( IN VARCHAR2, IN VARCHAR2,
RETURN NUMBER;
CREATE INDEX depth_ix
ON my_path_table(RID, sys_orderkey_depth(ORDER_KEY), ORDER_KEY);
SYS_XMLCONTAINS
Appears to return a 6 when found, 3 when partially found, and 0 when not found.
Introduced in 10.1
sys_xmlcontains(<arg1>, <arg2>) RETURN UNKNOWN;
SELECT sys_xmlcontains(XMLType('<Owner>Grandco</Owner>'),'X') FROM dual;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got -
-- not found
SELECT sys_xmlcontains('<Owner>Oracle</Owner>','Ownerz') FROM dual;
-- found
SELECT sys_xmlcontains('<Owner>Oracle Database</Owner>','Owner') FROM dual;
-- part found
SELECT sys_xmlcontains('<Ownerz>Oracle Database</Owner>','Owner') FROM dual;
SELECT sys_xmlcontains('<Owner>Oracle Database</Ownerz>','Owner') FROM dual;
3 - filter("SYS_NC_TYPEID$" IS NOT NULL AND
CAST(sys_xmlconv("SYS_NTrm0uwhm2Suu6WBsZ4N+t8w=="."SYS_NC00007$", 1, 259, 10333, '4C784CAE38274EF9A15A0334F643A6B5',0,0,1) AS VARCHAR2(3))='010')
sys_xqcodepeq(<value> IN VARCHAR2, <value> IN VARCHAR2) RETURN NUMBER;
sys_xqcodepeq(<value> IN NUMBER, <value> IN NUMBER) RETURN NUMBER;
sys_xqcodepeq(<value> IN DATE, <value> IN DATE) RETURN NUMBER;
SELECT sys_xqcodepeq('Dan Morgan', 'Dan Morgan') FROM dual;
SELECT sys_xqcodepeq('Dan Morgan', 'Dan Norgan') FROM dual;
SELECT sys_xqcodepeq(1,1) FROM dual;
SELECT sys_xqcodepeq(1,2) FROM dual;
SELECT sys_xqcodepeq((2*2),4) FROM dual;
SELECT sys_xqcodepeq(SYSDATE, SYSDATE) FROM dual;
SELECT sys_xqcodepeq(SYSDATE, SYSDATE+1/1440) FROM dual;
SYS_XQDOC
Introduced in 10.1
sys_xqdoc(<arg>) RETURN UNKNOWN;
SELECT sys_xqdoc('A') FROM dual;
*
ERROR:
ORA-31001: Invalid resource handle or path name "A"
no rows selected
Returns the appropriate partition number for one or more values
Introduced in version 6.0
The assignments at right are not necessarily correct but rather are based
on a succession of trials and assumptions based on error messages.
tbl$or$idx$part$num(
<partitioned_table_name> IN VARCHAR2,
<index_identifier> IN NUMBER,
<number_of_column_in_partition_key IN NUMBER,
p# IN BINARY_INTEGER,
<partition_value>) IN ROWID)
RETURN <UNKNOWN>;
SELECT tbl$or$idx$part$num(SYS.WRH$_SEG_STAT, 11211, 7, 2, 'AAATcIAADAAAXOFAAP')
FROM dual;
SELECT tbl$or$idx$part$num(SYS.WRH$_SEG_STAT, 11211, 7, 2, 0)
*
ERROR at line 1:
ORA-14198: rowid column must refer to table specified in 1st parameter
Undocumented but "hopefully" converts XML to JSON.
C2, it appears, should not be a CLOB. Next step is to figure out what the
non-scalar object type should be.
XMLTOJSON(<string> IN VARCHAR2 (or) CLOB)
RETURN VARCHAR2; or RETURN CLOB;
DECLARE
c1 CLOB := '<?xml version="1.0" encoding="utf-8"?>
<ShoppingCartData xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Id>ABCDEFG</Id>
<SiteId>1</SiteId>
<ProductId>100</ProductId>
<Quantity>2</Quantity>
</ShoppingCartData>';
c2 CLOB;
BEGIN
SELECT xmlToJSON(c1)
INTO c2
FROM dual;
END;
/
SELECT xmlToJSON(c1)
*
ERROR at line 12:
ORA-06550: line 12, column 19:
PL/SQL: ORA-00932: inconsistent datatypes: expected - got -
ORA-06550: line 12, column 3:
PL/SQL: SQL Statement ignored
