Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

LNNVL Function in Oracle SQL – PLSQL

by

The LNNVL function in Oracle SQL / PLSQL is used in the WHERE Clause of an SQL / PLSQL SELECT Statement. LNNVL Function is used to evaluate a condition where one of the operands of the condition may have NULL value.

Syntax for the LNNVL function in Oracle SQL / PLSQL is:

SELECT column(s)
FROM table_name
WHERE LNNVL (condition);

LNNVL Returns the values based on the table below:

Condition_Evaluation_Value LNNVL Return
TRUE FALSE
FALSE TRUE
UNKNOWN TRUE

Example:

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support 5
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 5
107 Emp G 12000 Sales
108 Emp H 12000 Support

If we write our query as:

 SELECT *
FROM employee
WHERE commission < 15;

We will get the following output:

Employee_ID Employee_Name Salary Department Commission
104 Emp D 30000 Support 5
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 5

Suppose we wish to see the records where commission is less than equal to 15 and also could be NULL, then we can achieve the same using the LNNVL function as:

 SELECT *
FROM employee
WHERE LNNVL(commission >= 15);

We will get the following output:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales  
104 Emp D 30000 Support 5
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 5
107 Emp G 12000 Sales  
108 Emp H 12000 Support  

In the above fetched results observe that the records for NULL commission are also included.

GROUP_ID Function in Oracle SQL – PLSQL

by

The GROUP_ID function in Oracle SQL / PLSQL is used to assign a unique group id to each group returned by a SQL / PLSQL SELECT statement using GROUP BY Clause.

GROUP_ID is mainly used to identify duplicate groups in the query results, the GROUP_ID function in Oracle SQL / PLSQL will return 0 for each unique group and whenever a duplicate group is found the GROUP_ID function will return a value that is > 0.

Syntax for the GROUP_ID function in Oracle SQL / PLSQL is:
SELECT column(s)
FROM table_name
WHERE condition(s)
GROUP BY column(s);

Example:
Using GROUP_ID in Oracle SQL / PLSQL Select Statement.

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support 5
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 5
107 Emp G 12000 Sales
108 Emp H 12000 Support

If we write our query as:

 SELECT employee_name
,commission
,salary
,GROUP_ID()
FROM employee
GROUP BY commission
         ,salary
         ,employee_name
         ,ROLLUP(commission,salary);

We will get the following output:

Employee_ID Commission Salary GROUP_ID
Emp H 12000 0
Emp B 20 20000 0
Emp G 12000 0
Emp A 10000 0
Emp C 20 28000 0
Emp D 5 30000 0
Emp E 10 32000 0
Emp F 5 20000 0
Emp H 12000 2
Emp B 20 20000 2
Emp G 12000 2
Emp A 10000 2
Emp C 20 28000 2
Emp D 5 30000 2
Emp E 10 32000 2
Emp F 5 20000 2
Emp H 12000 1
Emp B 20 20000 1
Emp G 12000 1
Emp A 10000 1
Emp C 20 28000 1
Emp D 5 30000 1
Emp E 10 32000 1
Emp F 5 20000 1

We can use the HAVING CLAUSE to remove the duplicate groups from the above fetched records as:

SELECT employee_name
,commission
,salary
,GROUP_ID()
FROM employee
GROUP BY commission
         ,salary
         ,employee_name
         ,ROLLUP(commission,salary)
HAVING GROUP_ID() &lt; 1;

We will get the following output:

Employee_ID Commission Salary GROUP_ID
Emp H 12000 0
Emp B 20 20000 0
Emp G 12000 0
Emp A 10000 0
Emp C 20 28000 0
Emp D 5 30000 0
Emp E 10 32000 0
Emp F 5 20000 0

Here we can observe that we have fetched only unique group of records using HAVING in Oracle SQL / PLSQL SELECT statement.

CARDINALITY Function in Oracle SQL – PLSQL

by

Oracle SQL / PLSQL CARDINALITY function is used to return the number of elements in a nested table.

Oracle SQL / PLSQL syntax for the CARDINALITY function is:

SELECT CARDINALITY (nested_column)
FROM table_name;

nested_column is the column in the nested table whose CARDINALITY we want to determine.

If nested table is empty or is a NULL collection then CARDINALITY function in Oracle SQL / PLSQL will return NULL.

Using Oracle SQL Cardinality Function with SELECT Statement 

SELECT employee_id
       ,CARDINALITY(location)
FROM employee;

The example above shows the use of Oracle SQL Cardinality Function with SQL SELCt Statement.

BFILENAME Function in Oracle SQL – PLSQL

by

The BFILENAME function in Oracle SQL / PLSQL is used to get a BFILE locator for a LOB file at a physical location.

Syntax for the BFILENAME function in Oracle SQL / PLSQL is:

SELECT BFILENAME(‘directory’, ‘file_name’)
FROM table_name;

  • directory is the folder / directory object which serves as an alias name for the full path where the file is placed on the file server.
  • file_name is the name of the file on the server.

Example:

First we need to create a directory say ‘sampleDir’ that will point to the a location say ‘/sample/files/techhoney’ location on the server as follows:

CREATE DIRECTORY sampleDir as '/sample/files/techhoney';

Now we can use the ‘sampleDir’ directory object in the BFILENAME function as shown below:

SELECT BFILENAME('sampleDir','report.pdf')
FROM dual;

TO_YMInterval Function in Oracle SQL – PLSQL

by

The TO_YMInterval function in Oracle SQL / PLSQL is used to convert a string value to Interval Year to Month type.

Syntax for the TO_YMInterval function in Oracle SQL / PLSQL is:

SELECT TO_YMInterval(character_1)
FROM table_name;

character_1 can be CHAR, VARCHAR2, NCHAR OR NVARCHAR2 data type, it is the value that will be converted to Interval Year to Month.

Examples:

SELECT TO_YMINTERVAL('5-11')
FROM dual;

Would return 5 years and 11 months as Year to Month Interval type