| 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; |
| |
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
col property_name format a30
SELECT property_name, con_id
FROM CONTAINERS(database_properties)
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'??');
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; |
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 |
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@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 |
| |
| 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
SQL> SELECT testcol AS TESTCOL, object2xml(testcol) AS O2XMLCOL
2 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_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_RAWCOMPARE |
| Unknown: Though one might expect 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;
SELECT ora_rawcompare('A', SYSDATE, 5)
FROM dual; |
| |
| PART$NUM$INST |
| Introduced in 6.0 |
part$num$inst(<arg>) RETURN UNKNOWN; |
SQL> SELECT part$num$inst(3)
2* 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
SQL> CREATE OR REPLACE VIEW int$pdb_local
2 PDB_LOCAL_ONLY SHARING=EXTENDED DATA AS
3 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));
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@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
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_KEY_VECTOR_CREATE |
| The demo at right, while invalid, proves the function's existence |
sys_op_key_vector_create(<arguments>) RETURN UNKNOWN; |
SQL> SELECT sys_op_key_vector_create(NULL)
2 FROM dual;
SELECT sys_op_key_vector_create(NULL)
*
ERROR at line 1:
ORA-02017: integer value required
SQL> 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 (new 12.1) |
| 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 (new 12.1) |
| 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
SQL> 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; |
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@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
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 |
| |
| SYS_XSID_TO_RAW (new 12.1) |
| Converts an XSID to RAW |
sys_xsid_to_raw(<arguments>) RETURN RAW; |
SQL> SELECT sys_xsid_to_raw(ixs_id)
2 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 |
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'); |
| |
| 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 |
