How to workwith these functions in SQL?

[AdusumalliGopikumar]

Thanks for giving me the answers for previous questions..


and how to work with these functions..

a1.Dump

2.Bin_To_And

3.BitAnd

4.Corr
5.Extract


Thankx in Advance

[amitpatel66]

1. Oracle/PLSQL: Dump Function
In Oracle/PLSQL, the dump function returns a varchar2 value that includes the datatype code, the length in bytes, and the internal representation of the expression.

The syntax for the dump function is:

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.

For example:

dump('Tech') would return 'Typ=96 Len=4: 84,101,99,104'
dump('Tech', 10) would return 'Typ=96 Len=4: 84,101,99,104'
dump('Tech', 16) would return 'Typ=96 Len=4: 54,65,63,68'
dump('Tech', 1016) would return 'Typ=96 Len=4 CharacterSet=US7ASCII: 54,65,63,68'
dump('Tech', 1017) would return 'Typ=96 Len=4 CharacterSet=US7ASCII: T,e,c,h'



2. In Oracle/PLSQL, the bitand function returns an integer representing an AND operation on the bits of expr1 and expr2.

The syntax for the bitand function is:

bitand( expr1, expr2 )

expr1 and expr2 must resolve to non-negative integers.



For example:

bitand(5,3) would return 1
bitand(15,7) would return 7
bitand(5,2) would return 0
bitand(5,0) would return 0
bitand(6,2) would return 2


3. In Oracle/PLSQL, the corr function returns the coefficient of correlation of a set of number pairs.

The syntax for the corr function is:

corr( n ,m ) [ over (analytic_clause) ]

n and m are the numbers to use to calculate the cofficient of correlation.

The corr function requires at least two rows.

For example:

select corr(quantity, commission)
from sales;


4. EXTRACT function is used to extract the day,month and year from a particular date.

For Eg:

SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL

SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL

SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL