Search This Blog

Monday, July 22, 2019

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

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

Friday, July 19, 2019

Strong Authentication

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; 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;

Sunday, July 7, 2019

Know Details About SQLERRM Function

This function retrieves the detailed SQLERRM of the last exception raised.
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
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

set_session_state APEX_UTIL.set_session_state ('AI_USERNAME', m_user_name);

Restrict File Upload by File Type in Oracle Apex

If you want to restrict file upload by file type/extension/format you can follow the below steps.  Goto File Browser Item --> Advanced --...