Oracle Common and very useful functions

Feel that some netizens always put forward some very basic questions, for example, there is no function to achieve such functions ah, what is the function of Ah, what the format is, and so on, but also feel their understanding of the lack of Oracle function, so focused on the remainder of the time to focus on the Oracle function, small heart is afraid to be concealed, Share with Antiphon.

This article is not intended to introduce all of the Oracle functions, in the current situation, I also do not have this time, need to learn too much, to spend most of the time to learn the technical aspects can often be used: so if you are ready to understand all the Oracle functions of friends, or to focus on: Oracle SQL Reference official documents are more reliable.

This article will introduce some of the functions that are often used in everyday life, or that are very interesting, though they are rarely used. It is divided into two kinds of introductions, namely:
Famous letters-functions that are often used
Non-well-known letters-that is, although rarely used, but in some cases it is practical


Note: n denotes a numeric type, C represents a character type, D represents a date type, [] indicates that the inner parameter can be ignored and FMT represents the format.

A single value function returns a single value in the query and can be applied to the Select,where clause, the start with, and the Connect by clause and the HAVING clause.
(i). Numeric type function (number functions)
A numeric function enters a numeric parameter and returns the value of a numeric type. Most of the return values of this type of function support 38-bit decimal points, such as: COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH support 36-bit decimal points. ACOS, ASIN, Atan, and ATAN2 support 30-bit decimal points.

1. MOD (N1,N2) returns the remainder of the N1 except N2 if N2=0 returns the N1 value.
For example: SELECT MOD (24,5) from DUAL;

2. ROUND (N1[,N2]) returns the value N1 after rounding the right n2 of the decimal point, N2 the default value of 0, and if N2 is negative, round to the corresponding bit to the left of the decimal point (although the value of the N2 mentioned in Oracle documents must be an integer, In fact, the judgment here is not rigorous, even if N2 is not an integer, it will automatically take the N2 after the whole processing, but my document in the other mentioned must be the whole place need special attention, if not for the whole execution of the error.
For example: SELECT ROUND (23.56), ROUND (23.56,1), ROUND (23.56,-1) from DUAL;

3. TRUNC (N1[,N2] Returns the N1 value of the truncated to N2 decimal, n2 The default setting is 0, and when N2 is the default, the N1 is truncated to an integer, and if the N2 is negative, it is truncated to the corresponding bit to the left of the decimal point.
For example: SELECT TRUNC (23.56), TRUNC (23.56,1), TRUNC (23.56,-1) from DUAL;
(ii). Character function returns the value of a character (Character functions returning Character values)
The class function returns the same type as the input type.
 The returned char type value is no longer than 2000 bytes;
 The returned VCHAR2 type value is not longer than 4000 bytes;
If the length of the character that should be returned above is exceeded, Oracle will not make an error but directly truncate to the maximum supporting length return.

 The returned CLOB type value is not longer than 4G;
For functions of type CLOB, if the return value is longer than that, Oracle will not return any errors but simply throw the error.

1, LOWER (c) in the specified string characters into lowercase, support char,varchar2,nchar,nvarchar2,clob,nclob type
For example: SELECT LOWER (' WhaT is this ') from DUAL;

2, UPPER (c) in the specified string characters into uppercase, support CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB type
For example: SELECT UPPER (' WhaT is this ') from DUAL;

3, Lpad (C1,N[,C2]) returns the string of the specified length =n, several points to note:
 If n<c1.length from right to left to intercept the specified length return;
If n>c1.length and C2 is null, add the character length to N and return with a space from left to right;
If N>c1.length and C2 is not NULL, add C1 length to n and return from left to right with the specified character C2;
For example: SELECT lpad (' WhaT is this ', 5), Lpad (' WhaT are this ', ", Lpad" (' WhaT is this ', "-") from DUAL;
Finally, let's guess, if n<0, what will be the result

4, Rpad (C1,N[,C2]) returns the string of the specified length =n, essentially the same as the upper, but the complementary character is the right and left direction exactly the opposite;
For example: SELECT rpad (' WhaT is this ', 5), Rpad (' WhaT are this ', ", Rpad" (' WhaT is this ', "-") from DUAL;

5, TRIM ([[Leading| | Trailing| | BOTH] C2 from] C1 haha, by my invincible description way Dizzy Head, this place or look at the picture more clear some.
It looks so complicated, it's simple to understand:
 Oracle removes C1 and trailing spaces if no parameters are specified
For example: SELECT TRIM (' WhaT is this ') from DUAL;
 If the C2 parameter is specified, Oracle removes the C1-Tail C2 (this recommendation is carefully tested for a variety of different scenarios)
For example: SELECT TRIM (' W ' from ' WhaT be this. W ') from DUAL;
 If the leading parameter is specified, the C1 head is removed C2
For example: SELECT TRIM (Leading ' w ' from ' WhaT be this w ') from DUAL;
 If the trailing parameter is specified, the C1 tail is removed C2
For example: SELECT TRIM (Trailing ' w ' from ' WhaT be this w ') from DUAL;
 If the both parameter is specified, it will remove the C1-tail C2 (different from the unspecified). No difference. )
For example: SELECT TRIM (both ' w ' from ' WhaT be this w ') from DUAL;

Note: C2 length =1

6, LTRIM (C1[,C2]) Tens of millions of meters with the top of the image, the function is similar to the above, this function is from the string C1 to the left to intercept the same characters C2 the specified string and return. If C2 is empty, the space is truncated by default.
For example: SELECT LTRIM (' wwhhhhhat is this w ', ' Wh ') from DUAL;

7, RTRIM (C1,C2) and on the same, but the opposite direction
For example: SELECT RTRIM (' wwhhhhhat are this w ', ' W ' ") from DUAL;

8, replace (C1,C2[,C3]) replaces the C2 in the C1 string with C3, and if C3 is empty, deletes all c1 from the C2.
For example: SELECT REPLACE (' Wwhhhhhat is ", ' w ', '-') from DUAL;

9, SOUNDEX (c) Magic function Ah, the function returns the string parameter of the voice representation, for comparison some pronunciation is the same, but the spelling of different words is very useful. The algorithm for calculating speech is as follows:
Preserves the first letter of the string, but deletes a, E, H, I, O, W, y.
Assign the numbers in the following table to the corresponding letters:
1:b, F, p, V
2:c, G, K, q, S, X, Z
3:d, T
4:l
5:M, N
6:r
If there are more than 2 (including 2) letters with the same number in the string (for example, B and f), or only H or W, then delete the others and keep only 1;
 return only the first 4 bytes, not enough to fill with 0
For example: SELECT SOUNDEX (' dog '), SOUNDEX (' Boy ') from DUAL;

10, SUBSTR (C1,N1[,N2]) intercepts the string of the specified length. A little attention may be filled with the function of the trap.
n1= start length;
n2= the length of the string intercepted and, if NULL, the default intercept to the end of the string;
 If N1=0 then n1=1
If n1>0, Oracle confirms starting position intercept from left to right
For example: SELECT SUBSTR (' What is this ', 5,3) from DUAL;
 Oracle from right to left to confirm start position if n1<0
For example: SELECT SUBSTR (' What is this ', -5,3) from DUAL;
 Return empty if n1>c1.length
For example: SELECT SUBSTR (' What is this ', 50,3) from DUAL;
And then you guess, if n2<1, how would you return the value?

11, TRANSLATE (C1,C2,C3) in terms of functionality, this function is somewhat similar to replace. However, it is important to note that translate is an absolute matching replacement, which is very different from the Replace function. What is an absolute match replacement? To put it simply, replace the string C1 with the C3 in a certain format c2. If the word description is still incomprehensible, we illustrate by several examples:
For example:
SELECT TRANSLATE (' What is this ', ', '-') from DUAL;
SELECT TRANSLATE (' What is this ', '-', ') from DUAL;
The results are all empty. Let's try this one:
SELECT TRANSLATE (' What is this ', ', ', ') from DUAL;
Look at this again:
SELECT TRANSLATE (' What is this ', ' ait ', '-* ') from DUAL;
Did you get the point? The Replace function is easier to understand, replacing a specified character in a string with another character, and its characters must be contiguous. In translate, it is the C2 that appears in the specified string c1, replacing each character in the C2 with the characters in the C3 where the position order is the same as in the C3. I see. Replace is a replacement, and translate is like filtering.
(iii). Character function returns a numeric value (Character functions returning number values)
This class of functions supports all data types

1, INSTR (C1,C2[,N1[,N2]]) returns C2 position in C1
C1: Original string
C2: The string to look for
n1: Query start position, positive value to left-to-right, negative to right to left (size indicates position, for example, 3 means to start at 3rd on the left, and 3 to start at 3rd). Black, if 0, then the return is also 0
N2: The first few matches. Greater than 0
For example: SELECT INSTR (' ABCDEFG ', ' e ', -3) from DUAL;

2, Length (c) Returns the length of the specified string. If
For example: SELECT LENGTH (' A123 ') from DUAL;
Guess what the return value of select LENGTH (') from DUAL;

(iv). Date function (DateTime functions)
In this type of function, the date is returned except for the value returned by the Months_between.

1, Add_months () returns the value after the specified date month +n, n can be any integer.
For example: SELECT add_months (sysdate,12), Add_months (sysdate,-12) from DUAL;

2. Current_date returns the default time of the current session's time zone
For example:
Sql> alter session Set Nls_date_format = ' mm-dd-yyyy ';
Sql> select current_date from dual;

3. The Sysdate function is the same as on, returning the default time of the current session's time zone. However, it is important to note that if you use both Sysdate and current_date the time is not necessarily the same, in some cases current_date will be faster than the sysdate for a second. After a brief exchange with XYF_TCK (the work mechanism written by the elder brother, Oracle), we think Current_date is rounding back the current_timestamp in milliseconds, although no documentation support is found But I think I should sorta. At the same time, only in some cases there will be a second error, under normal circumstances will not affect your operation, so understand.
For example: SELECT sysdate,current_date from DUAL;

4. Last_day (d) Returns the last day of the month of the specified time
For example: SELECT last_day (sysdate) from DUAL;

5, Next_day (d,n) returns the date of the first n after the specified date, n is the day of the week. However, it should be noted that n if it is a character, its weekday form needs to be the same as the week form in the default time zone for the current session.
For example: Think twice in Chinese nt,nls_language value is Simplified Chinese
SELECT Next_day (sysdate,5) from DUAL;
SELECT next_day (sysdate, ' Thursday ') from DUAL;
Both ways can be taken to the correct return, but:
SELECT next_day (sysdate, ' Thursday ') from DUAL;
Then an error will be made, providing that the day of the week is invalid, and that is the reason.

6, Months_between (D1,D2) return D1 and D2 between the month difference, depending on the size of the D1,D2 value, the result can be negative, of course, may also be 0
For example:
SELECT Months_between (Sysdate, Sysdate),
Months_between (Sysdate, Add_months (Sysdate,-1)),
Months_between (Sysdate, Add_months (sysdate, 1))
From DUAL;

7, ROUND (D[,fmt]) before the numerical function of the introduction of the ROUND, here and on the function is basically similar, but here the operation is the date. If you do not specify a FMT parameter, the default returns the date closest to the specified date.
For example: SELECT ROUND (sysdate, ' HH24 ') from DUAL;

8, TRUNC (D[,fmt]) is the same as the numerical TRUNC principle described earlier, but here is the date type of the operation.
For example: SELECT TRUNC (sysdate, ' HH24 ') from DUAL;

(v). Conversion function (conversion functions)
The conversion function converts the specified character from one type to another, which is usually followed by the following conventions: The function name is followed by the type to be converted and the output type.

1, To_char () This function can be divided into three small categories, respectively, are
 Convert character-> character To_char (c): Convert Nchar,nvarchar2,clob,nclob type to CHAR type;
For example: SELECT to_char (' aabbcc ') from DUAL;

 Conversion Time-> character To_char (d[,fmt]): Converts the specified time (Data,timestamp,timestamp with times zone) to the VARCHAR2 type in the specified format;
For example: SELECT to_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from DUAL;

 Convert numeric-> character to_char (n[,fmt]): Converts the specified value n in the specified format FMT to the VARCHAR2 type and returns;
For example: SELECT to_char ( -100, ' L99g999d99mi ') from DUAL;

2, To_date (C[,fmt[,nls]]) converts char,nchar,varchar2,nvarchar2 to a date type, and if the FMT parameter is not empty, the conversion is made according to the format specified in FMT. Note the FMT parameter here. If FTM is a ' J ', the conversion is based on the A.D. (Julian Day), and C must be a positive integer greater than 0 and less than 5373484.
For example:
SELECT to_date (2454336, ' J ') from DUAL;
SELECT to_date (' 2007-8-23 23:25:00 ', ' yyyy-mm-dd hh24:mi:ss ') from DUAL;

Why the ad system, the value of C must not be greater than 5373484. Because the date type of Oracle is scoped from January 1, 4712 to A.D. December 31, 9999. Look at the following statement:
SELECT To_char (to_date (' 9999-12-31 ', ' yyyy-mm-dd '), ' J ') from DUAL;

3, To_number (C[,fmt[,nls]]) converts the CHAR,NCHAR,VARCHAR2,NVARCHAR2 type string to the numeric type and returns the specified format in FMT.
For example: SELECT to_number (' -100.00 ', ' 9g999d99 ') from DUAL;

(vi). Other auxiliary functions (miscellaneous Single-row functions)

1, DECODE (EXP,S1,R1,S2,R2. S,R[,DEF]) can be interpreted as an enhanced if else, except that it does not pass a multiline statement but rather implements the if else function within a function.
Exp as the initial parameter. s as the contrast, the same returns R, if s has more than one, continue to traverse all s until a certain condition is true, otherwise return the default value def (if specified), if there is no default value, and the previous comparison is not true, return null.
There is no doubt that decode is a very important function, in the implementation of row-and-column functions will be used, need to remember and skilled use.

For example: Select Decode (' A2 ', ' A1 ', ' True1 ', ' A2 ', ' True2 ', ' Default ') from dual;

2, Greatest (N1,N2,... N) returns the maximum value in the sequence
For example: Select Greatest (15,5,75,8) "greatest" from DUAL;

3, least (N1,N2....N) returns the minimum value in the sequence
For example: SELECT least (15,5,75,8) least from DUAL;

4, Nullif (C1,C2)
Nullif is also a very interesting function. Logic equivalent to: case when C1 = C2 THEN NULL ELSE C1 End
For example: SELECT nullif (' A ', ' B '), Nullif (' A ', ' a ') from DUAL;

5, NVL (C1,C2) logic is equivalent to the IF C1 is null THEN c2 ELSE C1 end. C1,C2 can be of any type. If the two types are different, Oracle automatically converts C2 to the type of C1.
For example: SELECT NVL (null, ' ') from DUAL;

6, NVL2 (C1,C2,C3) Everyone may use to NVL, but you have used nvl2 it. Returns C2 if the C1 is Non-null, or returns if C1 is empty C3
For example: Select NVL2 (' A ', ' B ', ' C ') isnull,nvl2 (null, ' B ', ' C ') isnotnull from dual;

7, Sys_connect_by_path (col,c) This function can only be applied to tree queries. Returns the path from the root to the node connected through the C1. The function must be used in conjunction with the Connect by clause.
For example:
CREATE TABLE Tmp3 (
Rootcol VARCHAR2 (10),
Nodecol VARCHAR2 (10)
);

INSERT into Tmp3 values (', ' a001 ');
INSERT into Tmp3 values (', ' b001 ');
INSERT into Tmp3 values (' a001 ', ' a002 ');
INSERT into Tmp3 values (' a002 ', ' a004 ');
INSERT into Tmp3 values (' a001 ', ' a003 ');
INSERT into Tmp3 values (' a003 ', ' a005 ');
INSERT into Tmp3 values (' a005 ', ' a008 ');
INSERT into Tmp3 values (' b001 ', ' b003 ');
INSERT into Tmp3 values (' b003 ', ' b005 ');

Select Lpad (', level*10, ' = ') | | > ' | | Sys_connect_by_path (Nodecol, '/')
From Tmp3
Start with Rootcol = ' a001 '
Connect by prior Nodecol =rootcol;

8. Sys_context (C1,c2[,n]) returns the value of the specified parameter c2 of the specified namespace c1 after the specified length of n is intercepted.
Oracle9i provides a built-in namespace userenv that describes the information for the current session, with the following parameters:
current_schema: current mode name;
current_user: Current user;
ip_address: Current client IP address;
os_user: Current client operating system user;
Wait dozens of items, more detailed parameter columns please refer to Oracle Online Documents directly

For example: SELECT sys_context (' USERENV ', ' Session_user ') from DUAL; Note: n denotes a numeric type, C represents a character type, D represents a date type, [] indicates that the inner parameter can be ignored and FMT represents the format.

A single value function returns a single value in the query and can be applied to the Select,where clause, the start with, and the Connect by clause and the HAVING clause.
(i). Numeric type function (number functions)
A numeric function enters a numeric parameter and returns the value of a numeric type. Most of the return values of this type of function support 38-bit decimal points, such as: COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH support 36-bit decimal points. ACOS, ASIN, Atan, and ATAN2 support 30-bit decimal points.

1, ABS (n) returns the absolute value of the number
For example: SELECT ABS ( -1000000.01) from DUAL;

2, cos (n) returns the cosine value of n
For example: SELECT COS ( -2) from DUAL;

3, ACOS (n) inverse cosine function, n between-1 and 1, return value between 0 and pi.
For example: SELECT ACOS (0.9) from DUAL;

4, Bitand (N1,N2) bit and operation, this is so interesting, although did not expect to use where, detailed description:
Assuming that 3,9 do bit and operation, 3 binary form is: 0011,9 binary form is: 1001, then the result is 0001, converted to 10 in the number of 1.
For example: SELECT Bitand (3,9) from DUAL;

5, Ceil (n) returns the smallest integer value greater than or equal to n
For example: SELECT ceil (18.2) from DUAL;
Quiz you, guess what the value of ceil (-18.2) would be?

6, FLOOR (n) returns the maximum integer value less than or equal to n
For example: SELECT FLOOR (2.2) from DUAL;
And guess what the value of floor (-2.2) would be?

7, Bin_to_num (n1,n2,.... N) binary Turn decimal
For example: SELECT bin_to_num (1), Bin_to_num (1,0), Bin_to_num (1,1) from DUAL;

8, SIN (n) returns the positive value of N, N is radians.
For example: SELECT SIN (a) from DUAL;

9, SINH (n) returns the hyperbolic positive value of N, N is radians.
For example: SELECT SINH from DUAL;

10, ASIN (n) The inverse of the black function, n between-1 and 1, return value between PI/2 AND-PI/2.
For example: SELECT ASIN (0.8) from DUAL;

11, TAN (n) returns the tangent of N, N is radians
For example: SELECT TAN (0.8) from DUAL;

12, TANH (n) returns the hyperbolic tangent of N, N is radians
For example: SELECT TANH (0.8) from DUAL;

13, Atan (n) inverse tangent function, n represents radians, return value between PI/2 AND-PI/2.
For example: SELECT atan ( -444444.9999999) from DUAL;

14, EXP (n) returns the n power of e, E = 2.71828183 ...
For example: SELECT EXP (3) from DUAL;

15, LN (n) returns the natural logarithm of N, n>0
For example: SELECT LN (0.9) from DUAL;

16, log (N1,N2) returns the logarithm of the N1 as the base N2, N1 >0 and not 1, n2>0
For example: SELECT LOG (1.1,2.2) from DUAL;

17, Power (N1,N2) return to N1 N2 secondary side. N1,N2 can be any value, but if M is a negative number, then n must be an integer
For example: SELECT power (2.2,2.2) from DUAL;

18, SIGN (n) if n<0 returns 1, if n>0 returns 1 if n=0 returns 0.
For example: SELECT SIGN, SIGN ( -14), SIGN (0) from DUAL;

19, SQRT (n) returns the square root of N, and N is radians. N>=0
For example: SELECT SQRT (0.1) from DUAL;

(ii). Character function returns the value of a character (Character functions returning Character values)
The class function returns the same type as the input type.
 The returned char type value is no longer than 2000 bytes;
 The returned VCHAR2 type value is not longer than 4000 bytes;
If the length of the character that should be returned above is exceeded, Oracle will not make an error but directly truncate to the maximum supporting length return.

 The returned CLOB type value is not longer than 4G;
For functions of type CLOB, if the return value is longer than that, Oracle will not return any errors but simply throw the error.

1, CHR (n[USING Nchar_cs]) returns the character of the specified value in the current character set
For example: SELECT CHR from DUAL;

2, CONCAT (C1,C2) connection string, equivalent to | |
For example: SELECT concat (' aa ', ' BB ') from DUAL;

3. Initcap (c) Converts the first letter of the word in the string to uppercase, and the other converts to lowercase
For example: SELECT initcap (' WhaT is this ') from DUAL;

4, Nls_initcap (c) returns the specified string and capitalizes the first letter in the string, and the other letters are lowercase
For example: SELECT nls_initcap (' Zhonghua Minzhu ') from DUAL;
It also has a parameter: Nlsparam is used to specify the collation, which can be ignored, and the default state is the collation of the current session.

(iii). Character function returns a numeric value (Character functions returning number values)
This class of functions supports all data types
1, ASCII (c) and the use of the CHR function just in contrast, this function returns the value of the specified character corresponding to the current character set.
For example: SELECT ASCII ('_') from DUAL;

(iv). Date function (DateTime functions)
In this type of function, the date is returned except for the value returned by the Months_between.
1, Current_timestamp ([n]) returns the date and time of the time zone in which the current session is located. n represents the millisecond precision, not greater than 6
For example: SELECT Current_timestamp (3) from DUAL;

2, Localtimestamp ([n]), with the same, returns the date and time of the current session's time zone. n represents the millisecond precision, not greater than 6
For example: SELECT Localtimestamp (3) from DUAL;

3, Systimestamp ([n]) with the same, returns the date and time of the current database's time zone, n represents the millisecond precision, >0 and <6
For example: SELECT Systimestamp (4) from DUAL;

4, Dbtimezone return the current time zone of the database
For example: SELECT dbtimezone from DUAL;

5, Sessiontimezone return the current session in the time zone
For example: SELECT sessiontimezone from DUAL;

6, EXTRACT (key from date) key= (Year,month,day,hour,minute,second) mentions the specified date column from the specified time
For example: SELECT EXTRACT (year from sysdate) from DUAL;

7, To_timestamp (C1[,FMT]) converts the specified character to the TIMESTAMP format in the specified format.
For example: SELECT to_timestamp (' 2007-8-22 ', ' yyyy-mm-dd HH:MI:SS ') from DUAL;

(v). Conversion function (conversion functions)
The conversion function converts the specified character from one type to another, which is usually followed by the following conventions: The function name is followed by the type to be converted and the output type.

1, Bin_to_num (N1,N2...N) converts a set of bit vectors to an equivalent decimal form.
For example: SELECT bin_to_num (1,1,0) from DUAL;

2. Cast (c as Newtype) converts the specified string to the specified type, which is basically valid only for character types, such as Char,number,date,rowid. This class of transformations has a specialized table listing which types can be converted to which type, and this is not described here as sprinkle.
For example: SELECT CAST (' 1101 ' as number (5)) from DUAL;

3. Chartorowid (c) converts a string to a ROWID type
For example: SELECT chartorowid (' a003d1abbefaabsaa0 ') from DUAL;

4, Rowidtochar (ROWID) Conversion rowid value is VARCHAR2 type. Returns a string length of 18 bytes.
For example: SELECT Rowidtochar (ROWID) from DUAL;

5, To_multi_byte (c) converts the specified character to full angle and returns a char type string
For example: SELECT to_multi_byte (' abc abc China ') from DUAL;

6, To_single_byte (c) converts the specified character to Half-width and returns a char type string
For example: SELECT to_single_byte (' abc abc China ') from DUAL;

(vi). Other auxiliary functions (miscellaneous Single-row functions)
1, COALESCE (n1,n2,.... N) returns the first Non-null value in the sequence
For example: SELECT coalesce (null,5,6,null,9) from DUAL;

2, DUMP (Exp[,fmt[,start[,length]])
Dump is a very powerful function that is quite useful for people who have a deep understanding of Oracle storage. So for those of us who are just applications, we don't know where to apply them. Only use here, do not do in-depth analysis of its functions.

As shown above, dump has a lot of parameters. The essence is to return the VARCHAR2 value of the internal representation of exp for the specified length in the specified form. FMT contains 4 kinds of formats: 8| | 10| | 16| | 17, respectively, 8, 10, 16 and given, the default is 10-in-system. The start parameter represents the starting position, length as the number of strings separated.
For example: SELECT DUMP (' ABCDEFG ', 17,2,4) from DUAL;

3. Empty_blob,empty_clob These two functions return an empty LOB type and are typically used in statements such as INSERT and update to initialize LOB columns, or to empty them. Empty indicates that the LOB has been initialized, except that it has not been used to store the data.

4, Nls_charset_name (n) returns the character set name corresponding to the specified value.
For example: SELECT nls_charset_name (1) from DUAL;

5, nls_charset_id (c) Returns the character set ID corresponding to the specified character.
For example: SELECT nls_charset_id (' Us7ascii ') from DUAL;

6, Nls_charset_decl_len (N1,N2) returns the declaration width of a nchar value, in characters. N1 is the length of the value in bytes, N2 is the character set ID of the value
For example: SELECT Nls_charset_decl_len (nls_charset_id (' Us7ascii ')) from DUAL;

7, SYS_EXTRACT_UTC (timestamp) Return standard Universal time that is GMT.
For example: SELECT SYS_EXTRACT_UTC (Current_timestamp) from DUAL;

8, Sys_typeid (object_type) returns the ID of the object type.
For example: This one, no custom object has been established, I do an example.

9. UID returns an integer that uniquely identifies the current database user.
For example: SELECT UID from DUAL;

10, user returns the current session users
For example: SELECT USER from DUAL;

11, USERENV (c) The function is used to return information about the current session, which, according to the Oracle documentation, is USERENV to maintain backward-compatible legacy functions. Oracle Company recommends that you use the Sys_context function to call the Userenv namespace to get the information, so you can understand.
For example: SELECT USERENV (' LANGUAGE ') from DUAL;

12, Vsize (c) Returns the number of bytes in C.
For example: SELECT vsize (' ABC China ') from DUAL;