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

Export Oracle Apex Application From Database

If you want to export your apex application from database not from application, you can execute the below command

"SELECT APEX_050100.WWV_FLOW_EXPORT_API.GET_APPLICATION (your application id) d FROM DUAL"

some times your application server is not working but your DB server is ok and you need to export your application right now, then you need this function to export your application.

N.B
-------

this function work on 5.1.4 or later version

Install Oracle Apex 19.1 on Oracle 11g

SELECT NAME FROM V$DATAFILE ;

 KEEP THE PATH FROM OTHER TABLESPACE.

 Step-1 CREATE TABLESPACE APEX DATAFILE 'D:\APP\JEWEL\ORADATA\ORCL\MFIMIS\APEX.DBF' SIZE 10M AUTOEXTEND ON NEXT 20M;

 Step-2 @APEXINS.SQL APEX1 APEX1 TEMP /i/

 Step-3 @APXCHPWD.SQL

 Step-4 @APXLDIMG E:\apex_19.1 (This path is apex folder's immediate previous directory )

 Step-5 @apex_epg_config.sql E:\apex_19.1 (This path is apex folder's immediate previous directory )

 Step-6 EXEC DBMS_XDB.SETHTTPPORT(8888) ;

 Step-7  ALTER USER ANONYMOUS ACCOUNT UNLOCK ;
 ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
 ALTER USER APEX_PUBLIC_USER IDENTIFIED BY vinish;

Calling JS by ID

$("td[headers=myimgid]").click(function(){     // myimgid is a ID

 (I use it for Image Size)

    ShowLargeImage(this);
});
<title>
<span style="font-family: "courier new" , "courier" , monospace;">$("td[headers=myimgid]").click(function(){    </span> // myimgid is a ID<br />
 (I use it for Image Size)<br />
<span style="font-family: "courier new" , "courier" , monospace;">    ShowLargeImage(this);</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">  </span><br />
<span style="font-family: "courier new" , "courier" , monospace;">});</span>

Multi Table Update In One Update Query

UPDATE (SELECT B.CHECK_V AS OLD, B.CHECK_V AS NEW

          FROM MAS A INNER JOIN DTL B ON A.ID = B.SECTION_ID

         WHERE A.DOCNUMBR = :P6_DOC AND B.ROWID = :ROWID_X) T

   SET T.NEW = :CHECK_V;
<title>
<span style="font-family: "courier new" , "courier" , monospace;">UPDATE (SELECT B.CHECK_V AS OLD, B.CHECK_V AS NEW</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">          FROM MAS A INNER JOIN DTL B ON A.ID = B.SECTION_ID</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">         WHERE A.DOCNUMBR = :P6_DOC AND B.ROWID = :ROWID_X) T</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">   SET T.NEW = :CHECK_V;</span>

Example Code Of MERGE Insert/ Update