| ADJ_DATE |
| Truncates a timestamp to "seconds" precision |
adj_date(<expression> IN DATE) RETURN DATE |
CREATE TABLE t(
dcol TIMESTAMP);
INSERT INTO t VALUES (SYSTIMESTAMP);
SELECT adj_date(dcol), dcol, SYSTIMESTAMP
FROM t;
SELECT dump(adj_date(dcol)), dump(dcol), dump(SYSTIMESTAMP)
FROM t; |
| |
| CO_AUTH_IND |
| Undocumented |
co_auth_ind(...) RETURN ... |
SQL> SELECT co_auth_ind(1) FROM dual;
SELECT co_auth_ind(1) FROM dual
*
ERROR at line 1:
ORA-01760: illegal argument for function |
| |
| CSCONVERT |
| Appears to be related to Character Set conversion |
csconvert(...) RETURN ... |
CREATE TABLE t (
vchar VARCHAR2(20),
nchar NVARCHAR2(20));
INSERT INTO t VALUES ('??', n'??');
SELECT * FROM t;
set linesize 121
col a format a25
col b format a25
col c format a25
col d format a25
SELECT dump(vchar) a, dump(csconvert(vchar,'NCHAR_CS'),16) b,dump(vchar,16) c, dump(nchar,16) d
FROM t;
SELECT dump(csconvert(vchar,'CHAR_CS'),16) a,dump(vchar,16) b,dump(nchar,16) c, dump(nchar,16) d
FROM t;
SELECT dump(csconvert(nchar,'NCHAR_CS'),16) a,dump(vchar,16) b,dump(nchar,16) c, dump(nchar,16) d
FROM t;
SELECT dump(csconvert(nchar,'CHAR_CS'),16) a,dump(vchar,16) b,dump(nchar,16) c, dump(nchar,16) d
FROM t; |
| |
| CURRENTV |
| Related to the Model Clause returning the current value of a dimension |
currentv(<arg>) RETURN ... |
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 (2010, 10, 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);
SELECT * FROM uw_sales;
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[1, 2004] =
ROUND(sales_amount[CURRENTV(), 2003] * 1.25, 2))
ORDER BY prd_type_id, year, month; |
| |
| DENSE_RANKM |
| Introduced in 9i |
dense_rankm(...) RETURN ... |
conn oe/oe
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>; |
CREATE OR REPLACE FUNCTION test(x VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN x;
END;
/
alter session set flagger=FULL;
CREATE OR REPLACE FUNCTION test(x VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN x;
END;
/
alter session set flagger=OFF;
alter session set flagger=OFF;
CREATE OR REPLACE FUNCTION test(x VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN x;
END;
/ |
| |
| FIRSTM |
| Introduced in 9i |
firstm(...) RETURN ... |
SELECT firstm(1,1) FROM dual;
*
ERROR at line 1:
ORA-00909: invalid number of arguments |
| |
| MAKEXML |
| Introduced in 9i |
makexml(<arg>) RETURN ... |
-- 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'); |
| |
| MERGE$ACTIONS |
Introduced in 8i
Returns a string with 'B' in position n if arg1[n] <> arg2[n], otherwise returns the matched character. |
merge$actions(<arg1> IN VARCHAR2, <arg2> IN VARCHAR2) 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 merge$actions(SYSDATE, SYSDATE+35) FROM dual; |
| |
| ORA_NAME_LIST_T |
| ORA_NAME_LIST_T |
TYPE ora_name_list_t IS TABLE OF VARCHAR2(64); |
CREATE OR REPLACE TRIGGER test
AFTER GRANT
ON DATABASE
DECLARE
user_list dbms_standard.ora_name_list_t;
number_of_grantees PLS_INTEGER;
BEGIN
IF (ora_sysevent = 'GRANT') THEN
number_of_grantees := ora_grantee(user_list);
dbms_output.put_line(number_of_grantees);
FOR i IN 1 .. number_of_grantees LOOP
dbms_output.put_line(user_list(i));
END LOOP;
END IF;
END;
/
set serveroutput on
GRANT select ON servers TO scott;
GRANT all ON servers TO scott; |
| |
| PERCENT_RANKM |
| Introduced in 9i |
percent_rankm(...) RETURN ... |
SELECT percent_rankm(1,1) FROM dual;
*
ERROR at line 1:
ORA-00909: invalid number of arguments |
| |
| RANKM |
| Introduced in 9i |
rankm(...) RETURN ... |
SELECT rankm(1,1) FROM dual;
*
ERROR at line 1:
ORA-00909: invalid number of arguments |
| |
| 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(...) RETURN ... |
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 9i |
sys_dom_compare(<arg1>, <arg2>) RETURN ... |
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; |
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 arg>, arg2, arg3) RETURN ... |
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 |
| |
| SYS_ET_IMAGE_TO_BLOB |
| Introduced in 10.1 |
sys_et_image_to_blob(<blob arg>, arg2, arg3) RETURN BLOB; |
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(... IN RAW) RETURN BLOB; |
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 may relate to DBMS_FBT which is used for Flashback Table |
sys_fbt_insdel RETURN ... |
EXPLAIN PLAN FOR
SELECT sys_fbt_insdel FROM dual;
SELECT * FROM TABLE(dbms_xplan.display);
---------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01
|
--------------------------------------------------------------- |
| |
| SYS_MAKEXML |
|
Undocumented. This demo is from here. |
sys_makexml(...) RETURN ... |
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 |
Introduced in 9.2
Appears to converts BLOB to RAW |
sys_op_bl2b(<arg> IN CLOB) RETURN ... |
SELECT sys_op_bl2r(TO_BLOB(dbms_lob.substr(ad_photo, 2000, 1001)))
FROM pm.print_media; |
| |
| SYS_OP_CEG |
| Introduced in 9.2 |
sys_op_ceg(<arg>, <arg>) RETURN ... |
SELECT sys_op_ceg('A',1) FROM dual;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got CHAR |
| |
| SYS_OP_CL2C |
Introduced in 9.2
Appears to converts CLOB to VARCHAR (Data Type 1) |
sys_op_cl2c(<arg> IN CLOB) RETURN ... |
conn pm/pm
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. |
sys_op_combined_hash(<col1>, <col2>) RETURN ... |
CREATE TABLE t (
realcol1 VARCHAR2(20),
realcol2 VARCHAR2(20),
democol NUMBER AS (sys_op_combined_hash('realcol1','realcol2')));
desc t
SELECT column_name, data_default
FROM user_tab_cols
WHERE table_name = 'T';
INSERT INTO t (realcol1, realcol2) VALUES (1, 1);
SELECT * FROM t;
SELECT sys_op_combined_hash('REALCOL1','REALCOL2') FROM dual;
TRUNCATE TABLE t;
SELECT sys_op_combined_hash('REALCOL1','REALCOL2') FROM dual; |
| |
| SYS_OP_CONVERT |
| Introduced in 9.2. Converts CHAR to VARCHAR (Data type 96 to 1) as well as perform a character set conversion |
sys_op_convert(<arg1>, <arg2>) 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 |
| Counts the number of blocks in a table but the results between these two methods
were different when tested with sys.tab$ and very different from looking at BLOCKS in dba_tables and dba_segments. |
sys_op_countchg(rowid, integer_between_1_and_255) RETURN ... |
conn uwclass/uwclass
SELECT COUNT(DISTINCT dbms_rowid.rowid_block_number(rowid))
FROM airplanes;
SELECT sys_op_countchg(SUBSTRB(ROWIDTOCHAR("AIRPLANES".ROWID),1,15),1)
FROM airplanes;
EXPLAIN PLAN FOR
SELECT COUNT(DISTINCT dbms_rowid.rowid_block_number(rowid))
FROM airplanes;
SELECT * FROM TABLE(dbms_xplan.display);
EXPLAIN PLAN FOR
SELECT sys_op_countchg(SUBSTRB(ROWIDTOCHAR("AIRPLANES".ROWID),1,15),1)
FROM airplanes;
SELECT * FROM TABLE(dbms_xplan.display);
-- note the difference between the plans. |
| |
| SYS_OP_CSR |
| Unknown |
sys_op_csr(...) RETURN ... |
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 |
Introduced in 10.1
Likely C2C indicates a Character To Character convesion |
sys_op_c2c(<arg> INTEGER); |
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
Appears to be overloaded and converts whatever it receives into Data Type 23 which is not in DBMS_TYPES.
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 ... |
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 |
sys_op_distinct(col1 IN NUMBER, col2 IN NUMBER) RETURN ... |
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); |
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); |
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 |
| Undocumented |
sys_op_dump(<arg> IN UDT) RETURN ... |
SELECT sys_op_dump(1) FROM dual;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got NUMBER |
| |
| SYS_OP_GROUPING |
| Introduced in 9i |
sys_op_grouping(...) RETURN ... |
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 |
| Undocumented |
sys_op_guid() RETURN ... |
SELECT sys_guid()
FROM dual;
/ |
| |
| SYS_OP_IIX |
IMAGE=>IMAGE
Introduced in 9.2 |
SYS_OP_IIX(<arg1>, <arg2>) RETURN ... |
SELECT sys_op_iix('A', 1) FROM dual;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got CHAR |
| |
| SYS_OP_ITR |
Image TRansformation
Introduced in 10.1 |
sys_op_itr(...) RETURN ... |
SELECT sys_op_itr(1) FROM dual;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got NUMBER |
| |
| SYS_OP_LBID |
| Use the link at the bottom of the page |
| |
| SYS_OP_LVL |
| Introduced 9.2. It may be impossible to generate an error with this as long as you pass it at least one parameter |
sys_op_lvl(<arg1>, ...) RETURN ... |
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 |
sys_op_makeoid(<arg1>, <arg2>, <arg3>) RETURN ... |
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);
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
First introduce in 8i |
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
-- likely will handle any SQL data type |
conn scott/tiger
set linesize 121
SELECT * FROM emp;
SELECT comm, sys_op_map_nonnull(comm)
FROM emp;
conn uwclass/uwclass
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 |
SYS_OP_MSR(...) RETURN ... |
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 |
sys_op_nii(<arg1>, <arg2>) RETURN ... |
SELECT sys_op_nii('A',2) FROM dual;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got NUMBER |
| |
| SYS_OP_NOEXPAND |
| Undocumented |
sys_op_noexpand(<column_name> IN VARCHAR2) RETURN ... |
conn uwclass/uwclass
SELECT sys_op_noexpand('A') FROM dual;
SELECT sys_op_noexpand(srvr_id) FROM servers;
SELECT dump(srvr_id), dump(sys_op_noexpand(srvr_id)) FROM servers; |
| |
| SYS_OP_NUMTORAW |
| Undocumented |
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 |
sys_op_oidvalue(<... unknown data type ...> RETURN ... |
SELECT sys_op_oidvalue('AW_PROP$', 1) FROM dual;
*
ERROR at line 1:
ORA-00903: invalid table name |
| |
| SYS_OP_OPNSIZE |
| Undocumented but so far indistinguishable from VSIZE |
sys_op_opnsize(<value> IN VARCHAR2) RETURN NUMBER |
SELECT sys_op_opnsize('Dan Morgan'), vsize('Dan Morgan') FROM dual;
SELECT sys_op_opnsize(SYSDATE), vsize(SYSDATE) FROM dual; |
| |
| SYS_OP_PAR |
| Introduced 9.2 and relates to the OLAP API |
sys_op_par(<agr1>, <arg2>, <arg3>) RETURN ... |
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 |
Introduced 9.2
Overload 1 |
sys_op_pargid(<agr1>, <arg2>) RETURN ... |
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(1, SYSDATE) FROM dual; |
| |
| SYS_OP_RAWTONUM |
| Undocumented |
sys_op_rawtonum(<expression> IN VARCHAR2) 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 |
sys_op_rpb(rowid IN ROWID) RETURN INTEGER |
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 |
sys_op_r2o(<arg> IN REF) |
conn oe/oe
desc oc_orders
SELECT sys_op_r2o(CUSTOMER_REF)
FROM oc_orders
WHERE rownum = 1; |
| |
| SYS_OP_TOSETID |
| Undocumented |
sys_op_tosetid(<nested_table_column_name>) RETURN VARCHAR2 |
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')));
SELECT rid, col
FROM t;
SELECT sys_op_tosetid(col)
FROM t; |
| |
| SYS_OP_TRTB |
Introduced in 8i
Reportedly related to trimming and padding strings |
sys_op_trtb(<arg> IN VARCHAR2, <arg> IN INTEGER) RETURN VARCHAR2 |
SELECT sys_op_trtb('A', 2) FROM dual;
SELECT sys_op_trtb(' A ', -1) FROM dual;
SELECT LENGTH(sys_op_trtb(' A ', -1)) FROM dual;
SELECT sys_op_trtb(' A ', 999999999) FROM dual;
SELECT LENGTH(sys_op_trtb(' A ', 999999999)) FROM dual;
SELECT sys_op_trtb(' A ', 9999999999) FROM dual; |
| |
| SYS_OP_UNDESCEND |
| Introduced in 10.1 |
sys_op_undescend(unknown BINARY) RETURN ... |
SELECT sys_op_descend('0A') FROM dual;
SELECT sys_op_undescend('CFBEFF') FROM dual; |
| |
| SYS_OP_VECAND |
| Likely based on Vector and XAND |
sys_op_vecand(<expression> IN VARCHAR2, <expression> IN VARCHAR2) RETURN VARCHAR2 |
SELECT sys_op_vecand('ff','10')
FROM dual; |
| |
| SYS_OP_VECBIT |
| Likely based on Vector and Bit |
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 |
sys_op_vecor(<expression> IN VARCHAR2, <expression> IN VARCHAR2) RETURN VARCHAR2 |
SELECT sys_op_vecor('ff','10')
FROM dual; |
| |
| SYS_OP_VECXOR |
| Likely based on Vector and XOR |
sys_op_vecxor(<expression> IN VARCHAR2, <expression> IN VARCHAR2) RETURN VARCHAR2 |
SELECT sys_op_vecxor('ff','10')
FROM dual; |
| |
| SYS_OP_VERSION |
| Found in oracle.exe |
sys_op_version(<column_name> IN VARCHAR2) RETURN VARCHAR2 |
SELECT DISTINCT ksuseunm, ksuseclvsn, sys_op_version(ksuseclvsn)
FROM x$ksusecon; |
| |
| SYS_OP_XPTHATG |
| Introduced in 10.1 |
sys_op_xpthatg(...) RETURN ... |
SELECT sys_op_xpthatg(1) FROM dual;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got - |
| |
| SYS_OP_XPTHIDX |
| Introduced in 10.1 |
sys_op_xpthidx(...) RETURN ... |
SELECT sys_op_xpthidx(1) FROM dual;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got - |
| |
| SYS_OP_XPTHOP |
| Introduced in 10.1 |
sys_op_xpthop(...) RETURN ... |
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(...) RETURN ... |
SELECT sys_op_xtxt2sqlt(1,2) FROM dual;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got - |
| |
| SYS_OP_VVD |
| Introduced in 9i |
sys_op_vvd(...) RETURN ... |
SELECT sys_op_vvd('T_TYPE',1) FROM dual;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got CHAR |
| |
| SYS_XMLCONTAINS |
| Introduced in 10.1 |
sys_xmlcontains(<arg1>, <arg2>) RETURN ... |
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 9i |
sys_xmlconv(<arg1>, <arg2>, <arg3>, <arg4>, <arg5>, <arg6>, <arg7>, <arg8>) RETURN ... |
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 string |
sys_xmlgen(<string> IN VARCHAR2, <unknown> IN UDT) RETURN VARCHAR2; |
SELECT sys_xmlgen('Dan Morgan') FROM dual;
SELECT sys_xmlgen('Dan Morgan', 'MyTag') FROM dual;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got CHAR |
| |
| SYS_XQCODEPEQ |
| Returns 1 if values match, 0 if they do not |
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 |
| Undocumented |
sys_xqdoc(...) RETURN ... |
SELECT sys_xqdoc('A') FROM dual;
*
ERROR:
ORA-31001: Invalid resource handle or path name "A"
no rows selected |
| |
| SYS_XQED4URI |
| Undocumented |
sys_xqed4uri(<unknown> 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 |
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 |
| Undocumented |
sys_xqerr(...) RETURN ... |
SELECT sys_xqerr('A') FROM dual;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got - |
| |
| SYS_XQERRH |
| Undocumented |
sys_xqerrh(<unknown> IN BINARY_DOUBLE) RETURN ... |
SELECT TO_BINARY_DOUBLE(3004) FROM dual;
SELECT sys_xqerrh(TO_BINARY_DOUBLE(3004)) FROM dual; |
| |
| SYS_XQLANG |
| Undocumented |
sys_xqlang(...) RETURN ... |
SELECT sys_xqlang(1) FROM dual;
SELECT SYS_XQLANG(1) FROM dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2580
Session ID: 141 Serial number: 657 |
| |
| TBL$OR$IDX$PART$NUM |
| Found by Jonathan Lewis and noted here |
tbl$or$idx$part$num(<table_name>, <?>, <value>) RETURN ... |
SELECT DISTINCT TBL$OR$IDX$PART$NUM(BBUKDW.VISIT_TX, 0, CALENDAR_DT)
FROM (
SELECT D.CALENDAR_DT CALENDAR_DT
FROM BBUKDW.JPL_DAY D
WHERE D.FINANCIAL_WEEK_ID>=200218
AND D.FINANCIAL_WEEK_ID <=200222)
ORDER BY 1;
SELECT DISTINCT TBL$OR$IDX$PART$NUM(BBUKDW.VISIT_TX, 0, CALENDAR_DT)
*
ERROR at line 1:
ORA-14040: inadequate number of arguments passed to tbl$or$idx$part$num |
