ADVANCE FUNCTION

BFilename Function

First, we need to create a directory object called exampleDir that points to /example/totn on the file server.

CREATE DIRECTORY exampleDir AS 'C:\TEMP\';

Then we can use the exampleDir directory object in the bfilename function as follows:

SELECT bfilename('exampleDir', 'totn_logo.jpg') FROM dual;


CASE - It has the functionality of an IF-THEN-ELSE statement.

select table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;


select table_name,
CASE
WHEN owner='SYS' THEN 'The owner is SYS'
WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;

select supplier_id,
CASE
WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'
WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office'
END
from suppliers;

select
CASE
WHEN a < style="font-weight: bold;">COALSCE - function returns the first non-null expression in the list. If all expressions evaluate to null, then the coalesce function will return null.

SELECT coalesce( address1, address2, address3 ) result FROM SCMSUPPLIERSITES


DECODE - Function has the functionality of an IF-THEN-ELSE statement.
SELECT supplier_name,
decode(supplier_id, 10000, 'IBM',
10001, 'Microsoft',
10002, 'Hewlett Packard',
'Gateway') result
FROM suppliers;


LAG function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from a previous row in the table.

select headerid,itemid, requesteddate,
lag (requesteddate,1) over (ORDER BY requesteddate) AS prev_order_date
from POSLINES
where itemid = 38780;


LEAD function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from the next row in the table

select headerid,itemid, requesteddate,
LEAD (requesteddate,1) over (ORDER BY requesteddate) AS NEXT_order_date
from POSLINES
where itemid = 38780;


LNNVL - unction is used in the WHERE clause of an SQL statement to evaluate a condition when one of the operands may contain a NULL value.

select * from STOCKITEMS where LNNVL(qty >= reorder_level);

Condition Evaluates To LNNVL Return Value
TRUE FALSE
FALSE TRUE
UNKNOWN TRUE

NULLIF

SELECT NULLIF(12, 12) FROM DUAL -- would return NULL
SELECT NULLIF(12, 13) FROM DUAL -- would return 12
SELECT NULLIF('apples', 'apples') FROM DUAL -- would return NULL
SELECT NULLIF('apples', 'oranges') FROM DUAL -- would return 'apples'
SELECT NULLIF(NULL, 12) FROM DUAL -- would return an ORA-00932 error because expr1 can not be the literal NULL


NVL Function lets you substitute a value when a null value is encountered.
select HEADERID, NVL(CUSTOMERORDERREFERENCE, 'not found') cust_order_ref
from POSHEADERS


NVL2 function extends the functionality found in the NVL function. It lets you substitutes a value when a null value is encountered as well as when a non-null value is encountered.

select HEADERID, NVL2(CUSTOMERORDERREFERENCE, 'AVAILABLE','not found') cust_order_ref from POSHEADERS


sys_context function can be used to retrieve information about the Oracle environment.

SELECT sys_context('USERENV', 'TERMINAL') FROM DUAL
SELECT sys_context('USERENV', 'OS_USER') FROM DUAL
SELECT sys_context('USERENV', 'SESSION_USER') FROM DUAL
SELECT sys_context('USERENV', 'SESSION_USERID') FROM DUAL
SELECT sys_context('USERENV', 'TERMINAL') FROM DUAL
SELECT sys_context('USERENV', 'LANGUAGE') FROM DUAL
SELECT sys_context('USERENV', 'IP_ADDRESS') FROM DUAL
SELECT sys_context('USERENV', 'HOST') FROM DUAL
SELECT sys_context('USERENV', 'NETWORK_PROTOCOL') FROM DUAL



uid function returns the id number for a user's session (the user who is currently logged in).
user function returns the user_id from the current Oracle session.

select uid,USER from dual;

SELECT userenv('TERMINAL') FROM DUAL -- would return FALSE
SELECT userenv('LANGUAGE') FROM DUAL -- would return 'AMERICAN_AMERICA.WE8DEC'