Other Functions
Other Functions#
BASE64_DECODE#
Syntax#
BASE64_DECODE(expr)
Description#
This function decodes an input VARBYTE type string, which has been encoded with a base64 format, to return a raw data of the VARBYTE type.
Example#
SELECT BASE64_DECODE(VARBYTE'71673D3D') FROM DUAL;
BASE64_DECODE(VARBYTE'71673D3D')
-----------------------------
AA
1 row selected.
BASE64_DECODE_STR#
Syntax#
BASE64_DECODE_STR(expr)
Description#
BASE64_DECODE_STR reads the base 64-encoded input string and decodes it to its original value in hexadecimal format string. The return type is VARCHAR.
expr must be consisted of following characters: 0-9, a-z, A-Z, +, or /. Its length must be multiples of 4. '=' can be used as a padding character to make the input length multiples of 4.
Example#
SELECT BASE64_DECODE_STR('qw==') FROM DUAL;
BASE64_DECODE_STR('qw==')
-----------------------------
AB
1 row selected.
BASE64_ENCODE#
Syntax#
BASE64_ENCODE(expr)
Description#
This function encodes the VARBYTE type values to a base64 form in order to return a VARBYTE type string.
Example#
SELECT BASE64_ENCODE(VARBYTE'AA') FROM DUAL;
BASE64_ENCODE(VARBYTE'AA')
-----------------------------
71673D3D
1 row selected.
BASE64_ENCODE_STR#
Syntax#
BASE64_ENCODE_STR(expr)
Description#
BASE64_ENCODE_STR reads a hexadecimal string and encodes it to a base 64-encoded string. The return type is VARCHAR.
expr must be consisted of following characters: 0-9, a-z, or A-Z. Its length must be multples of 2.
Example#
SELECT BASE64_ENCODE_STR('AB') FROM DUAL;
BASE64_ENCODE_STR('AB')
---------------------------
qw==
1 row selected.
BINARY_LENGTH#
Syntax#
BINARY_LENGTH (expr)
Description#
This function returns the length of a value in a BLOB, BYTE or NIBBLE type column.
Example#
<Query> Output the length of three binary data type values
CREATE TABLE t1 (i1 BLOB, i2 Byte(10), i3 NIBBLE(10) );
INSERT INTO t1 VALUES ( BLOB'3FD', Byte'123FD', NIBBLE'90BCD');
SELECT BINARY_LENGTH (i1)
, BINARY_LENGTH (i2)
, BINARY_LENGTH (i3)
FROM t1;
BINARY_LENGTH (I1) BINARY_LENGTH (I2) BINARY_LENGTH (I3)
------------------------------------------------------
2 10 5
1 row selected.
CASE2#
Syntax#
CASE2 (expr1, ret_expr1,
[,expr2, ret_expr2,..,]
[, default])
Description#
CASE2 evaluates expr1 and returns return_expr1 if expr1 evaluates to TRUE. If expr1 evaluates to FALSE, expr2 is evaluated, and if expr2 evaluates to TRUE, return_expr2 is returned. This process continues until a TRUE expression is found. If none of the expressions evaluate to TRUE, default is returned. If none of the expressions evaluate to TRUE and default is not specified, NULL is returned.
Example#
<Query> Return employees' salaries. Substitute the word "HIGH" for every employee whose monthly pay is greater than 2000 dollars, the word "LOW" for every employee whose monthly pay is less than 1500, and "NULL" for employees without salary information. Return the actual salary for employees whose monthly pay is between 1500 and 2000 dollars.
SELECT e_lastname
, e_firstname
, emp_job
, CASE2(salary > 2000, 'HIGH', salary < 1500, 'LOW', salary IS NULL, 'NULL', TO_CHAR(salary)) salary
FROM employees;
E_LASTNAME E_FIRSTNAME EMP_JOB SALARY
---------------------------------------------------------------------------
Moon Chan-seung CEO NULL
Davenport Susan designer 1500
Kobain Ken engineer 2000
Foster Aaron PL 1800
Ghorbani Farhad PL HIGH
Momoi Ryu programmer 1700
Fleischer Gottlieb manager LOW
Wang Xiong manager NULL
.
.
.
20 rows selected.
CASE WHEN#
Syntax#
case
simple_case_expr
searched_case_expr
else_clause
Description#
When CASE WHEN is used with searched_case_expr, it is the same as CASE2. That is, it returns return_expr for the first condition that evaluates to TRUE. If all of the specified condition evaluate to FALSE, CASE WHEN returns else_expr if specified, or NULL otherwise. When using this function in this way, a variety of comparison operators can be used.
When CASE WHEN is used with simple_case_expr, expr is compared with each of comparison_expr using the equality operator ("=").
Example#
<Query> Output 'aaaaa' if the value of the third character in column c1 is a, output 'bbbbb' if it is b, output 'ccccc' if it is c, and output 'zzzzz' otherwise.
CREATE TABLE test (c1 CHAR(10));
INSERT INTO test VALUES('abcdefghi');
SELECT CASE SUBSTRING(c1, 3, 1)
WHEN 'a' THEN 'aaaaa'
WHEN 'b' THEN 'bbbbb'
WHEN 'c' THEN 'ccccc'
END
FROM test;
CASESUBSTRING(C1,3,1)WHEN'A'THEN'AAAAA'WHE
----------------------------------------------
ccccc
1 row selected.
COALESCE#
Syntax#
COALESCE (expr1, expr2, …, exprn)
Description#
COALESCE returns the first non-NULL expression among the given input expressions.
If expr1 is not NULL, expr1 is returned; if expr1 is NULL, the first non-NULL expression among the following expressions is returned. If all of the input expressions are NULL, this function returns NULL.
More than two expressions must be specified for this function.
Example#
<Query> Obtain the first non-NULL expression from the input expressions.
SELECT COALESCE( NULL, 'a') FROM DUAL;
COALESCE( NULL, 'a')
------------------------
a
1 row selected.
SELECT COALESCE( NULL, NULL, NULL) FROM DUAL;
COALESCE( NULL, NULL, NULL)
------------------------------
1 row selected.
DECODE#
Syntax#
DECODE (expr, comparison_expr1, ret_expr1,
[, comparison_expr2, ret_expr2,..,]
[, default])
Description#
The functionality of DECODE is similar to that of CASE WHEN when used with simple_case_expr. That is, expr is sequentially compared with each of comparison_expr using the equality operator ("="), and the return_expr corresponding to the first comparison_expr that matches expr is returned. If none of comparison_expr match expr, default is returned. If none of comparison_expr match expr and default is not specified, NULL is returned.
Examples#
<Query> If i is NULL, 1 or 2, return the string "NULL", "ONE" or "TWO", respectively.
CREATE TABLE t2(i NUMBER);
INSERT INTO t2 VALUES(NULL);
INSERT INTO t2 VALUES(1);
INSERT INTO t2 VALUES(2);
INSERT INTO t2 VALUES(3);
SELECT DECODE(i, NULL, 'NULL', 1, 'ONE', 2, 'TWO') revised_i FROM t2;
REVISED_I
-------------
NULL
ONE
TWO
4 rows selected.
<Query> Display the current wage of all employees. Increase the displayed wage 10% for engineers, 12% for sales representatives, and 20% for managers. Display the actual wage for all other employees.
SELECT emp_job
, salary
, DECODE(RTRIM(emp_job, ' '),
'engineer', salary*1.1,
'sales rep', salary*1.12,
'manager', salary*1.20,
salary) revised_salary
FROM employees;
EMP_JOB SALARY REVISED_SALARY
------------------------------------------------
CEO
designer 1500 1500
engineer 2000 2200
engineer 1800 1980
engineer 2500 2750
programmer 1700 1700
manager 500 600
.
.
.
20 rows selected.
DIGEST#
Syntax#
DIGEST(expr, algorithm_name)
Description#
DIGEST uses a standard cryptographic hash algorithm to return the hash digest of expr as a VARCHAR type string. The algorithms currently supported in Altibase are SHA-1, SHA-256 and SHA-512.
Example#
<Query> Use the SHA-1 algorithm to obtain the digest for the input string.
SELECT DIGEST('I am a boy.', 'SHA-1') FROM DUAL;
DIGEST('I am a boy. ', 'SHA-1')
--------------------------------------------
A817613E0B781BBF01816F36A8B0DC7C98B2C0CC
1 row selected.
<Query> Use the SHA-256 and SHA-512 algorithms respectively to obtain the digest for the input strings.
SELECT DIGEST('TEST', 'SHA-256') FROM DUAL;
DIGEST('TEST', 'SHA-256')
--------------------------------------------------------------------
94EE059335E587E501CC4BF90613E0814F00A7B08BC7C648FD865A2AF6A22CC2
1 row selected.
SELECT DIGEST('TEST', 'SHA-512') FROM DUAL;
DIGEST('TEST', 'SHA-512')
------------------------------------------------------------------------------------------------------------------------------------
7BFA95A688924C47C7D22381F20CC926F524BEACB13F84E203D4BD8CB6BA2FCE81C57A5F059BF3D509926487BDE925B3BCEE0635E4F7BAEBA054E5DBA696B2BF
1 row selected.
DUMP#
Syntax#
DUMP (expr)
Description#
DUMP analyzes expr and outputs information about its type, length, and contents.
Example
<Query> Display information about the employee number and name of three employees.
SELECT DUMP(eno) emp_Number
, DUMP(e_lastname) last_Name
, DUMP(e_firstname) first_Name
FROM employees
LIMIT 3;
EMP_NUMBER LAST_NAME FIRST_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Type=INTEGER(MS949) Length=4: 1,0,0,0 Type=CHAR(MS949) Length=22: 20,0,77,111,111,110,32 Type=CHAR(MS949) Length=22: 20,0,67,104,97,110,45,
,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32 115,101,117,110,103,32,32,32,32,32,32,32,32,32,32
Type=INTEGER(MS949) Length=4: 2,0,0,0 Type=CHAR(MS949) Length=22: 20,0,68,97,118,101,110 Type=CHAR(MS949) Length=22: 20,0,83,117,115,97,110
,112,111,114,116,32,32,32,32,32,32,32,32,32,32,32 ,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
Type=INTEGER(MS949) Length=4: 3,0,0,0 Type=CHAR(MS949) Length=22: 20,0,75,111,98,97,105, Type=CHAR(MS949) Length=22: 20,0,75,101,110,32,32,
110,32,32,32,32,32,32,32,32,32,32,32,32,32,32 32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
3 rows selected.
EMPTY_BLOB, EMPTY_CLOB#
Syntax#
EMPTY_BLOB()
EMPTY_CLOB()
Description#
This function can be used in INSERT and UPDATE, and this function. This indicates the empty state in which LOB column has been initialized, but data is not visible. Therefore, the column using this function has NOT NULL state.
Example#
CREATE TABLE test (id NUMBER, text CLOB);
INSERT INTO test VALUES (1, NULL);
INSERT INTO test VALUES (2, 'some data is here');
INSERT INTO test VALUES (3, EMPTY_CLOB());
SELECT * FROM test WHERE text IS NULL;
ID TEXT
------------------------------------------------
1
1 row selected.
SELECT * FROM test WHERE text IS NOT NULL;
ID TEXT
------------------------------------------------
2 some data is here
3
2 rows selected.
GREATEST#
Syntax#
GREATEST (expr1 [, expr2, expr3…])
Description#
GREATEST returns the input expression having the greatest value, that is, the one that would be last if the expressions were sorted in alphabetical order. The return type is VARCHAR.
Example#
<Query> Return the expression that would be last if the input expressions were sorted in alphabetical order.
SELECT GREATEST('HARRY', 'HARRIOT', 'HAROLD') greatest FROM DUAL;
GREATEST
------------
HARRY
1 row selected.
GROUPING#
Syntax#
GROUPING(expr1)
Description#
If the value of the column taken as an argument for this function, is evaluated to return a row with a NULL value, after being operated on by the ROLLUP, CUBE or GROUPING SETS operator, this function returns a 1; rows for which this function returns a 1 are subtotals. This function returns a 0 for all other rows.
Only expressions contained in the GROUP BY clause can be taken as arguments for this function.
Example#
Refer to the example of the GROUPING_ID function.
HOST_NAME#
Syntax#
HOST_NAME ()
Description#
This function returns the name of host which is currently being accessed. The maximum size is 64 and the conversion type is VARCHAR.
Example#
SELECT HOST_NAME() FROM DUAL;
HOST_NAME()
--------------------------------------------------
HDB-NODE1
1 row selected.
GROUPING_ID#
Syntax#
GROUPING_ID (expr1 [, expr2, expr3…])
Description#
This function returns binary numbers corresponding to bit character strings that demonstrate, whether or not the values selected by each expr taken as arguments for this function return NULL, when operated on by the ROLLUP, CUBE or GROUPING SETS in the GROUP BY clause, as decimal number values.
For each row, the GROUPING_ID function combines the 1s and 0s returned for each expr argument on which the GROUPING function is operated, into a binary number and returns that value as a decimal number.
Only expressions contained in the GROUP BY clause can be taken as arguments for this function.
A maximum number of 60 arguments can be taken for this function.
Example#
<Query> Compare the GROUPING and GROUPING_ID values for each of the grouping columns.
SELECT dno
, sex
, SUM(salary)
, GROUPING(dno) d
, GROUPING(sex) s
, GROUPING_ID(dno, sex ) ds
FROM employees
GROUP BY CUBE(dno, sex);
DNO SEX SUM(SALARY) D S DS
----------------------------------------------------------------------------------------
31223 1 1 3
1001 F 2300 0 0 0
1001 M 2000 0 0 0
1001 4300 0 1 1
1002 M 2680 0 0 0
1002 2680 0 1 1
1003 F 4000 0 0 0
1003 M 5753 0 0 0
1003 9753 0 1 1
2001 M 1400 0 0 0
2001 1400 0 1 1
3001 M 1800 0 0 0
3001 1800 0 1 1
3002 M 2500 0 0 0
3002 2500 0 1 1
4001 M 3100 0 0 0
4001 3100 0 1 1
4002 F 1890 0 0 0
4002 M 2300 0 0 0
4002 4190 0 1 1
F 1500 0 0 0
1500 0 1 1
F 9690 1 0 2
M 21533 1 0 2
24 rows selected.
LEAST#
Syntax#
LEAST(expr1 [, expr2, expr3…])
Description#
LEAST returns the input expression having the lowest value, that is, the one that would come first if the expressions were sorted in alphabetical order. The return type is VARCHAR.
Example#
<Query> Return the expression that would be first if the input expressions were sorted in alphabetical order.
SELECT LEAST('HARRY','HARRIOT','HAROLD') least FROM DUAL;
LEAST
-----------
HAROLD
1 row selected.
LNNVL#
Syntax#
LNNVL (condition)
Description#
This function returns TRUE if the condition evaluates to FALSE or NULL, and FALSE if the condition evaluates to TRUE.
| Condition Return Value | LNNVL Return Value |
|---|---|
| TRUE | FALSE |
| FALSE | TRUE |
| NULL | TRUE |
Example#
<Query> Output the names and salaries of employees whose salary is lesser than 2000 or salary data does not exist.
SELECT e_firstname
, e_lastname
, salary
FROM employees
WHERE LNNVL(salary >= 2000);
E_FIRSTNAME E_LASTNAME SALARY
------------------------------------------------------------
Chan-seung Moon
Susan Davenport 1500
Aaron Foster 1800
Ryu Momoi 1700
Gottlieb Fleischer 500
Xiong Wang
Curtis Diaz 1200
Sandra Hammond 1890
Mitch Jones 980
Jason Davenport 1000
Takahiro Fubuki 1400
John Huxley 1900
Alvar Marquez 1800
William Blake
14 rows selected.
MSG_CREATE_QUEUE#
Syntax#
MSG_CREATE_QUEUE (key)
Description#
This function creates a message queue which contains specified key values. If the queue is normally created, 0 is returned. However, if a message queue with the same key value already exists or the system call fails, 1 is returned. The type of return value is INTEGER.
Example#
<Query> Create a message queue containing key value which is 1004.
SELECT MSG_CREATE_QUEUE(1004) FROM DUAL;
MSG_CREATE_QUEUE(1004)
-----------------------------
0
1 row selected.
MSG_DROP_QUEUE#
Syntax#
MSG_DROP_QUEUE(key)
Query#
This function deletes a message queue with specified key values. If the message queue is normally deleted, 0 is returned, but if there is no message queue or the system call fails, 1 is returned. The type of return value is INTEGER.
Example#
<Query> Delete a message queue containing key value that is 1004.
SELECT MSG_DROP_QUEUE(1004) FROM DUAL;
MSG_DROP_QUEUE(1004)
-----------------------------
0
1 row selected.
MSG_SND_QUEUE#
Syntax#
MSG_SND_QUEUE(key,message)
Description#
This function applies to enqueuing messages to a message queue containing specified values. If enqueuing is succeeded, 0 is returned, but 1 is returned when the size of message is larger than the buffer size(8K) or the system call fails. The type of return value is INTEGER.
Example#
<Query> Enqueue the Altibase message to a message queue containing key value which is 1004.
SELECT MSG_SND_QUEUE(1004, VARCHAR'altibase') FROM DUAL;
MSG_SND_QUEUE(1004,VARCHAR'ALTIBASE')
----------------------------------------
0
1 row selected.
MSG_RCV_QUEUE#
Syntax#
MSG_RCV_QUEUE(key)
Description#
This function applies to dequeue a message of a queue containing specified key values. If the message is normally dequeued, the message is returned; however, 1 is returned if there is no message or the system call fails. The type of return value is VARBYTE.
Example#
<Query> Dequeue a message of a queue containing key value which is 1004.
SELECT RAW_TO_VARCHAR(MSG_RCV_QUEUE(1004)) FROM DUAL;
RAW_TO_VARCHAR(MSG_RCV_QUEUE(1004))
-----------------------------
altibase
1 row selected.
NULLIF#
Syntax#
NULLIF (expr1, expr2)
Description#
This function returns NULL if the two input expressions, expr1 and expr2, are equal; otherwise, it returns expr1.
Example#
<Query> Determine whether or not the given two expressions are equal.
SELECT NULLIF(10, 9) FROM DUAL;
NULLIF(10,9)
---------------
10
1 row selected.
SELECT NULLIF(10, 10) FROM DUAL;
NULLIF(10,10)
----------------
1 row selected.
NVL#
Syntax#
NVL (expr1, expr2)
Description#
NVL replaces a NULL value with a number, date or string in the results of a query.
The data types that can be used with NVL are DATE, CHAR, and NUMBER. expr2 must be the same data type as expr1.
Example#
<Query> Retrieve the name and the wage of all employees. Display the word 'Unknown' for employees without any wage data.
SELECT e_firstname
, e_lastname
, NVL(TO_CHAR(salary), 'Unknown')
FROM employees;
E_FIRSTNAME E_LASTNAME NVL(TO_CHAR(SALARY), 'Unknown')
----------------------------------------------------------------------------
Chan-seung Moon Unknown
Susan Davenport 1500
Ken Kobain 2000
.
.
.
20 rows selected.
NVL2#
Syntax#
NVL2 (expr1, expr2, expr3)
Description#
If expr1 is not NULL, NVL2 returns expr2, but if NULL, it returns expr3.
Example#
<Query> Display the name and the wage of all employees. For employees having wage data, display a value equal to 110% of the actual wage. For those without wage data, simply display 'Unknown'.
SELECT e_firstname
, e_lastname
, salary
, NVL2(TO_CHAR(salary), TO_CHAR(salary * 1.1), 'Unknown') nvl2_salary
FROM employees;
E_FIRSTNAME E_LASTNAME SALARY NVL2_SALARY
--------------------------------------------------------------------------
Chan-seung Moon Unknown
Susan Davenport 1500 1650
Ken Kobain 2000 2200
Aaron Foster 1800 1980
.
.
.
20 rows selected.
NVL_EQUAL#
Syntax#
NVL_EQUAL (expr1, expr2, expr3)
Description#
If expr1 is NULL, compare expr2 and expr3.
If expr1 is not NULL, compare expr1 and expr3.
"NVL_EQUAL(expr1, expr2, expr3)" is equivalent to "NVL(expr1, expr2) = expr3".
In the example below, the results of both queries are the same, but the difference is that NVL_EQUAL uses the index scan, while the NVL function does not use the index.
Notice
expr1 can have a data type of DATE, CHAR, and NUMBER, and the data types of expr1, expr2, and expr3 must be the same.
To use indexes in NVL_EQUAL, expr1 must be the index column and expr3 must be a constant.
Example#
The results of the below queries are the same, but NVL_EQUAL uses indexes.
ALTER SESSION SET EXPLAIN PLAN = ON;
SELECT e_firstname
, e_lastname
FROM employees
WHERE NVL_EQUAL(TO_CHAR(salary), 'Unknown','Unknown');
E_FIRSTNAME E_LASTNAME
-----------------------------------------------
Chan-seung Moon
Xiong Wang
William Blake
3 rows selected.
ALTER SESSION SET EXPLAIN PLAN = ON;
SELECT e_firstname
, e_lastname
FROM employees
WHERE NVL(TO_CHAR(salary), 'Unknown') = 'Unknown';
E_FIRSTNAME E_LASTNAME
-----------------------------------------------
Chan-seung Moon
Xiong Wang
William Blake
3 rows selected.
NVL_NOT_EQUAL#
Syntax#
NVL_NOT_EQUAL (expr1, expr2, expr3)
Description#
If expr1 is NULL, compare expr2 and expr3.
If expr1 is not NULL, compare expr1 and expr3.
"NVL_NOT_EQUAL(expr1, expr2, expr3)" is equivalent to "NVL(expr1, expr2) != expr3".
In the example below, the results of both queries are the same, but the difference is that NVL_NOT_EQUAL uses the index scan, while the NVL function does not use the index.
Notice
expr1 can have a data type of DATE, CHAR, and NUMBER, and the data types of expr1, expr2, and expr3 must be the same.
To use indexes in NVL_NOT_EQUAL, expr1 must be the index column and expr3 must be a constant.
Example#
The results of the below queries are the same, but NVL_NOT_EQUAL uses indexes.
SELECT e_firstname, e_lastname, birth
FROM employees
WHERE NVL_NOT_EQUAL(birth, 'Unknown', 'Unknown');
_FIRSTNAME E_LASTNAME BIRTH
--------------------------------------------------------
Susan Davenport 721219
Ken Kobain 650226
Aaron Foster 820730
Ryu Momoi 790822
Gottlieb Fleischer 840417
Xiong Wang 810726
Curtis Diaz 660102
Elizabeth Bae 710213
Sandra Hammond 810211
Mitch Jones 801102
Jason Davenport 901212
Wei-Wei Chen 780509
Takahiro Fubuki 781026
13 rows selected.
SELECT e_firstname, e_lastname, birth
FROM employees
WHERE NVL(birth, 'Unknown') != 'Unknown';
E_FIRSTNAME E_LASTNAME BIRTH
--------------------------------------------------------
Susan Davenport 721219
Ken Kobain 650226
Aaron Foster 820730
Ryu Momoi 790822
Gottlieb Fleischer 840417
Xiong Wang 810726
Curtis Diaz 660102
Elizabeth Bae 710213
Sandra Hammond 810211
Mitch Jones 801102
Jason Davenport 901212
Wei-Wei Chen 780509
Takahiro Fubuki 781026
13 rows selected.
RAW_CONCAT#
Syntax#
RAW_CONCAT (expr1, expr2)
Description#
The type which are not NULL. The available input values which can be defined in the VARBYTE range from '00' to 'FF'.
Example#
<Query> Retrieve a character string connecting 'AABB' and 'CCDD' of VARBYTE type.
SELECT RAW_CONCAT(VARBYTE'AABB', VARBYTE'CCDD') FROM DUAL;
RAW_CONCAT(VARBYTE'AABB',VARBYTE'CCDD')
-------------------------------------------
AABBCCDD
1 row selected.
SELECT RAW_CONCAT(VARBYTE'AABB', VARBYTE'GGDD') FROM DUAL;
[ERR-21011 : Invalid literal]
RAW_SIZEOF#
Syntax#
RAW_SIZEOF (expr)
Description#
The RAW_SIZEOF function returns the actual size of data which is allocated in the entered expr. All of data type value can be entered, and the value including a header is returned in a BYTE unit. The value is identical with that of the length that the DUMP function prints out.
Example#
<Query> Retrieve allocated dummy size of a dual column in a table. The headers include 2 bytes unlike the SIZEOF function.
SELECT RAW_SIZEOF(DUMMY) FROM DUAL;
RAW_SIZEOF(DUMMY)
--------------------
3
1 row selected.
ROWNUM#
Syntax#
ROWNUM
Description#
ROWNUM returns a pseudo record number (pseudo rownum) as a BIGINT. The range of possible return values is between 1 and the maximum possible value of the BIGINT numeric data type.
Record numbers are assigned in the order in which records appear in a table or view. However, they can be reordered using an ORDER BY, GROUP BY or HAVING clause.
Example#
<Query> Retrieve the first three employee records, sort them in order of employee name, and output the employee number, name, and phone number.
SELECT eno, e_lastname, e_firstname, emp_tel
FROM employees
WHERE ROWNUM < 4
ORDER BY e_lastname;
ENO E_LASTNAME E_FIRSTNAME EMP_TEL
---------------------------------------------------------------------------
2 Davenport Susan 0113654540
3 Kobain Ken 0162581369
1 Moon Chan-seung 01195662365
3 rows selected.
SENDMSG#
Syntax#
SENDMSG ( VARCHAR ipaddr,
INTEGER port,
VARCHAR msg,
INTEGER ttl )
Description#
SENDMSG sends a user message to the specified IP address and port as a socket datagram. If a regular IP address is entered, a UDP datagram is sent. If a multicast IP address is entered, a multicast datagram is sent.
Multicast IP addresses are limited to the range from 225.0.0.0 - 238.0.0.255; that is, reserved multicast groups cannot be used.
The usable port range is from 1025 to 65535.
msg cannot exceed 2048 bytes.
ttl stands for time-to-live. This argument is useful when sending a message to a multicast IP address. It limits the transfer range during multicast transfer as shown below. The range of possible ttl values is from 0 to 255.
| TTL | Range |
|---|---|
| 0 | Limited to the inside of the host, cannot be output past the network interface |
| 1 | Limited to the same subnet, not forwarded beyond router |
| < 32 | Limited to the same site, organization or department |
| < 64 | Limited to the same region |
| < 128 | Limited to the same continent |
| < 255 | Unlimited, worldwide |
The return value is an INTEGER indicating the length of the message that was sent.
Example#
<Query> Send a user message to a regular IP address (in this case, the value of ttl is ignored).
SELECT SENDMSG( '192.168.1.60', 12345, 'THIS IS A MESSAGE', 1 )
FROM T1;
SENDMSG( '192.168.1.60', 12345, 'THIS IS
-------------------------------------------
17
1 row selected.
<Query> Send a user message to a multicast IP address (in this case, the value of ttl is used).
SELECT SENDMSG( '226.0.0.37', 12345, 'THIS IS A MESSAGE', 0 )
FROM T1;
SENDMSG( '192.168.1.60', 12345, 'THIS IS
-------------------------------------------
17
1 row selected.
USER_ID#
Syntax#
USER_ID()
Description#
USER_ID returns the ID of the connected user. The return type is INTEGER.
Example#
<Query> View the information about all tables owned by the current user.
SELECT TABLE_NAME
FROM SYSTEM_.SYS_TABLES_
WHERE USER_ID = USER_ID();
USER_LOCK_REQUEST#
Syntax#
USER_LOCK_REQUEST(INTEGER id)
Description#
The USER_LOCK_REQUEST is a function allowing a user to request a lock by defining id of applicable parameters. The possible values that can be entered in the parameters range from 0 to 1073741823. The return type is INTEGER, and the return value is as follows.
-
0: Successful
-
1: Timeout
-
3: Parameter error
-
4: User lock has been successfully requested witht the same ID
Example#
<Query> Request user lock whose ID is 5.
SELECT USER_LOCK_REQUEST( 5 ) FROM DUAL;
USER_LOCK_REQUEST( 5 )
-------------------------
0
1 row selected.
USER_LOCK_RELEASE#
Syntax#
USER_LOCK_RELEASE(INTEGER id)
Description#
The USER_LOCK_RELEASE is a function allowing a user to release the lock through specifying an id of the parameter. It cannot be released if the user does not have the user lock pertaining to the session.
The USER_LOCK_RELEASE is a function allowing a user to release the lock through specifying an id of the parameter. It cannot be released if the user does not have the user lock pertaining to the session.
-
0: Success
-
3: Parameter error
-
4: Impossible to unlock the user lock which is not retained
Example#
<Query> Release the user lock whose ID is 5.
SELECT USER_LOCK_RELEASE( 5 ) FROM DUAL;
USER_LOCK_RELEASE( 5 )
-------------------------
0
1 row selected.
USER_NAME#
Syntax#
USER_NAME()
Description#
USER_NAME returns the name of the connected user. The return type is VARCHAR.
Query#
<Query> View the name of the current user.
SELECT USER_NAME(), USER_ID() FROM DUAL;
SESSION_ID#
Syntax#
SESSION_ID()
Description#
SESSION_ID returns the identifier of the session with which the current user is connected. The return type is INTEGER.
Example#
<Query> Retrieve the character set in use on the currently connected client.
SELECT CLIENT_NLS FROM V$SESSION WHERE ID = SESSION_ID();
CLIENT_NLS
--------------------------------------------
US7ASCII
1 row selected.
SUBRAW#
Syntax#
SUBRAW (expr1, start [, length])
Description#
The SUBRAW function returns the byte rows range from specified values in start to the values inserted in length of a character string expr.
The start indicates a location in which the search begins within expr. The default value of start is 1; thus, the default value would be the first character. Even if it is specified as 0, the process is taken as if 1 was specified. If it is negative number, it begins to search from very end of expr.
An error would be incurred when the value of start is larger the length of expr, and a character string is returned to the end if the value of length is ommitted.
BYTE or VARBYTE data type can be input, and the type of return value is VARBYTE.
Example#
<Query> Retrieve 2 bytes from the first of the inserted character string 'AABBCC'.
SELECT SUBRAW(VARBYTE'AABBCC', 1, 2) FROM DUAL;
SUBRAW(VARBYTE'AABBCC',1,2)
-------------------------------
AABB
1 row selected.
SYS_CONNECT_BY_PATH#
Syntax#
SYS_CONNECT_BY_PATH(column, delimiter)
Description#
This function returns the path of a column value from the highest node to the current node. A delimiter can be specified at the function argument delimiter.
The return type is VARCHAR(32000).
Example#
<Query> The following example obtains the paths from the root to each id. The CAST operator is used to enhance the readability of the output results.
CREATE TABLE hier_order( id INTEGER, parent INTEGER );
INSERT INTO hier_order VALUES(0, NULL);
INSERT INTO hier_order VALUES(1, 0);
INSERT INTO hier_order VALUES(2, 1);
INSERT INTO hier_order VALUES(3, 1);
INSERT INTO hier_order VALUES(4, 1);
INSERT INTO hier_order VALUES(5, 0);
INSERT INTO hier_order VALUES(6, 0);
INSERT INTO hier_order VALUES(7, 6);
INSERT INTO hier_order VALUES(8, 7);
INSERT INTO hier_order VALUES(9, 7);
INSERT INTO hier_order VALUES(10, 6);
SELECT ID
, CAST(SYS_CONNECT_BY_PATH(ID, '/') AS VARCHAR(20)) "PATH"
FROM HIER_ORDER START WITH ID = 0 CONNECT BY PRIOR ID = PARENT
ORDER BY ID;
ID Path
-------------------------------------
0 /0
1 /0/1
2 /0/1/2
3 /0/1/3
4 /0/1/4
5 /0/5
6 /0/6
7 /0/6/7
8 /0/6/7/8
9 /0/6/7/9
10 /0/6/10
11 rows selected.
SYS_CONTEXT#
Syntax#
Description#
The SYS_CONTEXT function returns the value of parameters related to the namespace with the environment context of currently accessed session.
Parameters#
| Parameter | Result Value |
|---|---|
| CLIENT_INFO | The user session information is returned up to 128 bytes. |
| IP_ADDRESS | The connected IP address |
| ISDBA | This parameter confirms whether it is DBA privilege or not [TRUE | FALSE]. |
| LANGUAGE | The language which is being used in the user session. |
| NLS_CURRENCY | The currency of current session. |
| NLS_DATE_FORMAT | The date format of current session. |
| NLS_TERRITORY | The territory of current session. |
| ACTION | The status of modules that are specified as DBMS_APPLICATION_INFO package. |
| CURRENT_SCHEMA | The name of basic schema which is being used in current schema. |
| DB_NAME | The name specified as DB_NAME. |
| HOST | The host name connected to clients. |
| INSTANCE | ID number of current instance. |
| MODULE | The module name specified by DBMS_APPLICATION_INFO package. |
| SESSION_USER | The user name when logging into the database |
| SID | The session ID |
Example#
SELECT SYS_CONTEXT('USERENV','SID') FROM DUAL;
SYS_CONTEXT('USERENV','SID')
-------------------------------------------------------
87
SYS_GUID#
Syntax#
SYS_GUID()
Description#
SYS_GUID generates globally unique identifier made up of 16 bytes, converts it to hexadecimal string of 16 characters, and returns the final result. The return type is BYTE type.
Example#
SELECT SYS_GUID() FROM DUAL;
SYS_GUID()
------------------------------------
080027253126D13B69F892D46508FD9D
1 row selected.
SELECT DUMP(SYS_GUID()) FROM DUAL;
DUMP(SYS_GUID())
------------------------------------------------------------------------------------------------------------------------------
Type=BYTE(UTF8) Length=18: 16,0,8,0,39,37,49,38,209,59,105,248,146,214,101,8,253,157
1 row selected.
SYS_GUID_STR#
Syntax#
SYS_GUID_STR()
Description#
SYS_GUID_STR generates globally unique identifier made up of 16 bytes, converts it to hexadecimal string of 32 characters, and returns the final result. The return type is VARCHAR type.
Example#
SELECT SYS_GUID_STR() FROM DUAL;
SYS_GUID_STR
------------------------------------
A8C09B011C02856E092284D95091D27A
1 row selected.
SELECT DUMP(SYS_GUID_STR()) FROM DUAL;
DUMP(SYS_GUID_STR())
--------------------------------------------------------------------------------------------------------
Type=VARCHAR(KSC5601) Length=34: 32,0,65,56,67,48,57,66,48,49,49,67,48,50,56,53,54,69,48,57,50,50,56,52,68,67,53,48,57,49,68,50,55,65
1 row selected.
QUOTE_PRINTABLE_DECODE#
Syntax#
QUOTE_PRINTABLE_DECODE(expr)
Description#
This function decodes a VARBYTE type string, which has been encoded with a quoted printable form, in order to return the VARBYTE type raw data.
Example#
SELECT QUOTED_PRINTABLE_DECODE(VARBYTE'3D4142') FROM DUAL;
QUOTED_PRINTABLE_DECODE(VARBYTE'3D4142')
-----------------------------
AB
1 row selected.
QUOTE_PRINTABLE_ENCODE#
Syntax#
QUOTE_PRINTABLE_ENCODE(expr)
Description#
This function converts a VARBYTE type string into a quoted printable form in order to return the VARBYTE type.
Example#
SELECT QUOTED_PRINTABLE_ENCODE(VARBYTE'ABCD') FROM DUAL;
QUOTED_PRINTABLE_ENCODE('ABCD'
-----------------------------
3D41423D4344
1 row selected.
Nested Functions#
Description#
Single-row functions can be nested several layers deep. Nested functions are evaluated starting with the innermost one and working outwards.
Example#
<Query> Display the date of the first Monday six months after each employee was hired by the company.
SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS(join_date, 6), 'MONDAY'), 'DD-Mon-YYYY') monday_six_months
FROM employees
ORDER BY join_date;
MONDAY_SIX_MONTHS
--------------------------------------
26-Jul-2004
21-May-2007
05-May-2008
24-May-2010
.
.
.
20 rows selected.