Tip#4 Dump, Lock & Nth Max/Min

DUMP Function :
Sometimes if you need to handle special characters or like in my case various European characters then it is quite useful to know the Hexadecimal or decimal value of the character. You can use the DUMP sql function for the same as shown below,

dump( expression, [return_format], [start_position], [length] )

=> expression is the expression to analyze.

=> return_format is optional. It determines the format of the return value. This parameter can be any of the following values:

Value Explanation
----- -------------
8....... octal notation
10..... decimal notation
16 ..... hexadecimal notation
17 ..... single characters
1008... octal notation with the character set name
1010...decimal notation with the character set name
1016... hexadecimal notation with the character set name
1017... single characters with the character set name

=> start_position and length are optional parameters. They determines which portion of the internal representation to display. If these parameters are omitted, the dump function will display the entire internal representation in decimal notation.

e.g.
select dump('öäüß',1016) hex_value from dual;

Hex_Value
-------------
Typ=96 Len=8 CharacterSet=UTF8: c3,b6,c3,a4,c3,bc,c3,9f


Lock any table explicitly :

LOCK table MV_REPORT_E2E_LOGIC IN EXCLUSIVE MODE NOWAIT;

commit or rollback to release the lock.

Find Nth Maximum value of a column in a table :

SELECT *
FROM MY_TAB t1
WHERE &N = (SELECT count(DISTINCT(t2.col1))
FROM MY_TAB t2 WHERE t1.col1<=t2.col1)

e.g. N=1 will return first max or N=2 will return second max.

Find Nth Minimum value of a column in a table :

SELECT *
FROM MY_TAB t1
WHERE &N = (SELECT count(DISTINCT(t2.col1))
FROM MY_TAB t2 WHERE t1.col1 >=t2.col1)

e.g. N=1 will return first min or N=2 will return second min.