Unknown Functions

A Word : Risk of using Oracle Undocumented functions or procedures here is that without any notice Oracle may remove them and there will not be support from ORACLE

To get similar characters among the two fields (from 9i onwards)

SELECT merge$actions('lNvl', 'pppl') FROM dual;
SELECT replace(merge$actions(1204, 1260),'BB','') FROM dual;

------------------------------------------------------------------------------
To view blob details (from 10g onwards)

SELECT sys_op_cl2c(ad_finaltext),s.product_id FROM pm.print_media s

-----------------------------------------------------------------------------
To get blocknumber of a particular tables (from 9i onwards)

select dbms_rowid.rowid_block_number(rowid) from dual;

SELECT COUNT(DISTINCT dbms_rowid.rowid_block_number(rowid)),
COUNT( dbms_rowid.rowid_block_number(rowid))FROM pm.print_media;

----------------------------------------------------------------------
To convert into HEX (from 9i onwards)

SELECT sys_op_descend('9')FROM dual;

-----------------------------------------------------------------------------
To show generate null coloumn (from 9i onwards)

SELECT sys_op_lvl(1,21,1,1,1,1 ,2) FROM dual;

-------------------------------------------------------------------------------
To generates and returns a globally unique identifier of 16 bytes The returned data type is RAW(16) (from 9i onwards)

SELECT sys_guid() FROM dual;

-----------------------------------------------------------------------
To get comma seperated values in rows wise (from 10g onwards)

select sys.odcivarchar2list('Football','Rugby') from dual;

select column_value from table(
select sys.odcivarchar2list('Football','Rugby') from dual);
-----------------------------------------------------------------------
To get data in ascii characters (from 10g onwards)


SELECT sys_op_c2c(9068986) FROM dual;
-------------------------------------------------------------------------
TO get number representation of rowid (from 9i onwards)

SELECT rowid, sys_op_rpb(rowid),dbms_rowid.rowid_block_number(rowid) FROM scott.emp
-------------------------------------------------------------------------
Similar to null (from 10g onwards)

SELECT sys_fbt_insdel, decode(sys_fbt_insdel,null,1,0) FROM dual;
-------------------------------------------------------------------------
To convert hex to num (from 9i onwards)


SELECT sys_op_rawtonum('000000FF'),UTL_RAW.CAST_FROM_BINARY_INTEGER(255) FROM dual;
-------------------------------------------------------------------------
To check whether a number is even or odd (from 9i onwards)

Return the value of the bit at position N
The return value is 0 or 1

SELECT sys_op_vecbit('255',0),sys_op_vecbit('255',1),sys_op_vecbit('22',1),sys_op_vecbit('22',0) FROM dual;
-------------------------------------------------------------------------
Return the binary AND of two hex values (from 9i onwards)

SELECT sys_op_vecand('FC','FD') from dual;
SELECT sys_op_vecand('FF','FD') from dual;
-----------------------------------------------------------------------
Return the binary OR of two hex values (from 9i onwards)


SELECT sys_op_vecor(('FC'),('FE')) from dual;
-----------------------------------------------------------------------
Return the binary XOR of two hex values (from 9i onwards)

SELECT sys_op_vecxor(('FF'),('FE')) from dual;
-----------------------------------------------------------------------
To get ref value visible (from 9i onwards)

SELECT sys_op_r2o(CUSTOMER_REF),CUSTOMER_REF FROM oe.oc_orders WHERE rownum = 1;

--------------------------------------------------------------------------------
Query to show which all values are present in a table and not present

SELECT nvl(to_char(e.EID), column_value||' Not found') empno from table(sys.odcinumberlist(7369,7370,7566,7555,1))l left outer join emp e on e.EID = l.column_value order by 1

sys.odcinumberlist is the default varray that oracle supplies . If you want to create your own type

CREATE TYPE typ_p is varray (32767) of number

SELECT * from table(typ_p(7369,7370,7566,7555,1 ))

-----------------------------------------------------------------------
To compare null (from 9i onwards)

SELECT 'hi there' FROM DUAL WHERE NULL = NULL;

SELECT 'hi there' FROM DUAL WHERE sys_op_map_nonnull (NULL) = sys_op_map_nonnull (NULL);
SELECT 'hi there' FROM DUAL WHERE nvl(null,'FF')='FF';
select sys_op_map_nonnull(null) from dual; --similat to nvl(null,'FF')
select * from dual where sys_op_map_nonnull(null) = 'FF';
SELECT 'hi there' FROM DUAL WHERE to_char(1||NULL) = to_char(1||NULL);
-----------------------------------------------------------------------