Uncommon Functions and Objects In Oracle SQL and PL/SQL

1. TO_CHAR(Date,'fmDay Month fmDD, YYYY'): 'fm' prefix can be remove Embedded spaces       from value.


2. NVL2( value , value_if_not_null, value_if_null )

3. In NVL and NVL2

NVL2(inv_date,'Pending','Incomplete')               
=> True (value_if_not_null and value_if_null are same type)

NVL2(inv_amt,inv_date,'Not Available')                                       False

NVL2(inv_date,sysdate-inv_date,sysdate)                   
=> True (value_if_not_null and value_if_null are same type)

NVL2(inv_amt,inv_amt*.25,'Not Available')                                False

NVL(ADD_MONTHS(END_DATE,1),SYSDATE)             
=> True (value and value_if_null are same type )

TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE))               False

NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing')     False

NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing')
=> True (value_if_null is convert to value_if_null type)

NVL(TO_CHAR(cust_credit_limit*.15),'Not Available')       
=>  True (value_if_null is convert to value_if_null type)

TO_CHAR(NVL(cust_credit_limit*.15,'Not Available'))             False

NVL(cust_credit_limit*.15,'Not Available')                                  False

NVL(cust_credit_limit,'Not Available')                                         False

4. NULLIF
If both value is same then return null or retrun first value.
SELECT NULLIF(1, 1) FROM emp; Return : null
SELECT NULLIF(1, 2) FROM emp; Return : 1
SELECT NULLIF(null, 2) FROM emp; Return : null

5. COALESCE
COALESCE (value1, value2, value3) is equal to

CASE
WHEN value1 is NOT NULL THEN value1
WHEN value2 is NOT NULL THEN value2
Else value3
END

Strong Authentication

CREATE TABLE  "OTSL_USERS"
   ( "USER_ID" NUMBER,
"USER_NAME" VARCHAR2(100),
"PASSWORD" VARCHAR2(4000),
"CREATED_ON" DATE,
"DEPARTMENT" VARCHAR2(200),
"AUT_SECTION" VARCHAR2(200),
"EXPIRES_ON" DATE,
"ADMIN_USER" CHAR(1),
"FACTORY_NAME" VARCHAR2(200),
"ACCESSCODE" NUMBER(20,0),
"ACTIVE" CHAR(2),
"PHOTO" BLOB,
"SCHOOL_NAME" VARCHAR2(200)
   )
 
 ==================================

CREATE OR REPLACE TRIGGER  "USER_T1" BEFORE INSERT OR UPDATE ON OTSL_USERS FOR EACH ROW
BEGIN

:NEW.PASSWORD:=CUSTOM_HASH(UPPER(:NEW.USER_NAME),:NEW.PASSWORD);

if inserting then
IF :NEW.USER_ID IS NULL THEN
SELECT NVL(MAX(USER_ID)+1,1) INTO :NEW.USER_ID FROM OTSL_USERS;
END if;
end if;
END;

/
ALTER TRIGGER  "USER_T1" ENABLE
/

==========================================
create or replace function custom_hash (p_username in varchar2, p_password in varchar2)
return varchar2
is
  l_password varchar2(4000);
  l_salt varchar2(4000) := 'UYUKQULATJYQ2RJTWPTHHIU7K1O7FR';
begin

-- This function should be wrapped, as the hash algorhythm is exposed here.
-- You can change the value of l_salt or the method of which to call the
-- DBMS_OBFUSCATOIN toolkit, but you much reset all of your passwords
-- if you choose to do this.

l_password := utl_raw.cast_to_raw(dbms_obfuscation_toolkit.md5
  (input_string => p_password || substr(l_salt,10,13) || p_username ||
    substr(l_salt, 4,10)));
return l_password;
end;
=========================================
create or replace function custom_auth (p_username in VARCHAR2, p_password in VARCHAR2)
return BOOLEAN
is
  l_password varchar2(4000);
  l_stored_password varchar2(4000);
  l_expires_on date;
   t NUMBER;
  l_count number;
begin
t:=0;
select count(*) into l_count from otsl_users where user_name = p_username;
if l_count > 0 and t=0 then
  select password, expires_on into l_stored_password, l_expires_on
   from otsl_users where user_name = p_username;
  if l_expires_on > sysdate or l_expires_on is null then
    l_password := custom_hash(p_username, p_password);
    if l_password = l_stored_password then
      return true;
    else
      return false;
    end if;
  else
    return false;
end if;
else
  return false;
end if;
end;

Know Details About SQLERRM Function

This function retrieves the detailed SQLERRM of the last exception raised.

Syntax

UTL_HTTP.get_detailed_sqlerrm 
RETURN VARCHAR2;

EXAMPLE:
-----------------------
1. EXECUTE THE BELOW ANONYMOUS BLOCK
DECLARE
   l_request   UTL_HTTP.req;
BEGIN
   l_request := UTL_HTTP.begin_request ('http://www.some_company.com');
END;

2. NOW RUN THE BELOW SQL COMMAND

select utl_http.get_detailed_sqlerrm from dual

Value Assign On Application Item From Backend in Oracle Apex

APEX_UTIL.set_session_state ('AI_USERNAME', m_user_name);