Oracle APEX - How to show Images in Oracle APEX Interactive Report

 In this post, I can explain how to show images in Oracle APEX interactive Report.

Table Script

CREATE TABLE BLOB_IMAGES (BI_IMG_DESC VARCHAR2(100),   BI_IMG BLOB,                                BI_MIME_TYPE VARCHAR2(250),   BI_FILE_NAME VARCHAR2(250));

Report Query

SELECT ROWID,
       bi_img_desc,
       DECODE(NVL(DBMS_LOB.GETLENGTH(bi_img), 0), 0, NULL,
       '<img alt="'||APEX_ESCAPE.HTML_ATTRIBUTE(bi_img_desc)||
       '" src = "'||APEX_UTIL.GET_BLOB_FILE_SRC('P11_BI_IMG', ROWID)||

       '" height = "50" width = "50" />') bi_img,
       bi_mime_type,
       bi_file_name
  FROM blob_images

* P11_BI_IMG - Form Page column name

APEX Workings

Step 1:

Create a Page with the type of Report with form

Step 2:

Change Report query which is mentioned above

Step 3:

Run the report and add image and view

https://youtu.be/4HpNXB-Sf5Y

Thanks & Regards

R. Kalaiyarasan

+91 9944809554

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

Oracle SQL - Print nth maximum salary using DENSE_RANK function

In this post, I explained you how to get nth Maximum/Max salary.

Example Table:

--------------------

CREATE TABLE BLOG_EMP_SALARY_DTLS

(BESD_EMP_ID         VARCHAR2(15),

 BESD_EMP_NAME       VARCHAR2(150),

 BESD_GENDER         VARCHAR2(1)         DEFAULT 'M' NOT NULL,

 BESD_DOJ            DATE,

 BESD_DOB            DATE,

 BESD_DEPT           VARCHAR2(100),

 BESD_SALARY         NUMBER(12, 2)       DEFAULT 0 NOT NULL);

INSERT SCRIPT

-------------

INSERT INTO BLOG_EMP_SALARY_DTLS VALUES ('100001', 'JOSEPH', 'M', '01-JAN-2010', '25-JUL-1988', 'ADMIN', 15000) 

INSERT INTO BLOG_EMP_SALARY_DTLS VALUES ('100002', 'KARIM', 'M', '15-MAR-2012', '01-AUG-1987', 'HRM', 18500)

INSERT INTO BLOG_EMP_SALARY_DTLS VALUES ('100003', 'SURESH', 'M', '16-MAR-2012', '01-MAR-1990', 'PRODUCTION', 11250) 

INSERT INTO BLOG_EMP_SALARY_DTLS VALUES ('100004', 'RANJITH', 'M', '1-APR-2012', '01-AUG-1990', 'PRODUCTION', 11250)

INSERT INTO BLOG_EMP_SALARY_DTLS VALUES ('100005', 'SATHISH', 'M', '05-SEP-2015', '01-MAR-1995', 'ADMIN', 75000)

INSERT INTO BLOG_EMP_SALARY_DTLS VALUES ('100006', 'GANESH', 'M', '05-AUG-2015', '01-SEP-1992', 'ADMIN', 16000)

SAMPLE OUTPUT

-------------

SELECT * FROM BLOG_EMP_SALARY_DTLS

Nth Salary Query

----------------

SELECT *

  FROM (SELECT besd_emp_id,

               besd_emp_name,

               besd_salary,

               DENSE_RANK () OVER (ORDER BY besd_salary DESC) bsed_sal_rank

          FROM blog_emp_salary_dtls)

 WHERE bsed_sal_rank = 1;

OUTPUT: 1 (First Salary)

------------------------

OUTPUT: 2 (Second  Salary)

------------------------

OUTPUT: 3 (Third Salary)

------------------------

OUTPUT: 4 (Fourth Salary)

-------------------------

Both Suresh and Ranjith have same salary, query return 4th salary both of them.

Thanks & Regards

R. Kalaiyarasan

+91 9944809554