Oracle APEX - How to change Default Authentication Schemes to Custom Authentication Schemes

In this post, I can explain how to change Default Authentication Schemes  to Custom Authentication Schemes in Oracle APEX.

- Database Level

Step 1: Create Table

Step 2: Insert Script

Step 3: Create Function to validate User

- APEX Application Level

Step 1:

- Go to Shared Components 

Step 2:

- Go to Security → Authentication Schemes

Step 3:

- Select Application Express Authentication - Current

Step 4:

- Change Scheme Type From Application Express Account to Custom

- Call Function name in the Authentication Function Name Text box

- Click Apply Changes button

Step 5:

- Run the Application

Step 6:

- Login using default Username and Password.  It return Invalid Login Credentials

- Login using credentials from apex_custom_auth table such as USER1/USER1 username and password respectively.

Video Link:

Youtube video Tutorial

Note:

If you need Table script, Insert script of data and Function description.  Please Mail me or Whatsapp me. Follow and Comment.

Email : oraclehuntltg@gmail.com

Whatsapp: 9944809554

Thanks & Regards

R. Kalaiyarasan

+91 9944809554

Oracle SQL - How to delete duplicate record in a Oracle table.

 In this example I can explain you how to delete duplicate record in a table using ROWID.

Step 1: Table Script

Step 2: Insert Statement

Step 3: SQL query to find duplicate record count.

 The above query return Count of records stored in the EMP_DTLS table.  All the records have duplicate record except WRK department employee record.

Step 4: How to select and delete duplicate record in a table.

* Query to find which record is going to be delete.

    The Query return duplicate records using Max ROWID.

* Delete statement to delete duplicate record.

          

 
Final Output

* After delete the duplicate data in the EMP_DTLS table.  There is not duplicate data in the table.  Please check with Step 3.

Thanks & Regards

R. Kalaiyarasan

+91 9944809554

Oracle APEX - Dynamic Navigation Menus

 In this post I explain you how to Change default navigation menu to Dynamic menu in oracle APEX.

Step 1

Create a Table. 

 CREATE TABLE BLOG_DYNAMIC_MENU(BDM_MENU_ID     VARCHAR2(25),
  BDM_MENU_DESC     VARCHAR2(50),
  BDM_PAGE_NO     NUMBER(15),
  BDM_PAR_MENU_ID     VARCHAR2(25),
  BDM_SEQ_NO     NUMBER(5),
  BDM_ICON     VARCHAR2(100),
  BDM_VISIBLE     VARCHAR2(1) DEFAULT 'Y' NOT NULL,
  CONSTRAINT BDM_PK PRIMARY KEY (BDM_MENU_ID));

Step 2

Insert records to table.

INSERT INTO blog_dynamic_menu(bdm_menu_id, bdm_menu_desc, bdm_page_no, bdm_par_menu_id, bdm_seq_no, bdm_icon, bdm_visible)
               VALUES ('HOME', 'Home', 1, NULL, 1, 'fa-home', 'Y');
 
INSERT INTO blog_dynamic_menu(bdm_menu_id, bdm_menu_desc, bdm_page_no, bdm_par_menu_id, bdm_seq_no, bdm_icon, bdm_visible)
               VALUES ('MENU_1', 'Menu 1', 2, NULL, 2, 'fa-wheelchair', 'Y');
              
INSERT INTO blog_dynamic_menu(bdm_menu_id, bdm_menu_desc, bdm_page_no, bdm_par_menu_id, bdm_seq_no, bdm_icon, bdm_visible)
               VALUES ('SUB_MENU1_1', 'Sub Menu 1.1', 3, 'MENU_1', 1, 'fa-hospital-o', 'Y');
              
INSERT INTO blog_dynamic_menu(bdm_menu_id, bdm_menu_desc, bdm_page_no, bdm_par_menu_id, bdm_seq_no, bdm_icon, bdm_visible)
               VALUES ('SUB_MENU1_2', 'Sub Menu 1.2', 4, 'MENU_1', 2, 'fa-heartbeat', 'Y');   
              
INSERT INTO blog_dynamic_menu(bdm_menu_id, bdm_menu_desc, bdm_page_no, bdm_par_menu_id, bdm_seq_no, bdm_icon, bdm_visible)
               VALUES ('MENU_2', 'Menu 2', 5, NULL, 3, 'fa-gear', 'Y');
              
INSERT INTO blog_dynamic_menu(bdm_menu_id, bdm_menu_desc, bdm_page_no, bdm_par_menu_id, bdm_seq_no, bdm_icon, bdm_visible)
               VALUES ('SUB_MENU2_1', 'Sub Menu 2.1', 6, 'MENU_2', 1, 'fa-refresh', 'Y');  
              
INSERT INTO blog_dynamic_menu(bdm_menu_id, bdm_menu_desc, bdm_page_no, bdm_par_menu_id, bdm_seq_no, bdm_icon, bdm_visible)
               VALUES ('SUB_MENU2_2', 'Sub Menu 2.2', 7, 'MENU_2', 2, 'fa-spinner', 'N');

Step 3

Write SQL Query using connect by.

  SELECT LEVEL,
         bdm_menu_desc label,
         DECODE(bdm_page_no, NULL, NULL, ('f?p=&APP_ID.:'||bdm_page_no||':&SESSION.::&DEBUG.::::')) target,
         NULL is_current,
         bdm_icon image,
         NULL
    FROM blog_dynamic_menu
   WHERE bdm_visible = 'Y'
   START WITH bdm_par_menu_id IS NULL
 CONNECT BY bdm_par_menu_id = PRIOR bdm_menu_id
 ORDER SIBLINGS BY bdm_seq_no ASC

Step 4

Click below link to view Video tutorial to change Static Navigation to Dynamic Navigation Menu.

https://youtu.be/xHnNIYTmMDY

Thanks & Regards

R. Kalaiyarasan

+91 9944809554

Oracle SQL - Extract Year's Month's Day's from two dates.

 In this Post I explain to find Years, Months and days from Two dates.

1. Extract Total years

  SELECT TRUNC(MONTHS_BETWEEN(SYSDATE, '01-JAN-2018')/12) total_years
    FROM DUAL;

  OUTPUT

  ------

  2

  

2. Extract Total months

  SELECT (TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, '01-JAN-2018'), 12))) total_months
    FROM DUAL;

  

  OUTPUT

  ------

  10

3. Extract Total days

  SELECT (TRUNC(SYSDATE) - ADD_MONTHS('01-JAN-2018', 

                           TRUNC(MONTHS_BETWEEN(SYSDATE, '01-JAN-2018')))) total_days
    FROM DUAL;

  OUTPUT

  ------

  2

4. Concat Year Month Days

  SELECT TRUNC(MONTHS_BETWEEN(SYSDATE, '01-JAN-2018')/12) ||' Years '||                       (TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, '01-JAN-2018'), 12)))||' Months '||               (TRUNC(SYSDATE) - ADD_MONTHS('01-JAN-2018',                                           TRUNC(MONTHS_BETWEEN(SYSDATE, '01-JAN-2018'))))||' Days' yr_mm_dd

    FROM DUAL;

  OUTPUT

  ------

  2 Years 10 Months 2 Days

Thanks & Regards

R. Kalaiyarasan

+91 9944809554