Undocumented Oracle
Version 19.3

<
General Information
Library Note Morgan's Library Page Header
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;
 
ADJ_DATE
Truncates a timestamp to "seconds" precision

Introduced in 9.0.1
adj_date(<expression> IN DATE) RETURN DATE;
CREATE TABLE t(
dcol TIMESTAMP);

INSERT INTO t VALUES (SYSTIMESTAMP);

col adjdate format a35
col dcol format a35
col systimestamp format a35

SELECT adj_date(dcol) AS ADJDATE, dcol, SYSTIMESTAMP
FROM t;

SELECT dump(adj_date(dcol)), dump(dcol), dump(SYSTIMESTAMP)
FROM t;

-- for one of Oracle's best error messages
SELECT adj_date(SYSDATE)
FROM dual;
 
AS_JSON
  AS_JSON(<string> IN VARCHAR2 (or) CLOB)
RETURN VARCHAR2; or AS_JSON RETURN CLOB;
SELECT as_json('Dan Morgan')
FROM dual;

AS_JSON('{DA
------------
{Dan Morgan}
 
BSON (new 19c)
Converts JSON to BSON (Binary JSON) bson(<arg1> IN VARCHAR2) RETURN <value>
SELECT bson('{a:100}')
FROM dual;

BSON('{A:100}')
------------------------
0C0000001061006400000000
 
COLUMNS
Not sure what it does but proof it exists COLUMNS(<argument>)
SELECT columns('TAB$')
FROM dual;
       *
ERROR at line 1:
ORA-62556: Incorrect use of COLUMNS operator.
 
CONTAINERS
Adds the CON_ID column to a select statement's results

It is possible that CON_ID is, in some sense, similar to a pseudo-column.
CONTAINERS(<object_name>)
conn / as sysdba

SELECT property_name, con_id
FROM database_properties
WHERE rownum < 6;
*
ERROR at line 1:
ORA-00904: "CON_ID": invalid identifier


col property_name format a30

SELECT property_name
FROM CONTAINERS(database_properties)
ORDER BY 1,2;

PROPERTY_NAME
-----------------------------
BACK_END_DB
CON_VSN
DBTIMEZONE
DEFAULT_EDITION
DEFAULT_PERMANENT_TABLESPACE
DEFAULT_TBS_TYPE
DEFAULT_TEMP_TABLESPACE
DICT.BASE
DICTIONARY_ENDIAN_TYPE
DST_PRIMARY_TT_VERSION
DST_SECONDARY_TT_VERSION
DST_UPGRADE_STATE
EXPORT_VIEWS_VERSION
Flashback Timestamp TimeZone
GLOBAL_DB_NAME
LOCAL_UNDO_ENABLED
MAX_AQ_STREAMSPOOL
MAX_PDB_SNAPSHOTS
MAX_PDB_STORAGE
MAX_SHARED_TEMP_SIZE
MAX_STRING_SIZE
MIN_AQ_STREAMSPOOL
NLS_CALENDAR
NLS_CHARACTERSET
NLS_COMP
NLS_CURRENCY
NLS_DATE_FORMAT
NLS_DATE_LANGUAGE
NLS_DUAL_CURRENCY
NLS_ISO_CURRENCY
NLS_LANGUAGE
NLS_LENGTH_SEMANTICS
NLS_NCHAR_CHARACTERSET
NLS_NCHAR_CONV_EXCP
NLS_NUMERIC_CHARACTERS
NLS_RDBMS_VERSION
NLS_SORT
NLS_TERRITORY
NLS_TIMESTAMP_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_TIME_FORMAT
NLS_TIME_TZ_FORMAT
NO_USERID_VERIFIER_SALT
NO_USERID_VERIFIER_SALT_COPY
OLS_OID_STATUS
TDE_MASTER_KEY_ID
WORKLOAD_CAPTURE_MODE
WORKLOAD_REPLAY_MODE
 
CON_ID_TO_CON_NAME
Returns the container name corresponding to the container identifier con_id_to_con_name(<arg> IN NUMBER) RETURN VARCHAR2;
SELECT con_id_to_con_name(1)
FROM dual;

CON_ID_T
--------
CDB$ROOT
 
CON_ID_TO_DBID
Returns the DBID corresponding to the container identifier con_id_to_dbid(<arg> IN NUMBER) RETURN NUMBER;
SELECT con_id_to_dbid(1)
FROM dual;

CON_ID_TO_DBID(1)
-----------------
       1262297360
 
CO_AUTH_IND
Undocumented

Introduced in 11.1.0.6
co_auth_ind(<arg> IN UNKNOWN) RETURN UNKNOWN;
SELECT co_auth_ind from dual;
*
ERROR at line 1:
ORA-00904: "CO_AUTH_IND": invalid identifier


SELECT co_auth_ind(1) FROM dual;
*
ERROR at line 1:
ORA-01760: illegal argument for function


SELECT co_auth_ind(1,1) FROM dual;
*
ERROR at line 1:
ORA-00909: invalid number of arguments
 
CSCONVERT
Appears related to Character Set conversion

Introduced in 8.0
csconvert(<arg1>, <arg2>, [<arg3] ...) RETURN VARCHAR2
conn uwclass/uwclass@pdbdev

CREATE TABLE t (
vchar VARCHAR2(20),
nchar NVARCHAR2(20));

INSERT INTO t VALUES ('??', n'??');
COMMIT;

SELECT vchar, dump(vchar), nchar, dump(nchar) FROM t;

set linesize 121
col a format a25
col b format a25
col c format a25
col d format a25

SELECT dump(vchar) a, dump(csconvert(vchar,'NCHAR_CS'),16) b,dump(vchar,16) c, dump(nchar,16) d
FROM t;

SELECT dump(csconvert(vchar,'CHAR_CS'),16) a,dump(vchar,16) b,dump(nchar,16) c, dump(nchar,16) d
FROM t;

SELECT dump(csconvert(nchar,'NCHAR_CS'),16) a,dump(vchar,16) b,dump(nchar,16) c, dump(nchar,16) d
FROM t;

SELECT dump(csconvert(nchar,'CHAR_CS'),16) a,dump(vchar,16) b,dump(nchar,16) c, dump(nchar,16) d
FROM t;
SELECT dump(utl_i18n.escape_reference('UW' || CHR(150),'US7ASCII'))
FROM dual;

SELECT dump(csconvert(utl_i18n.escape_reference('UW' || CHR(150),'US7ASCII'), 'NCHAR_CS'))
FROM dual;
 
CUME_DISTM
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
 
CURRENTV
Related to the Model Clause returning the current value of a dimension

Introduced in 10.2.0.1
currentv(<arg>) RETURN NUMBER;
conn uwclass/uwclass@pdbdev

CREATE TABLE uw_sales (
year        INTEGER,
month       INTEGER,
prd_type_id INTEGER,
emp_id      INTEGER ,
amount      NUMBER(8, 2));

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2011, 1, 1, 21, 16034.84);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2011, 2, 1, 21, 15644.65);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2011, 3, 2, 21, 20167.83);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2011, 4, 2, 21, 25056.45);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2011, 5, 2, 21, NULL);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2011, 6, 1, 21, 15564.66);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2011, 7, 1, 21, 15644.65);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2011, 8, 1, 21, 16434.82);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2011, 9, 1, 21, 19654.57);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2011, 10, 1, 21, 21764.19);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2011, 11, 1, 21, 13026.73);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2011, 12, 2, 21, 10034.64);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2010, 1, 2, 22, 16634.84);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2010, 1, 2, 21, 26034.84);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2010, 2, 1, 21, 12644.65);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2010, 3, 1, 21, NULL);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2010, 4, 1, 21, 25026.45);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2010, 5, 1, 21, 17212.66);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2010, 6, 1, 21, 15564.26);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2010, 7, 2, 21, 62654.82);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2010, 8, 2, 21, 26434.82);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2010, 9, 2, 21, 15644.65);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2011, 11, 2, 21, 21264.19);

INSERT into uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2010, 11, 1, 21, 13026.73);

INSERT INTO uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2010, 12, 1, 21, 10032.64);

INSERT INTO uw_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
VALUES (2010, 11, 2, 21, 10032.64);
COMMIT;

-- 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;
 
DBMS_PDB_EXEC_SQL
API for dbms_pdb.exec_as_oracle_script

Introduced in 12.1.0.1 slightly altered in 12.2.0.1 by Oracle. Owned by SYS with execute granted to XDB.
-- full source code properly formatted for the library

CREATE OR REPLACE PROCEDURE dbms_pdb_exec_sql(sql_stmt IN VARCHAR2) AUTHID CURRENT_USER AS
BEGIN
  dbms_pdb.exec_as_oracle_script(sql_stmt);
END;
/
 
DENSE_RANKM
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
 
FIPS Flagging
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.
 
FIRSTM
Undocumented Aggregate I suspect relates to the Model Clause

Introduced in 9.0.1
firstm(<arg>) RETURN UNKNOWN;
SELECT firstm(1,1) FROM dual;
       *
ERROR at line 1:
ORA-00909: invalid number of arguments
 
JSONTOXML
Undocumented, and the demo at right not perfected, but likely casts JSON its XML equivalent con_id_to_con_name(<arg> IN NUMBER) RETURN VARCHAR2;
SELECT doc_id, date_loaded
FROM j_purchase_order
WHERE jsontoxml(po_document, '$[*].PONumber') = 'zzyzx';
 
JSON_HASH (new 19c)
Undocumented json_hash(<arg> IN VARCHAR2) RETURN RAW;
SELECT standard_hash('Morgan') FROM dual;

STANDARD_HASH('MORGAN')
----------------------------------------
8E4408B475D63385A73AED2FE911DD9818E82FB5


SELECT ora_hash('Morgan') FROM dual;

ORA_HASH('MORGAN')
------------------
4043173571


SELECT json_hash('Morgan') FROM dual;

JSON_HASH('MORGAN')
----------------------------------------------------------------
02281B3B5DD57C4643681B8B113C9D56E9B8F1DC8C30A5BBA4C864BDD27D1ED7


-- the above string looks a lot like BASE64 to me so I tried the following

SELECT utl_encode.base64_decode(json_hash('Morgan'))
FROM dual;

UTL_ENCODE.BASE64_DECODE(JSON_HASH('MORGAN'))
---------------------------------------------
050A150347


-- and strongly suspect that this is the hash and the JSON_HASH function returns
-- the hash as BASE64
 
JSON_EQUAL
Undocumented json_equal(<arg1>, <arg2>, <arg3>, <arg4>) RETURN BOOLEAN;
TBD
 
JSON_MERGEPATCH
Undocumented json_mergepatch(<arg1>, <arg2>, <arg3>, <arg4>) RETURN <value>
TBD
 
JSON_SERIALIZE (new 19c)
Undocumented json_serialize(<arg1>) RETURN <value>
TBD
 
JSON_TEXTCONTAINS2
Undocumented JSON_TEXTCONTAINS(<column_name>, <JSON_path_expression>, '<string>')
TBD
 
LAG_DIFF
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
 
LAG_DIFF_PERCENT
Undocumented but appears to returns the lag between a primary database and a remote database LAG_DIFF_PERCENT(
<primary_database> IN VARCHAR2,
<remote_database>  IN VARCHAR2)
RETURN NUMBER
TBD
 
LEAD_DIFF
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
 
LEAD_DIFF_PERCENT
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
 
LOBNVL
An NVL for LOBs. I haven't test it but I would expect there is a BLOB overload too

Introduced in 10gR2
lobnvl(arg1 IN VARCHAR2>, <arg2 IN CLOB> RETURN CLOB;
CREATE TABLE lobtest (
rid    NUMBER,
lobcol CLOB);

DECLARE
 c1 VARCHAR2(30);
 c2 CLOB := TO_CLOB('C1 is NULL');
BEGIN
  INSERT INTO lobtest
  (rid, lobcol)
  VALUES
  (1, lobnvl(c1, c2));
  COMMIT;
END;
/

SELECT * FROM lobtest;
 
MERGE$ACTIONS
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;
 
OBJECT2XML
Converts an object data type into an XML representation

Introduced in 11gR1
object2xml(<column_name> IN UDT) RETURN XMLTPE;
conn / as sysdba

CREATE OR REPLACE TYPE message_t AUTHID DEFINER AS OBJECT(
id     NUMBER,
source VARCHAR2(30),
rx     VARCHAR2(30));
/

CREATE TABLE msgs (
testcol message_t);

INSERT INTO msgs VALUES (message_t(1, 'TEST', 'Thorazine'));
COMMIT;

set linesize 151
col testcol format a34
col o2xmlcol format a108

SELECT testcol AS TESTCOL, object2xml(testcol) AS O2XMLCOL
FROM msgs;

TESTCOL(ID, SOURCE, RX) O2XMLCOL
---------------------------------- ------------------------------------------------------
MESSAGE_T(1, 'TEST', 'Thorazine') <?oracle-xmldoc versions="D0 C0" ?><MESSAGE_T><ID>1</ID><SOURCE>TEST</SOURCE><RX>Thorazine</RX></MESSAGE_T>
 
OBJ_ID
Unknown obj_id(3 or 4 parms)
TBD
 
ORA_CHECK_SYS_PRIV
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
 
ORA_NAME_LIST_T
Undocumented TYPE ora_name_list_t IS TABLE OF VARCHAR2(64);
conn uwclass/uwclass@pdbdev

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;
 
ORA_PARTITION_VALIDATION
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
 
ORA_RAWCOMPARE
Unknown: Though one might reasonably expect a comparison of RAW values ora_rawcompare(<arg1>, <arg2>, <arg3>) RETURN NUMBER;
SELECT
ora_rawcompare(utl_i18n.string_to_raw
('MORGAN'), utl_i18n.string_to_raw('MORGAN'), 1)
FROM dual;

ORA_RAWCOMPARE(UTL_I18N.STRING_TO_RAW('MORGAN'),UTL_I18N.STRING_TO_RAW('MORGAN'),1)
-----------------------------------------------------------------------------------
                                                                                  0


SELECT ora_rawcompare('A', SYSDATE, 5)
FROM dual;

ORA_RAWCOMPARE('A',SYSDATE,5)
-----------------------------
                           -1
 
OSON
Unknown

Demos at right return NULL proving the function does exist but not helping to establish why
oson(<arg> IN VARCHAR2) RETURN <UNKNOWN>;
SELECT oson('ZZYZX') AS RETVAL
FROM dual;

RETVAL
------------


SELECT oson('12345') AS RETVAL
FROM dual;

RETVAL
------------
 
 
PART$NUM$INST
Introduced in 6.0 part$num$inst(<arg>) RETURN UNKNOWN;
SELECT part$num$inst(3)
FROM dual;

PART$NUM$INST(3)
----------------
             120
 
PDB_LOCAL_ONLY
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;

@?/rdbms/admin/sqlsessend.sql
 
PERCENT_RANKM
Aggregate function likely related to the Model clause

Introduced in 9i
percent_rankm(<arg> IN NUMBER) RETURN NUMBER;
SELECT percent_rank(1) FROM dual;
                       *
ERROR at line 1:
ORA-02000: missing WITHIN keyword


SELECT percent_rankm(1,1) FROM dual;
       *
ERROR at line 1:
ORA-00909: invalid number of arguments
 
PRINTBLOBTOCLOB
Unknown printblobtoclob(2 args)
TBD
 
RANKM
Aggregate function likely related to the Model clause

Introduced in 9i
rankm(<arg> IN NUMBER) RETURN NUMBER;
SELECT rankm(1) FROM dual;
       *
ERROR at line 1:
ORA-00919: invalid function


SELECT rankm(1,1) FROM dual;
       *
ERROR at line 1:
ORA-00909: invalid number of arguments
 
SDO_GEOM_MBR
Unknown sdo_geom_mbr(2 args) RETURN BOOLEAN;
SELECT sdo_geom_mbr('UWCLASS', 'SERVERS') FROM dual;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got CHAR
 
SYS_CHECKACL
Found in the EXPLAIN PLAN output for a query on an XMLType table created as a result of calling PL/SQL procedure DBMS_XMLSCHEMA sys_checkacl(<arg1>, <arg2>, <arg3>) RETURN UNKNOWN;
3 - filter(sys_checkacl("ACLOID","OWNERID",xmltype(''<privilege
xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd
http://xmlns.oracle.com/xdb/acl.xsd
DAV:http://xmlns.oracle.com/xdb/dav.xsd">
<read-properties/><read-contents/></privilege>''))=1)
 
SYS_DOM_COMPARE
Introduced in 9.2.0.1 sys_dom_compare(<arg1>, <arg2>) RETURN UNKNOWN;
SELECT sys_dom_compare(1,1) FROM dual;
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
 
SYS_ET_BFILE_TO_RAW
Introduced in 10.1 sys_et_bfile_to_raw(arg IN bfile) RETURN RAW;
conn pm/pm@pdbdev

SELECT sys_et_bfile_to_raw(ad_graphic)
FROM pm.print_media;
 
SYS_ET_BLOB_TO_IMAGE
Introduced in 10.1 sys_et_blob_to_image(<blob arg1>, <arg2>, <arg3>) RETURN UNKNOWN;
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
 
SYS_ET_IMAGE_TO_BLOB
Introduced in 10.1 sys_et_image_to_blob(<arg1>, <arg2>, <arg3>) RETURN BLOB;
conn pm/pm@pdbdev

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 * FROM TABLE(dbms_xplan.display);

----------------------------------------------------------------------------------------
| Id | Operation        | Name                          | Rows | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT |                               |    1 |       2 (0)| 00:00:01 |
|  1 | TABLE ACCESS FULL| BIN$UROsNzMoQtykMyUcIHae0A==$0|    1 |       2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------


PURGE recyclebin;
Return value:

Recyclebin purged.
 
SYS_MAKEXML
Introduced in 9i makexml(<arg>) RETURN XMLTYPE;
-- Found on the internet: Source

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');
-- found on the internet: Source

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

-----------------------------------------------------------------------------------------
| Id | Operation                            | Name          | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |               |   17 |  3111 |      34 (6)|
|  1 |  NESTED LOOPS                        |               |   17 |  3111 |      34 (6)|
|  2 |   NESTED LOOPS                       |               |   17 |  2822 |      34 (6)|
|  3 |    NESTED LOOPS                      |               |  466 | 63842 |      34 (6)|
|* 4 |     TABLE ACCESS BY INDEX ROWID      | XDB$RESOURCE  |    1 |   135 |       3 (0)|
|* 5 |      DOMAIN INDEX                    | XDBHI_IDX     |      |       |            |
|  6 |     COLLECTION ITERATOR PICKLER FETCH|               |      |       |            |
|* 7 |    INDEX UNIQUE SCAN                 | XDB_PK_H_LINK |    1 |    28 |       0 (0)|
|* 8 |   INDEX UNIQUE SCAN                  | SYS_C003900   |    1 |    17 |       0 (0)|
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter(CAST("P"."SYS_NC00011$" AS VARCHAR2(100)) LIKE 'S%')
5 - access("XDB"."UNDER_PATH"(SYS_MAKEXML('8758D485E6004793E034080020B242C6', 734,
   "XMLEXTRA", "XMLDATA"),'/home/QUINE/PurchaseOrders/2002/Apr',9999)=1)
7 - access("H"."PARENT_OID"=SYS_OP_ATG(VALUE(KOKBF$),3,4,2) AND
   "H"."NAME"=SYS_OP_ATG(VALUE(KOKBF$),2,3,2))
8 - access("R2"."SYS_NC_OID$"=SYS_OP_ATG(VALUE(KOKBF$),3,4,2))
 
SYS_OP_BL2R
Appears to converts BLOB to RAW

Introduced in 9.0.1
sys_op_bl2b(<arg> IN BLOB) RETURN RAW;
conn pm/pm@pdbdev

SELECT sys_op_bl2r(TO_BLOB(dbms_lob.substr(ad_photo, 2000, 1001)))
FROM pm.print_media;
 
SYS_OP_CEG
Introduced in 6.0 sys_op_ceg(<arg1>, <arg2>) RETURN UNKNOWN;
SELECT sys_op_ceg('A',1) FROM dual;
                  *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got CHAR


-- so try a UDT

CREATE OR REPLACE TYPE message_t AUTHID DEFINER AS OBJECT(
id     NUMBER,
source VARCHAR2(30),
rx     VARCHAR2(30));
/

CREATE TABLE msgs (
testcol message_t);

INSERT INTO msgs VALUES (message_t(1, 'TEST', 'Thorazine'));
COMMIT;

SELECT sys_op_ceg(testcol,1) FROM msgs;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got UWCLASS.MESSAGE_T
 
SYS_OP_CL2C
Appears to converts CLOB to VARCHAR2 (Data Type 1)

Introduced in 9.2
sys_op_cl2c(<arg> IN CLOB) RETURN VARCHAR2;
conn pm/pm@pdbdev

SELECT sys_op_cl2c(ad_finaltext)
FROM print_media;

SELECT dump(sys_op_cl2c(ad_finaltext))
FROM print_media;

SELECT dump(ad_finaltext)
FROM print_media;
 
SYS_OP_COMBINED_HASH
Used in the gathering of extended stats by DBMS_STATS

Introduced in 11.1.0.6
sys_op_combined_hash(<col1>, <col2>) RETURN NUMBER;
conn uwclass/uwclass@pdbdev

CREATE TABLE t (
realcol1 VARCHAR2(20),
realcol2 VARCHAR2(20),
democol  NUMBER AS (sys_op_combined_hash('realcol1','realcol2')));

desc t

col data_default format a30

SELECT column_name, data_default, virtual_column
FROM user_tab_cols
WHERE table_name = 'T'
ORDER BY column_id;

INSERT INTO t (realcol1, realcol2) VALUES (1, 1);
COMMIT;

SELECT * FROM t;

col soch format 999999999999999999

SELECT sys_op_combined_hash('REALCOL1','REALCOL2') AS SOCH
FROM dual;

TRUNCATE TABLE t;

SELECT sys_op_combined_hash('REALCOL1','REALCOL2') AS SOCH
FROM dual;
 
SYS_OP_CONVERT
Converts CHAR to VARCHAR2 (Data type 96 to 1) as well as perform a character set conversion

Introduced in 9.2.0.1
sys_op_convert(<arg1 value> IN VARCHAR2, <arg2 source character set> IN VARCHAR2, <arg3 target character set> IN VARCHAR2) RETURN ...
SELECT 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
sys_op_convert('ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'US7ASCII', 'D8EBCDIC1141')
FROM dual;

SELECT dump('ABCDEFGHIJKLMNOPQRSTUVWXYZ'),
dump(sys_op_convert('ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'US7ASCII', 'D8EBCDIC1141'))
FROM dual;
 
SYS_OP_COUNTCHG
Aggregate function that counts the number of block changes as an index is used to visit table blocks: In essence the clusting factor.

The second parameter is automatically set by the value of DBMS_STATS.SET_TABLE_PREFS for TABLE_CACHED_BLOCKS.

Introduced in 9.2.0.1
sys_op_countchg(rowid, integer_between_1_and_255) RETURN NUMBER;
conn uwclass/uwclass@pdbdev

SELECT blocks
FROM dba_tables
WHERE table_name = 'AIRPLANES';

SELECT COUNT(DISTINCT dbms_rowid.rowid_block_number(rowid))
FROM airplanes;

SELECT sys_op_countchg(SUBSTRB(ROWIDTOCHAR("AIRPLANES".ROWID),1,15), 100)
FROM airplanes;
 
SYS_OP_CSR
Introduced 8.1.5 sys_op_csr(<arg1>, <arg2>) RETURN UNKNOWN;
Found in an Explain Plan Report posted on the net by James Morle as follows:

3 - filter(COUNT(sys_op_csr(sys_op_msr(COUNT(*)),0))>100)

[ Click Here ]
 
SYS_OP_C2C
Likely C2C indicates a Character To Character conversion.

Introduced in 10.1
sys_op_c2c(<arg1> IN INTEGER) RETURN CHAR(2);
SELECT sys_op_c2c(67), dump(67), dump(sys_op_c2c(67)) FROM dual;

SELECT sys_op_c2c(97), dump(97), dump(sys_op_c2c(97)) FROM dual;
 
SYS_OP_DESCEND
Descending Index support

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

SELECT sys_op_distinct(col1, col2)
FROM t;
 
SYS_OP_DUMP
Returns a UDT without the type declaration. No idea what ARG2 is for.

Introduced in 8.0
sys_op_dump(<arg> IN UDT) RETURN VARCHAR2;
CREATE OR REPLACE TYPE message_t AUTHID DEFINER AS OBJECT(
id     NUMBER,
source VARCHAR2(30),
rx     VARCHAR2(30));
/

CREATE TABLE msgs (
testcol message_t);

INSERT INTO msgs VALUES (message_t(1, 'TEST', 'Thorazine'));
COMMIT;

SELECT sys_op_iix(testcol, 2) FROM msgs;
SELECT sys_op_dump(testcol) FROM msgs;
 
SYS_OP_GROUPING
Introduced in 9.0.1 sys_op_grouping(<arg1>, <arg2>, <arg3>, <arg4>) RETURN UNKNOWN;
SELECT sys_op_grouping('1','1','1') FROM dual;
       *
ERROR at line 1:
ORA-00909: invalid number of arguments


SELECT sys_op_grouping('1','2','3','4') FROM dual;
*
ERROR:
ORA-00932: inconsistent datatypes: expected CHAR got B4


no rows selected
 
SYS_OP_GUID
Returns a GUID

Introduced in 8.0
sys_op_guid() RETURN CHAR(32);
SELECT sys_op_guid()
FROM dual;

SYS_GUID()
--------------------------------
57F95091F61841468F780D34FFCAB5DB


/

SYS_OP_GUID()
--------------------------------
4800A588263F4BF08F31D57CAFFBA383
 
SYS_OP_IIX
Returns a UDT with the type declaration. No idea what ARG2 is for.

Introduced in 8.1.5
SYS_OP_IIX(<arg1 IN UDT>, <arg2 IN NUMBER>) RETURN UDT
CREATE OR REPLACE TYPE message_t AUTHID DEFINER AS OBJECT(
id     NUMBER,
source VARCHAR2(30),
rx     VARCHAR2(30));
/

CREATE TABLE msgs (
testcol message_t);

INSERT INTO msgs VALUES (message_t(1, 'TEST', 'Thorazine'));
COMMIT;

SELECT * FROM msgs;

SELECT sys_op_dump(testcol, 2) FROM msgs;
SELECT sys_op_iix(testcol, 2) FROM msgs;
 
SYS_OP_ITR
Image TRansformation

Introduced in 8.1.6
sys_op_itr(<arg1, <arg2>) RETURN UNKNOWN;
SELECT sys_op_itr(1) FROM dual;
                  *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got NUMBER


SELECT sys_op_itr(SERVERS, 1) FROM dual
                  *
ERROR at line 1:
ORA-00904: "SERVERS": invalid identifier


SELECT sys_op_itr(TABLE, 1) FROM dual;
SELECT sys_op_itr(TABLE, 1) FROM dual
                  *
ERROR at line 1:
ORA-00936: missing expression
 
SYS_OP_KEY_VECTOR_CREATE
The demo at right, while invalid, proves the function's existence sys_op_key_vector_create(<arguments>) RETURN UNKNOWN;
SELECT sys_op_key_vector_create(NULL)
FROM dual;
SELECT sys_op_key_vector_create(NULL)
                                *
ERROR at line 1:
ORA-02017: integer value required


SELECT sys_op_key_vector_create(1)
  2* FROM dual;
FROM dual
*
ERROR at line 2:
ORA-00905: missing keyword
 
SYS_OP_KEY_VECTOR_SUCCEEDED
The demo at right, while invalid, proves the function's existence sys_op_key_vector_succeeded(<arguments>) RETURN UNKNOWN;
SELECT sys_op_key_vector_succeeded(NULL, 1)
FROM dual;
SELECT sys_op_key_vector_succeeded(NULL, 1)
                                   *
ERROR at line 1:
ORA-62034: Invalid KEY VECTOR
 
SYS_OP_KEY_VECTOR_USE
The demo at right, while invalid, proves the function's existence sys_op_key_vector_use(<arguments>) RETURN UNKNOWN;
SELECT sys_op_key_vector_use(NULL, 1)
FROM dual;
SELECT sys_op_key_vector_use(NULL, 1)
                             *
ERROR at line 1:
ORA-02017: integer value required
 
SYS_OP_LBID
Use the link at the bottom of the page
 
SYS_OP_LVL
It may be impossible to generate an error with this as long as you pass it at least one parameter

Introduced in 9.2
sys_op_lvl(<arg1>, ...) RETURN UNKNOWN;
SELECT '-' || sys_op_lvl(5) || '-' FROM dual;

SELECT sys_op_lvl(SYSTIMESTAMP, 'ABC', 99) FROM dual;

SELECT sys_op_lvl(1, 1, 2, 3, 4, 5, 7) FROM dual;
 
SYS_OP_MAKEOID
Undocumented

Introduced in 8.0
sys_op_makeoid(<arg1>, <arg2>, <arg3>) RETURN "<ADT_1>";
CREATE OR REPLACE TYPE o_type AUTHID CURRENT_USER AS OBJECT (n NUMBER, v VARCHAR2(20));
/

CREATE OR REPLACE TYPE t_type AS TABLE OF o_type;
/

CREATE OR REPLACE TYPE m_type AUTHID CURRENT_USER AS OBJECT (id NUMBER, t o_type);
/

CREATE TABLE som_demo (
n  NUMBER,
v  VARCHAR2(20),
id NUMBER);

CREATE VIEW v_som_demo OF m_type WITH OBJECT identifier(id) AS
SELECT id, o_type(n, v)
FROM som_demo;

desc v_som_demo

set describe depth all

desc v_som_demo

INSERT INTO som_demo VALUES (1,'one',1);
COMMIT;

col t format a20

SELECT * FROM v_som_demo;

SELECT sys_op_makeoid(v_som_demo, id)
FROM v_som_demo;
 
SYS_OP_MAP_NONNULL
Returns hex from row for comparison

Introduce in 8.1.5
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:

3 - filter(COUNT(SYS_OP_CSR(SYS_OP_MSR(COUNT(*)),0))>100)


[ Click Here ]
 
SYS_OP_NII
Returns a NULL image from an image

Introduced in 8.1.5
sys_op_nii(<arg1>, <arg2>) RETURN UNKNOWN;
conn uwclass/uwclass@pdbdev

SELECT sys_op_nii('SERVERS',  2) FROM dual;
SELECT sys_op_nii('SERVERS', 2) FROM dual
                  *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CHAR
 
SYS_OP_NOEXPAND
Undocumented and not found in the result set from the a query of v$sqlfn_metadata sys_op_noexpand(<table_name.column_name or column_name> IN VARCHAR2) RETURN UNKNOWN;
conn uwclass/uwclass@pdbdev

SELECT sys_op_noexpand('A') FROM dual;
 *
ERROR at line 1:
ORA-01747: invalid user.table.column, table.column, or column specification


SELECT sys_op_noexpand(srvr_id) FROM servers;

SELECT *
FROM servers
WHERE dump(srvr_id) <> dump(sys_op_noexpand(srvr_id));
 
SYS_OP_NUMTORAW
Converts a number to RAW

Introduced 9.0.1
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
 
SYS_OP_OPNSIZE
Undocumented but so far indistinguishable from VSIZE

Introduced in 6.0
sys_op_opnsize(<value> IN VARCHAR2) RETURN NUMBER;
sys_op_opnsize(<value> IN NUMBER) RETURN NUMBER;
sys_op_opnsize(<value> IN DATE) RETURN NUMBER;
SELECT sys_op_opnsize('Dan Morgan'), vsize('Dan Morgan') FROM dual;

SELECT sys_op_opnsize(99.999), vsize(99.999) FROM dual;

SELECT sys_op_opnsize(SYSDATE), vsize(SYSDATE) FROM dual;

SELECT sys_op_opnsize(SYSTIMESTAMP), vsize(SYSTIMESTAMP) FROM dual;
 
SYS_OP_PAR
Relates to the OLAP API

Introduced in 9.2.0.1
sys_op_par(<agr1>, <arg2>, <arg3>) RETURN UNKNOWN;
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


SELECT sys_op_pargid(1, 99) FROM dual;

SELECT sys_op_pargid(1, 4, 1, 1) FROM dual;

SELECT sys_op_pargid(1, 5, 2, 18, 99, 99, 1, 99, 99) FROM dual;
Overload 2 SELECT sys_op_pargid(1, 'ABC', 3) FROM dual;
Overload 3 SELECT sys_op_pargid(1, 'ABC', 'XYZ') FROM dual;
Overload 4 SELECT sys_op_pargid(1, 'ABC', SYSDATE) FROM dual;
Overload 5 SELECT sys_op_pargid(2, SYSDATE, 1, 7) FROM dual;
 
SYS_PLSQL_COUNT
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
 
SYS_PLSQL_CPU
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
 
SYS_PLSQL_IO
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
Converts RAW to NUMBER

Introduced in 9.0.1
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_OP_RPB
Returns the row number in the block given a rowid

Introduced 8.1.5
sys_op_rpb(rowid IN ROWID) RETURN INTEGER;
conn uwclass/uwclass@pdbdev

SELECT rowid, srvr_id
FROM servers
WHERE rownum < 11;

SELECT rowid, sys_op_rpb(rowid), srvr_id
FROM servers
WHERE rownum < 11;

SELECT AVG(sys_op_rpb(rowid))
FROM servers;

SELECT MAX(sys_op_rpb(rowid))
FROM servers;
 
SYS_OP_R2O
Undocumented

Introduced in 8.0
sys_op_r2o(<arg> IN REF) RETURN "<ADT_1>";
conn oe/oe@pdbdev

SELECT object_type
FROM user_objects
WHERE object_name = 'OC_ORDERS';

desc oc_orders

SELECT sys_op_r2o(CUSTOMER_REF)
FROM oc_orders
WHERE rownum = 1;
 
SYS_OP_TOSETID
Introduced in 8.0 sys_op_tosetid(<nested_table_column_name>) RETURN RAW(32);
conn uwclass/uwclass@pdbdev

CREATE OR REPLACE TYPE o_type AUTHID CURRENT_USER AS OBJECT (n number, v VARCHAR2(20));
/

CREATE OR REPLACE TYPE t_type AS TABLE OF o_type;
/

CREATE TABLE t (
rid  NUMBER(5),
col  t_type)
NESTED TABLE col STORE AS nested_tab;

desc t

set describe depth all

INSERT INTO t
(rid, col)
VALUES
(100, t_type(o_type(1, 'Daniel Morgan'), o_type(2, 'Tom Kyte')));
COMMIT;

SELECT rid, col
FROM t;

SELECT sys_op_tosetid(col) FROM t;
 
SYS_OP_TRTB
Reportedly related to trimming and padding strings: I can get it to pad but not with a lot of understanding of the parameters

Introduced in 8i
sys_op_trtb(
<string_to_pad>           IN VARCHAR2,
<arg2>                    IN INTEGER,
<length_of_output_string> IN INTEGER,
<arg4>                    IN INTEGER)
RETURN VARCHAR2;
SELECT '-' || sys_op_trtb('ABCD', ASCII(9), 6, 10) || '-' FROM dual;

SELECT ascii(substr(sys_op_trtb('A', 9, 10, 10),2,1)) FROM dual;

SELECT '-' || sys_op_trtb('ABCD', ASCII(9), 6, 10) || '-' FROM dual;

SELECT '-' || sys_op_trtb('ABCDEFGHIJKLMNOP', ASCII(9), 6, 10) || '-' FROM dual;

SELECT '-' || sys_op_trtb('ABCDEFGHIJKLMNOP', ASCII(9), 6, 10) || '-' FROM dual;
 
SYS_OP_UNDESCEND
Likely related to the introduction of descending indexes

Introduced in 8.1.5
sys_op_undescend(<arg> IN HEX) RETURN RAW;
SELECT sys_op_descend('0A') FROM dual;

SELECT sys_op_undescend('CFBEFF') FROM dual;
 
SYS_OP_VECAND
Likely based on Vector and XAND

Inroduced in 9.0.1
sys_op_vecand(<expression> IN VARCHAR2, <expression> IN VARCHAR2) RETURN VARCHAR2;
SELECT sys_op_vecand('0A','10')
FROM dual;
 
SYS_OP_VECBIT
Likely based on Vector and Bit

Introduced in 9.0.1
sys_op_vecbit(<expression> IN VARCHAR2, <expression> IN NUMBER) RETURN BINARY_INTEGER;
SELECT sys_op_vecbit('3',0), sys_op_vecbit('3',1), sys_op_vecbit('3',2)
FROM dual;
 
SYS_OP_VECOR
Likely based on Vector and Or

Introduced in 9.0.1
sys_op_vecor(<expression> IN VARCHAR2, <expression> IN VARCHAR2) RETURN VARCHAR2;
SELECT sys_op_vecor('0A','10')
FROM dual;
 
SYS_OP_VECXOR
Likely based on Vector and XOr

Introduced in 9.0.1
sys_op_vecxor(<expression> IN VARCHAR2, <expression> IN VARCHAR2) RETURN VARCHAR2;
SELECT sys_op_vecxor('ff','10')
FROM dual;
 
SYS_OP_VERSION
Decodes the version number of X$KSUSECON and likely other X$ structures

Introduced 11.1.0.6
sys_op_version(<column_name> IN UDT) RETURN VARCHAR2;
SELECT DISTINCT ksuseunm, ksuseclvsn, sys_op_version(ksuseclvsn)
FROM x$ksusecon;
 
SYS_OP_XPTHATG
Introduced 10.2.0.1 sys_op_xpthatg(<arg1>, <arg2>) RETURN UNKNOWN;
SELECT sys_op_xpthatg(1) FROM dual;
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
 
SYS_OP_XPTHIDX
May relate to XPath Indexing

Introduced 10.1
sys_op_xpthidx(<arg>) RETURN UNKNOWN;
SELECT sys_op_xpthidx(1) FROM dual;
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -


-- played a bit with XMLType without success
 
SYS_OP_XPTHOP
Introduced in 10.1 sys_op_xpthop(<arg1>, <arg2>) RETURN UNKNOWN;
SELECT sys_op_xpthop(1) FROM dual;
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
 
SYS_OP_XTXT2SQLT
Introduced in 10.1 sys_op_xtxt2sqlt(<arg1>, <arg2>) RETURN UNKNOWN;
SELECT sys_op_xtxt2sqlt(1,2) FROM dual;
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
 
SYS_OP_VVD
Undocumented and apparently unrelated to SYS_OP_DUMP and SYS_OP_IIX

Introduced in 9i (8.2)
sys_op_vvd(<arg1>, <arg2>) RETURN UNKNOWN;
SELECT sys_op_vvd('T_TYPE',1) FROM dual;
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got CHAR


SELECT sys_op_vvd(testcol, 1) FROM msgs;
SELECT sys_op_vvd(testcol, 1) FROM msgs
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got UWCLASS.MESSAGE_T
 
SYS_ORDERKEY_DEPTH
Returns the depth of a value in a document 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;
 
SYS_XMLCONV
Introduced in 9.2.0.1 sys_xmlconv(<arg1>, <arg2>, <arg3>, <arg4>, <arg5>, <arg6>, <arg7>, <arg8>) RETURN UNKNOWN;
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_XMLGEN
Generates XML from the input object type column name

Introduced in 9.0.1
sys_xmlgen(<column_name> IN UDT) RETURN XMLTYPE;
conn uwclass/uwclass@pdbdev

CREATE OR REPLACE TYPE message_t AUTHID DEFINER AS OBJECT(
id     NUMBER,
source VARCHAR2(30),
rx     VARCHAR2(30));
/

CREATE TABLE msgs (
testcol message_t);

INSERT INTO msgs VALUES (message_t(1, 'TEST', 'Thorazine'));

COMMIT;

SELECT sys_xmlgen(testcol) FROM msgs;
 
SYS_XQCODEPEQ
Returns 1 if values match, 0 if they do not

Introduced in 10.1
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
 
SYS_XQED4URI
Introduced in 10.1 sys_xqed4uri(<arg> IN VARCHAR2) RETURN VARCHAR2;
SELECT sys_xqed4uri('<CODE>') FROM dual;
 
SYS_XQENDSWITH
Returns 0 if false and 1 if true depending on whether string1 end with string2. The reference indicates 3 args but it seems to only functional with 2.

Introduced in 10.1
sys_xqerrh(<string1> IN VARCHAR2, <string2> IN VARCHAR2) RETURN NUMBER;
SELECT sys_xqendswith('Dan Morgan', 'n') FROM dual;

SELECT sys_xqendswith('Dan Morgan', 'a') FROM dual;

SELECT sys_xqendswith('Dan Morgan', 'n Morgan') FROM dual;
 
SYS_XQERR
Introduced in 10.1 sys_xqerr(<arg1>, <arg2>, <arg3>)  RETURN UNKNOWN;
SELECT sys_xqerr('A', 'A', 'A') FROM dual;
SELECT sys_xqerr('A', 'A', 'A') FROM dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
 
SYS_XQERRH
Introduced in 10.1 sys_xqerrh(<unknown> IN BINARY_DOUBLE)  RETURN UNKNOWN;
SELECT TO_BINARY_DOUBLE(3004) FROM dual;

SELECT sys_xqerrh(TO_BINARY_DOUBLE(3004)) FROM dual;
 
SYS_XQLANG
Introduced in 10.1 sys_xqlang(<arg>) RETURN UNKNOWN;
SELECT sys_xqlang(1) FROM dual;
                          *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 13608
Session ID: 368 Serial number: 99

ERROR:
ORA-03114: not connected to ORACLE
 
SYS_XSID_TO_RAW
Converts an XSID to RAW sys_xsid_to_raw(<arguments>) RETURN RAW;
SELECT sys_xsid_to_raw(ixs_id)
FROM ctxsys.dr$index_set;

SYS_XSID_TO_RAW(
----------------
000000000000040D
 
TBL$OR$IDX$PART$NUM
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
 
XMLEXISTS2
Introduced 11.1.0.6 xmlExists2(<arg>) RETURN UNKNOWN;
TBD
 
XMLISNODE
Introduced 9.2.0.1 xmlIsNode(<arg>) RETURN UNKNOWN;
TBD
 
XMLTOJSON
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
 
XMLTRANSFORMBLOB
Introduced 10.2.0.1 xmlTransformBlob(<arg>) RETURN UNKNOWN;
TBD
 
XML2OBJECT
Introduced 11.1.0.6 xml2Object(<arg>) RETURN UNKNOWN;
TBD

Related Topics
Built-in Functions
Built-in Packages
DBMS_PDB
Indexes
Partitioned Tables
SKIP LOCKED
SYS_OP_LBID
System Events
What's New In 21c
What's New In 23c

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