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