CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Undocumented Oracle
Version 11.1
 
ADJ_DATE
Undocumented

Appears to cast a timestamp to a date
adj_date(<expression> IN DATE) RETURN DATE
CREATE TABLE t(
dcol TIMESTAMP);

INSERT INTO t VALUES (SYSTIMESTAMP);

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

Undocumented

Appears to be related to Character Set conversion
csconvert
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 CURRENTV(<arg>)
SELECT currentv(1) FROM dual;
       *
ERROR at line 1:
ORA-32644: this function is not allowed outside of MODEL clause
 
DENSE_RANKM
Undocumented
Introduced in 9i
DENSE_RANKM(
SELECT dense_rankm(1,1) FROM dual;
       *
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
Undocumented
Introduced in 9i
FIRSTM(
SELECT firstm(1,1) FROM dual;
       *
ERROR at line 1:
ORA-00909: invalid number of arguments
 
MAKEXML
Undocumented
Introduced in 9i
MAKEXML(<arg>)
-- 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

Undocumented
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

ORA_SQL_TXT retrieves
the SQL text of the triggering statement
ora_name_list_t & oora_sql_txt
CREATE OR REPLACE TRIGGER det_grant
AFTER grant
ON schema

DECLARE
 priv_list ora_name_list_t;
 user_list ora_name_list_t;

 l_str VARCHAR2(255);
 l_job NUMBER;
BEGIN
  IF ora_sysevent = 'GRANT'
  AND ora_privilege_list(priv_list) > 0
  AND ora_dict_obj_type = 'OBJECT PRIVILEGE' THEN
    FOR i IN 1 .. ora_grantee(user_list)
    LOOP
      IF (user_list(i) = 'PUBLIC') THEN
        l_str := 'execute immediate "revoke all on ' ||
        ora_dict_obj_name || ' from public";';

        dbms_job.submit(l_job, replace(l_str,'"',''''));

        l_str := 'execute immediate "grant select, insert,
        delete, update on ' || ora_dict_obj_name || ' to
        my_role";';

        dbms_job.submit(l_job, replace(l_str,'"',''''));
      END IF;
    END LOOP;
  END IF;
END det_grant;
/

GRANT select, update, delete ON servers TO scott;
 
PERCENT_RANKM
Undocumented
Introduced in 9i
PERCENT_RANKM(
SELECT percent_rankm(1,1) FROM dual;
       *
ERROR at line 1:
ORA-00909: invalid number of arguments
 
RANKM
Undocumented
Introduced in 9i
RANKM(
SELECT rankm(1,1) FROM dual;
       *
ERROR at line 1:
ORA-00909: invalid number of arguments
 
SYS_CHECKACL

Used as part of the EXPLAIN PLAN output for a query on an XMLType table created as a result of calling PL/SQL procedure DBMS_XMLSCHEMA

SYS_CHECKACL()
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
Undocumented
Introduced in 9i
SYS_DOM_COMPARE(<arg1>, <arg2>)
SELECT sys_dom_compare(1,1) FROM dual;
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
 
SYS_ET_BFILE_TO_RAW
Undocumented
Introduced in 10.1
SYS_ET_BFILE_TO_RAW(arg IN bfile);
conn pm/pm

SELECT sys_et_bfile_to_raw(ad_graphic)
FROM print_media;
 
SYS_ET_BLOB_TO_IMAGE
Undocumented
Introduced in 10.1
SYS_ET_BLOB_TO_IMAGE(<blob arg>, arg2, arg3);
conn pm/pm

SELECT sys_et_blob_to_image(ad_photo, ad_composite, 'TEST')
FROM print_media;
                                      *
ERROR at line 1:
ORA-30175: invalid type given for an argument
 
SYS_ET_IMAGE_TO_BLOB
Undocumented
Introduced in 10.1
SYS_ET_IMAGE_TO_BLOB(<blob arg>, arg2, arg3);
conn pm/pm

SELECT sys_et_image_to_blob(ad_photo)
FROM print_media;
                            *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got BLOB
 
SYS_ET_RAW_TO_BFILE
Undocumented
Introduced in 10.1
SYS_ET_RAW_TO_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 SYS_FBT_INSDEL
SELECT sys_fbt_insdel FROM dual;
 
SYS_OP_BL2R
Undocumented
Introduced in 9.2

Appears to converts BLOB to RAW
SYS_OP_CL2C(<arg> IN CLOB)
conn pm/pm

SELECT sys_op_bl2r(ad_photo)
FROM print_media;
SELECT sys_op_bl2r(ad_photo)
       *
ERROR at line 1:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 5482, maximum: 2000)
  
SYS_OP_CEG

Undocumented
Introduced in 9.2

SYS_OP_CEG
SELECT sys_op_ceg(1) FROM dual;
       *
ERROR at line 1:
ORA-00938: not enough arguments for function

SELECT sys_op_ceg('A',1) FROM dual;
                   *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got CHAR
 
SYS_OP_CL2C
Undocumented
Introduced in 9.2

Appears to converts CLOB to CHAR
SYS_OP_CL2C(<arg> IN CLOB)
conn pm/pm

SELECT sys_op_cl2c(ad_finaltext)
FROM print_media;
 
SYS_OP_COMBINED_HASH

Undocumented
Used in the gathering of extended stats by DBMS_STATS
SYS_OP_COMBINED_HASH(<col1>, <col2>)
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';

SELECT SYS_OP_COMBINED_HASH('REALCOL1','REALCOL2') from dual;
 
SYS_OP_CONVERT
Undocumented
Introduced in 9.2
SYS_OP_CONVERT(<arg1>, <arg2>)
SELECT dump('Morgan'), dump(sys_op_convert('Morgan', 'IS8PC861', 'RU8PC866'))
FROM dual;
 
SYS_OP_COUNTCHG

Undocumented
SYS_OP_COUNTCHG(rowid, integer_between_1_and_255)
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 difference between SORT GROUP BY and SORT AGGREGATE
 
SYS_OP_C2C
Undocumented
Introduced in 10.1
SYS_OP_C2C(<arg> INTEGER);
SELECT sys_op_c2c(67) FROM dual;

SELECT sys_op_c2c(97) FROM dual;
 
SYS_OP_DESCEND
Descending Index /*
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);
SELECT sys_op_descend('0A')
FROM dual;

SELECT sys_op_descend('Dan Morgan')
FROM dual;
 
SYS_OP_DISTINCT

Undocumented

Returns 0 if the columns are identical in value, 1 if they are not

Overload 1
SYS_OP_DISTINCT(col1 IN NUMBER, col2 IN NUMBER);
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)
SELECT sys_op_dump(1) FROM dual;
                    *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got NUMBER
 
SYS_OP_GROUPING
Undocumented
Introduced in 9i
SYS_OP_GROUPING(
SELECT sys_op_grouping(1,1,1,1) FROM dual;
ERROR:
ORA-00932: inconsistent datatypes: expected CHAR got B4

no rows selected
 
SYS_OP_GUID
Undocumented SYS_OP_GUID()
SELECT sys_guid()
FROM dual;

SELECT sys_op_guid()
FROM dual;
 
SYS_OP_IIX

Undocumented
Introduced in 9.2
SYS_OP_IIX(<arg1>, <arg2>)
SELECT sys_op_iix(1) FROM dual;
       *
ERROR at line 1:
ORA-00909: invalid number of arguments

SELECT sys_op_iix('A',1) FROM dual;
                   *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got CHAR
 
SYS_OP_ITR

Undocumented
Introduced in 10.1
sys_op_itr(... unknown data type ...)
SELECT sys_op_itr(1) FROM dual;
                   *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got NUMBER
 
SYS_OP_LBID

Leaf Block ID Scanning
/*
Has many functional variations, but the point is to scan through index leaf blocks and calculate a measure of the quality of the index. The way it is counted, and the thing being counted, depends on the type of index (viz: bitmap, simple b-tree, local/global index, IOT, or secondary on IOT, cluster).

*/


SYS_OP_LBID(<object_id>, <block_type>, <table_name.rowid>);
CREATE INDEX ix_serv_inst
ON serv_inst(srvr_id);

SELECT object_id
FROM user_objects
WHERE object_name = 'IX_SERV_INST';

SELECT rows_per_block, count(*) blocks
FROM (
  SELECT /*+ cursor_sharing_exact
             dynamic_sampling(0)
             no_monitoring
             no_expand
             index_ffs(serv_inst,ix_serv_inst)
             noparallel_index(serv_inst,ix_serv_inst)
         */
    sys_op_lbid(89996, 'L', serv_inst.rowid) block_id,
    COUNT(*) rows_per_block
  FROM serv_inst -- t1 sample block (100)
  WHERE srvr_id IS NOT NULL
  GROUP BY sys_op_lbid(89996, 'L', serv_inst.rowid))
GROUP BY rows_per_block;

drop index ix_serv_inst;
 
SYS_OP_LVL

Undocumented but Introduced 9.2
sys_op_lvl(<arg1>, ...)
SELECT '-' || sys_op_lvl(5) || '-' FROM dual;

SELECT sys_op_lvl(1, 1, 2) 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>)
CREATE OR REPLACE TYPE o_type AS OBJECT (n NUMBER, v VARCHAR2(20));
/

CREATE OR REPLACE TYPE t_type AS TABLE OF o_type;
/

CREATE OR REPLACE TYPE m_type 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;

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

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_NII

Undocumented
sys_op_nii(<arg1>, <arg2>);
SELECT sys_op_nii(1) FROM dual;
       *
ERROR at line 1:
ORA-00909: invalid number of arguments

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>);
SELECT sys_op_noexpand('A') FROM dual;

SELECT sys_op_noexpand(srvr_id) FROM servers;
 
SYS_OP_OIDVALUE
Undocumented

May related to object views
sys_op_oidvalue(<... unknown data type ...> ...
SELECT sys_op_oidvalue('TAB$', 1) FROM dual;
                       *
ERROR at line 1:
ORA-00903: invalid table name
 
SYS_OP_PAR

Undocumented but relates to the OLAP API
Introduced 9.2
sys_op_par(<agr1>, <arg2>, <arg3>)
CREATE TABLE t (
x NUMBER(10),
y NUMBER(10));

INSERT INTO t
SELECT object_id, data_object_id
FROM all_objs;
COMMIT;

SELECT xx, yy, TO_CHAR(sys_op_par(0, GROUPING_ID(xx, yy), xx, yy))
FROM (
  SELECT t1.x xx, t2.x yy
  FROM t t1, t t2
  WHERE t1.x =t2.y)
GROUP BY xx, ROLLUP(yy)
HAVING GROUPING_ID(xx,yy) =1;
 
SYS_OP_PARGID

Undocumented but Introduced 9.2
sys_op_pargid(<agr1>, <arg2>)
SELECT sys_op_pargid(1, 99) FROM dual;

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

SELECT sys_op_pargid(1, 4, 2, 1, 99, 99, 99, 99, 99) 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('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 MAX(sys_op_rpb(rowid))
from servers;
 
SYS_OP_R2O

Undocumented
SYS_OP_R2O(<arg> IN REF)
conn oe/oe

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 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, 'Jack Cline')));


SELECT rid, col
FROM t;

SELECT sys_op_tosetid(col)
FROM t;
 
SYS_OP_TRTB
Undocumented
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 LENGTH(sys_op_trtb('A', 2)) FROM dual;

SELECT '-' || sys_op_trtb('A', 3) || '-' FROM dual;

SELECT LENGTH(sys_op_trtb('A', 3)) FROM dual;
 
SYS_OP_UNDESCEND
Undocumented
Introduced in 10.1
SYS_OP_UNDESCEND(unknown BINARY)
SELECT sys_op_undescend('10')
FROM dual;

SELECT sys_op_undescend('00')
FROM dual;
 
SYS_OP_VECAND
Undocumented but 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
Undocumented SYS_OP_VECAND(<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
Undocumented but 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
Undocumented but 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_XPTHATG
Undocumented
Introduced in 10.1
SYS_OP_XPTHATG(
SELECT sys_op_xpthatg(1) FROM dual;
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
 
SYS_OP_XPTHIDX
Undocumented
Introduced in 10.1
SYS_OP_XPTHIDX(
SELECT sys_op_xpthidx(1) FROM dual;
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
 
SYS_OP_XPTHOP
Undocumented
Introduced in 10.1
SYS_OP_XPTHOP(
SELECT sys_op_xpthop(1) FROM dual;
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
 
SYS_OP_XTXT2SQLT

Undocumented
Introduced in 10.1
SYS_OP_XTXT2SQLT(?)
SELECT sys_op_xtxt2sqlt(1) FROM dual;
       *
ERROR at line 1:
ORA-00938: not enough arguments for function

SELECT sys_op_xtxt2sqlt(1,2) FROM dual
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
 
SYS_OP_VVD
Undocumented
Introduced in 9i
SYS_OP_VVD(
SELECT sys_op_vvd('T_TYPE',1) FROM dual;
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got CHAR
 
SYS_XMLCONTAINS

Undocumented
Introduced in 10.1
SYS_XMLCONTAINS(<arg1>, <arg2>)
SELECT sys_xmlcontains(1) FROM dual;
       *
ERROR at line 1:
ORA-00938: not enough arguments for function

SELECT sys_xmlcontains(1,2) FROM dual;
 
SYS_XMLCONV
Undocumented
Introduced in 9i
SYS_XMLCONV(<arg1>, <arg2>, <arg3>, <arg4>, <arg5>, <arg6>, <arg7>, <arg8>)
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')
 
WM_CONCAT

This function is owned by WMSYS
WM_CONCAT(p1 IN VARCHAR2) RETURN VARCHAR2
CREATE TABLE t (
col1 VARCHAR2(5),
col2 VARCHAR2(20));

INSERT INTO t VALUES (111, 'This');
INSERT INTO t VALUES (111, 'is');
INSERT INTO t VALUES (111, 'a');
INSERT INTO t VALUES (111, 'test');
INSERT INTO t VALUES (222, 'This is not');

SELECT * FROM t;

col concat format a40

SELECT col1, wmsys.wm_concat(col2) CONCAT
FROM t
GROUP BY col1;

SELECT col1, TRANSLATE(wmsys.wm_concat(col2), 'A,', 'A ') CONCAT
FROM t
GROUP BY col1;
 
Related Topics
Functions
Indexes
SKIP LOCKED
System Events
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [96 users online]    © 2010 psoug.org