Undocumented Oracle
Version 12.1.0.1

General Information
Find undocumented functions. This statement produces a list that must be further edited by hand SELECT name, minargs, maxargs, datatype, version, analytic, aggregate, usage
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);

SELECT adj_date(dcol), 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;
 
ANTI-OBJECTS
Non-Existent Objects Tom Kyte has referred to them as ANTI_OBJECTS so it is reasonable name to use here in the Library. Tom has written:
"they are anti-objects created when you reference a public synonym. for the invalidations/dependency to work -- we need something to hook onto. These non-existent (i call them anti-objects) provide that."
conn / as sysdba

col owner format a20
col name format a20
col referenced_owner format a20
col referenced_name format a20

SELECT owner, name, type, referenced_owner, referenced_name
FROM dba_dependencies
WHERE referenced_type = 'NON-EXISTENT';
 
CONTAINERS (new in 12.1) (replaces CDB$VIEW in 12.1.0.2)
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

SELECT property_name, con_id
FROM CONTAINERS(database_properties)
WHERE rownum < 6;

SQL> SELECT property_name, con_id
2 FROM containers(database_properties)
3 WHERE rownum < 6;

PROPERTY_NAME                  CON_ID
----------------------------- -------
DICT.BASE                           1
DEFAULT_TEMP_TABLESPACE             1
DEFAULT_PERMANENT_TABLESPACE        1
DEFAULT_EDITION                     1
Flashback Timestamp TimeZone        1
 
CO_AUTH_IND
Undocumented

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

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

SQL> 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'??');

SELECT * 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;
SQL> select cume_distm(15500, .05) within group
   2 (order by salary, commission_pct) cume_dist_of_15500
   3 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 (new 12.1)
API for dbms_pdb.exec_as_oracle_script

Introduced in 12.1.0.1		 -- full source code

CREATE OR REPLACE NONEDITIONABLE PROCEDURE sys.dbms_pdb_exec_sql(sql_stmt IN VARCHAR2) 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@pdborcl

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 VARCHAR2) RETURN VARCHAR2
AUTHID DEFINER IS
BEGIN
  RETURN x;
END;
/

ALTER SESSION SET flagger=FULL;

CREATE OR REPLACE FUNCTION test(x VARCHAR2) RETURN VARCHAR2
AUTHID DEFINER IS
BEGIN
  RETURN x;
END;
/

ALTER SESSION SET flagger=OFF;

CREATE OR REPLACE FUNCTION test(x VARCHAR2) RETURN VARCHAR2
AUTHID DEFINER IS
BEGIN
  RETURN x;
END;
/
 
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
 
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;

SELECT testcol
FROM msgs;

SQL> SELECT object2xml(testcol)
   2 FROM msgs;

OBJECT2XML(TESTCOL)
------------------------------------------------------------------------
<MESSAGE_T><ID>1</ID><SOURCE>TEST</SOURCE><RX>Thorazine</RX></MESSAGE_T>
 
ORA_CHECK_ACL (new 12.1)
Unknown ora_check_acl(XS_Operator IN HEX_NUMBER, arg2, arg3);
SELECT ora_check_acl(2147483661,  2147483653, NULL) FROM dual;
                     *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected BINARY got NUMBER
 
ORA_CHECK_PRIVILEGE (new 12.1)
Unknown ora_check_privilege(arg1, arg2);
SELECT ora_check_privilege('SCOTT', 'SYSDBA') FROM dual;

SELECT ora_check_privilege('SYSTEM', 'SELECT ANY TABLE') FROM dual;

SELECT ora_check_privilege('SELECT ANY TABLE', 'SYSTEM') FROM dual;
 
ORA_CLUSTERING (new 12.1)
Unknown ora_clustering(arg1, arg2, arg3, arg4);
SELECT ora_clustering(1, 1) FROM dual;

SELECT ora_clustering('ORACLE', 'ACE') FROM dual;
 
ORA_NAME_LIST_T
Undocumented TYPE ora_name_list_t IS TABLE OF VARCHAR2(64);
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_RAWCOMPARE (new 12.1)
Unknown: Though one might expect comparison of RAW values ora_rawcompare(<arg1>, <arg2>, <arg3>) RETURN NUMBER;
SELECT ora_rawcompare(1, 1, 1)
FROM dual;

SELECT ora_rawcompare('A', SYSDATE, 5)
FROM dual;
 
ORA_RAWCONCAT (new 12.1)
Unknown; Though one might expect concatenation of RAW values ora_rawconcat(<arg1 IN NUMBER, arg2 IN NUMBER) RETURN BINARY_INTEGER;
SELECT ora_rawconcat(1, 1)
FROM dual;
 
PART$NUM$INST
Introduced in 6.0 part$num$inst(<arg>) RETURN UNKNOWN;
SELECT part$num$inst('sh.sales')
FROM dual;
 
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
 
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
 
STANDARD_HASH
Hashes an input value standard_hash(<arg> IN VARCHAR2) RETURN VARCHAR2;
standard_hash(<arg> IN NUMBER) RETURN VARCHAR2;
standard_hash(<arg> IN DATE) RETURN VARCHAR2;
standard_hash(<arg> IN TIMESTAMP) RETURN VARCHAR2;
conn / as sysdba

desc standard_hash

SELECT owner
FROM dba_objects
WHERE object_name = 'STANDARD_HASH';

SELECT standard_hash('Dan Morgan')
FROM dual;

SELECT standard_hash(42)
FROM dual;

SELECT standard_hash(SYSDATE)
FROM dual;
 
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@pdborcl

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

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

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

SQL> 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@pdborcl

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

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

Other than that I have no idea what it is actually doing: Research for another day.

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

Appears to be 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_guid()
FROM dual;

/
 
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

SQL> 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_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@pdborcl

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

SQL> SELECT sys_op_nii('SERVERS',  2) FROM dual;
SELECT sys_op_nii(SERVERS, 2) FROM dual
*
ERROR at line 1:
ORA-00904: "SERVERS": invalid identifier
 
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;
SQL> 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

SQL> 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
SQL> 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;
SQL> 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_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@pdborcl

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

SQL> 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_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;
SQL> 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
 
TBL$OR$IDX$PART$NUM
Returns the appropriate partition number for one or more values

Introduced in version 6.0		 tbl$or$idx$part$num(<partitioned_table_name>, 0, d#, p#, <partition_value>) RETURN UNKNOWN;
SELECT tbl$or$idx$part$num(UWCLASS.SERV_INST, 0, 0, 0, 0)
FROM dual;
SELECT DISTINCT TBL$OR$IDX$PART$NUM(UWCLASS.SERV_INST, 0, 0, 0, 0)
                                            *
ERROR at line 1:
ORA-14091: table must be partitioned

WITH q AS (
  SELECT partition_position, partition_name
  FROM dba_tab_partitions
  WHERE table_name = 'SALES')
SELECT SUM(unit_price)
FROM sh.costs, q
WHERE q.partition_position =
  tbl$or$idx$part$num(SH.SALES, 0, 0, 65536, '01-JAN-2002');
 
TO_ACLID (new 12.1)
Converts an unknown value to an ACL_ID to_aclid(<arg> IN VARCHAR2) RETURN VARCHAR2;
SQL> SELECT to_aclid('ZZYZX') FROM dual;
                                   *
ERROR at line 1:
ORA-46114: ACL name ZZYZX not found.
 
XML2OBJECT
Introduced 11.1.0.6 xml2Object(<arg>) RETURN UNKNOWN;
TBD
 
XMLEXISTS2
Introduced 11.1.0.6 xmlExists2(<arg>) RETURN UNKNOWN;
TBD
 
XMLISNODE
Introduced 9.2.0.1 xmlIsNode(<arg>) RETURN UNKNOWN;
TBD
 
XMLTRANSFORMBLOB
Introduced 10.2.0.1 xmlTransformBlob(<arg>) RETURN UNKNOWN;
TBD
 
XS_SYS_CONTEXT
Introduced 11.1.0.6 xs_sys_context(<arg1>, <arg2>) RETURN UNKNOWN;
TBD

Related Topics
DBMS_PDB
Functions
Indexes
Partitioned Tables
SKIP LOCKED
SYS_OP_LBID
System Events

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