10 Oracle SQL functions and function parameters you might not know
Functions mentioned here are in alphabetical order
BITAND function but no BITOR function
There is a BITAND function in SQL but no BITOR function.
Bitand
allows to bit-compare values. It returns a value where all bits have been compared using AND. That means, each bit in the first operand must be matched with the bits in the second operand. This is useful to compare a set of flags (yes/no values) that are stored inside the same integer field.BIN_TO_NUM
is a helper function that “translates” bits into (decimal) numbers.BITAND
exampleSELECT BITAND( BIN_TO_NUM(1,0,0), -- 4 in decimal BIN_TO_NUM(0,0,1) -- 1 in decimal ) check_flag from dual; 0 |
So 4 and 1 have no bits in common.
SELECT BITAND( BIN_TO_NUM(1,0,1), -- 5 in decimal BIN_TO_NUM(1,0,0) -- 4 in decimal ) check_flag from dual; 4 |
4 = 0×2⁰ + 0×2 + 1×2² = BIN_TO_NUM(1,0,0)
So 5 and 4 have bit 3 in common. (Bitcounting starts 1 but powers of 2 start with 0)
BITOR
would combine the bits from each operand.
Fortunately
BITOR
can be calculated using BITAND
. We need to sum the operands and then subtract the BITAND
result from that.
Here is an example
select 1 + 4 - BITAND(1,4) bitor_compare from dual; 5 select 5 + 4 - BITAND(5,4) bitor_compare from dual; 5 |
CAST with default on conversion error
The CAST function allows to convert values from one datatype into another.
Parameter enhancements have been introduced in 12.2 along with similar enhancements to many datatype conversion functions.
Let’s experiment a little with it.
select cast ( '01-01-20' as date default null on conversion error , 'dd-mm-rr' ) from dual; 01-Jan-20 select cast ( 'INVALID' as date default null on conversion error , 'dd-mm-rr' ) from dual; ( null ) |
Works like a charm. The string was converted into a date (and converted back into a string to print it on the screen using local nls settings). An invalid string could not be converted, instead the default NULL value was chosen.
Sometimes we want to return today in case there is a conversion error…
select cast ( '01-01-20' as date default sysdate on conversion error , 'dd-mm-rr' ) from dual; 01-Jan-20 select cast ( 'INVALID' as date default sysdate on conversion error , 'dd-mm-rr' ) from dual; 17-Mar-20 |
seems to work!
..not so fast
alter session set nls_date_format = 'day-Mon-rr' ; select cast ( '01-01-20' as date default sysdate on conversion error , 'dd-mm-rr' ) from dual; |
ORA-01858: a non-numeric character was found where a numeric was expected
Although we used
The problem here is the default parameter. It needs to be a string. Since it is not, an implicit type conversion happened. The result differs from the function parameter
cast
with a default on conversion error
, we still got an conversion error!The problem here is the default parameter. It needs to be a string. Since it is not, an implicit type conversion happened. The result differs from the function parameter
fmt
. Which then leads to a conversion error.
Unfortunately the default parameter can not be an expression.
select cast ( '01-01-20' as date default to_char(sysdate, 'dd-mm-rr' ) on conversion error , 'dd-mm-rr' ) from dual; |
ORA-43907: This argument must be a literal or bind variable.
Also nls_date_format is not allowed as
nlsparam
.select cast ( '01-01-20' as date default sysdate on conversion error , 'dd-mm-rr' , 'nls_date_format=dd-mm-rr' ) from dual; |
ORA-12702: invalid NLS parameter string used in SQL function
The same could happen for other conversions, like casting a string into a number. This example is from the docs.
SELECT CAST(200 AS NUMBER DEFAULT 0 ON CONVERSION ERROR) FROM DUAL;
It is a very very bad example, since 200 is already a number. So let’s assume this is ‘200.00’ but the default is still 0. And we add the other parameters to specify the format of this string.
select cast ( '200.00' as number default 0 on conversion error , 'FM999d00' , 'nls_numeric_characters=.,' ) from dual; 200 select cast ( '200x00' as number default 0 on conversion error , 'FM999d00' , 'nls_numeric_characters=.,' ) from dual; 0 |
Seems to work. But here again we can manipulate the session settings into a wrong conversion. Notice that the alter session settings and the
nlsparam
settings differ.alter session set nls_numeric_characters = ',.' ; Session altered. select cast ( '200x00' as number default 0 on conversion error , 'FM999d00' , 'nls_numeric_characters=.,' ) from dual; 0 |
Good. Fortunately the number conversion is slightly more robust than the date conversion. We can not set a session default number format (it is always “TM9” I believe) in the same way as we can set the default date format.
However we get into trouble once the default value uses non-integer numbers.
select cast ( '200x00' as number default 0.5 on conversion error , 'FM999d00' , 'nls_numeric_characters=.,' ) from dual; |
5
The default was 0.5 but we got 5!
The correct way to do it of cause would be to use a string with a format that reflects the
fmt
and nlsparam
parameters.select cast ( '200x00' as number default '0.5' on conversion error , 'FM999d00' , 'nls_numeric_characters=.,' ) from dual; |
0,5
Now we got the correct default value returned!
CHECKSUM
Checksum can be used to quickly calculate a checksum over a set of rows. There is an analytic version of it too.
If you want to quickly see if data was changed between two databases, you can run a checksum over each column and compare the result on both environments.
select checksum(phone_number) from hr.employees; |
Never heard of this function before? Probably because it just got introduced in 20c.
Dump
The dump function is often used to inspect binary values. Useful for example when solving character set issues.
Not many know that is has some additional parameters.
Interesting is especially the
return_fmt
parameter. It allows to return the dump in octal (8), hex (16), decimal (10=default) or a special format (17). The return format “17” will return the single byte values in readable ASCII format, but multi byte values that can not be converted are shown in hex.
For example the “€” symbol in UTF-8 uses 3 Bytes: E2 82 AC
Dump-17 will return this
select dump( 'abc€' ,17) from dual; |
Typ=96 Len=6: a,b,c,e2,82,ac
The string is made of 6 bytes. The first 3 are single byte characters. They are converted into ascii. The 4th character is the Euro-Sign, witch is 3 bytes in UTF-8. So the format 17 is interesting, because it helps us to focus and find the problematic characters.
Another interesting option is to add 1000 to the format. This will add the character-set to the output.
select dump( 'abc€' ,1010) from dual; |
Typ=96 Len=6 CharacterSet=AL32UTF8: 97,98,99,226,130,172
Of cause that works with NCHAR too.
select dump(n 'abc€' ,1010) from dual; |
Typ=96 Len=8 CharacterSet=AL16UTF16: 0,97,0,98,0,99,32,172
LNNVL
This is a very special function. Apart from the unspeakable name it is used for Oracle internal SQL transformations by the optimizer. Here is a document that describes such transformations.
LNNVL
returns a Boolean and can be used directly inside a where clause. This is not yet possible for user defined functions that return Boolean, but other such functions do exist (e.g. regexp_like
).
I sometimes use it to find rows where two values differ. If the comparison value is NULL I still want to consider it to be different.
Instead of using the non-equality operator
!=
we have to use the opposite the equality operator =
. This is just how LNNVL
works.select dummy from dual where lnnvl( 'A' = 'B' ); X; select dummy from dual where lnnvl( 'A' = 'A' ); No data found. select dummy from dual where lnnvl( 'A' = null ); X |
This assumes that ‘A’ always is a not null value.
To get the same result typically a comparison condition needs also consider the NULL case.
select dummy from dual where (( 'A' != 'B' and 'B' is not null ) OR 'B' is null ); |
Since more conditions like this might follow, the statement very soon becomes cluttered with OR checks, parenthesis and IS NULL comparisons.
Unfortunately since not many developers are familiar with this function, we should always add some comments to explain the purpose and the behaviour.
NANVL
NANVL
is similar to NVL
.NVL
returns a value, when NULL
is encountered.NANVL
returns a value when NaN
(not a number) is encountered.NaN
is part of the binary_float and binary_double datatypes.select to_binary_float(1/17) from dual; 0,05882353 select to_binary_float( 'NaN' ) from dual; NaN |
Lets apply
NANVL
to itselect nanvl(to_binary_float( 'NaN' ), '0' ) from dual; 0.0 |
Or we could set it to negative infinity…
select nanvl(to_binary_float( 'NaN' ), '-INF' ) from dual; -Infinity |
Somehow interesting, but rarely useful I think.
NEXT_DAY function parameter
The NEXT_DAY function needs a second parameter that defines what target weekday it points to.
The argumenthttps://docs.oracle.com/en/database/oracle/oracle-database/20/sqlrf/NEXT_DAY.html#GUID-01B2CC7A-1A64-4A74-918E-26158C9096F6char
must be a day of the week in the date language of your session, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argumentdate
.
This second parameter
char
works differently than what you might expect. Here is an example.alter session set nls_date_format = 'Dy, DD-MON-YYYY' ; Session altered. alter session set nls_language= 'AMERICAN' ; select next_day(sysdate, 'FRIDAY' ) from dual; Fri, 20-MAR-2020 alter session set nls_language= 'GERMAN' ; Session altered. select next_day(sysdate, 'FREITAG' ) from dual; Fr, 20-MRZ-2020 select next_day(sysdate, 'FRIDAY' ) from dual; Fr, 20-MRZ-2020 |
Isn’t that strange? It looks as if although my session language is GERMAN, but that the American spelling still works!
Let’s test this with a different language. In Spanish “friday” would be “viernes”.
select next_day(sysdate, 'VIERNES' ) from dual; ORA-01846: Kein gültiger Wochentag "not a valid day of the week" alter session set nls_language= 'SPANISH' ; Session altered. select next_day(sysdate, 'VIERNES' ) from dual; Vie, 20-MAR-2020 select next_day(sysdate, 'FRIDAY' ) from dual; ORA-01846: día de la semana no válido "not a valid day of the week" |
Ok. So the Spanish language works as long the NLS settings are correct, but it doesn’t accept the American spelling.
Is German special? In a way yes. In German several weekdays have an abbreviation that matches the American spelling. And the abbreviation is only two letters short. Especially those days that have a good chance to be used in the NEXT_DAY function (monday, friday, saturday, but not sunday!).
For “FREITAG” the abbreviation is “FR”. The NEXT_DAY function accepts anything as long as the
char
string starts with the abbreviation of a weekday in the correct language. So FREITAG, FRIDAY and even FRITZ or FROG all return the next friday.alter session set nls_language= 'GERMAN' ; Session altered. select next_day(sysdate, 'FR' ) from dual; Fr, 20-MRZ-2020 select next_day(sysdate, 'FRITZ' ) from dual; Fr, 20-MRZ-2020 select next_day(sysdate, 'FROG' ) from dual; Fr, 20-MRZ-2020 |
Is this how you expected the
char
parameter to work?
Recommendation? Don’t use
NEXT_DAY
at all. I prefer TRUNC
over NEXT_DAY
any day.POWER (sql) = ** (plsql)
In SQL to calculate an exponentiation we have to use the
POWER
function.
Example 2³
select power(2,3) from dual; |
8
In plsql we can use the
**
operator instead.set serveroutput on begin dbms_output.put_line(2**3); end ; / |
8
Unfortunately the
**
operator will not work in SQL.ROUND_TIES_TO_EVEN function
Everybody knows the
round
function. Round
rounds a value up which is exactly half.1.5 ≈ 2 2.5 ≈ 3 ... 7.5 ≈ 8 8.5 ≈ 9 9.5 ≈ 10
This is what statisticians call a biased function. The computed values do not reflect the distribution of the original values – especially if there are many 0.5 decisions.
An unbiased version of rounding is to round one value up, the next down, then up again.
1.5 ≈ 2 2.5 ≈ 2 ... 7.5 ≈ 8 8.5 = 8 9.5 ≈ 10
In German that is called “kaufmännisches Runden”. Loosely translated to Bankers Rounding.
The
round_ties_to_even
function has been introduced in 18c.select level +0.5 as val, round( level +0.5), round_ties_to_even( level +0.5) from dual connect by level <= 10; |
VAL | ROUND(VAL) | ROUND_TIES_TO_EVEN(VAL) |
---|---|---|
1.5 | 2 | 2 |
2.5 | 3 | 2 |
3.5 | 4 | 4 |
4.5 | 5 | 4 |
5.5 | 6 | 6 |
6.5 | 7 | 6 |
7.5 | 8 | 8 |
8.5 | 9 | 8 |
9.5 | 10 | 10 |
10.5 | 11 | 10 |
This new rounding function implements “Bankers Rounding”. Especially when adding up the rounded values, the rounded sum differs less from the correct (unrounded) sum compared to what normal rounding delivers.
STANDARD_HASH function
If we quickly want to create some hash values we can use the
standard_hash
function.select standard_hash(dummy) from dual; |
C032ADC1FF629C9B66F22749AD667E6BEADF144B
The default uses an SHA1 hash algorithm, but we can specify
SHA1
, SHA256
, SHA384
, SHA512
and MD5
.select standard_hash(dummy, 'SHA512' ) from dual; |
3173F0564AB9462B0978A765C1283F96F05AC9E9F8361EE1006DC905C153D85BF0E4C45622E5E990ABCF48FB5192AD34722E8D6A723278B39FEF9E4F9FC62378
MD5 is not recommended at all (https://www.md5online.org/blog/why-md5-is-not-safe/). Also SHA1 is not considered secure anymore, but you can still use it safely to create hash values by it for non security purposes.
Conclusion
That’s it. 10 functions or parameters of functions that you probably didn’t know before. At least not all of them.
Comments