File Type Icons in Oracle APEX Report

In this post I am going to show you how we can display the icon of file types also in an Oracle APEX report. The files are stored in database and in the report we are showing the saved files along with their icons for quick understanding of file type.

Step 1: I created a table with blob column to store the files

CREATE TABLE  "EBA_INTRACK_FILES" 
   (	"ID" NUMBER, 
	"ROW_VERSION_NUMBER" NUMBER, 
	"INCIDENT_ID" NUMBER, 
	"FILENAME" VARCHAR2(4000), 
	"FILE_MIMETYPE" VARCHAR2(512), 
	"FILE_CHARSET" VARCHAR2(512), 
	"FILE_BLOB" BLOB, 
	"FILE_COMMENTS" VARCHAR2(4000), 
	"TAGS" VARCHAR2(4000), 
	"CREATED" TIMESTAMP (6) WITH LOCAL TIME ZONE, 
	"CREATED_BY" VARCHAR2(255), 
	"UPDATED" TIMESTAMP (6) WITH LOCAL TIME ZONE, 
	"UPDATED_BY" VARCHAR2(255), 
	 PRIMARY KEY ("ID")
  USING INDEX  ENABLE, 
	 CONSTRAINT "EBA_INTRACK_FILES_UQ_ID_FN" UNIQUE ("INCIDENT_ID", "FILENAME")
  USING INDEX  ENABLE
   )

Step 2: Saved different types of files in this table from front end.

Step 3: Created a classic report based on table "EBA_INTRACK_FILES"

select
    id,
    '<a href="'||APEX_UTIL.GET_BLOB_FILE_SRC('P54_FILE_BLOB',id)||'">'||apex_escape.html(FILENAME)||'</a>' FILE_NAME,
    FILE_MIMETYPE,
    FILE_CHARSET,
    apex_util.filesize_mask(dbms_lob.getlength(FILE_BLOB)) f_len,
    substr(FILE_COMMENTS,1,50)||decode(greatest(length(FILE_COMMENTS),50),50,'','...') FILE_COMMENTS,
     '<span class="t-Icon file-icon fa '
       || Decode(Substr(Upper(f.filename), -4), '.PPT',
          'fa-file-powerpoint-o',
                                   '.XLS', 'fa-file-excel-o',
                                   '.DOC', 'fa-file-word-o',
                                   '.PDF', 'fa-file-pdf-o',
                                   '.GIF', 'fa-file-image-o',
                                   '.PNG', 'fa-file-image-o',
                                   '.JPG', 'fa-file-image-o',
          Decode(Substr(Upper(f.filename), -5), '.PPTX',
          'fa-file-powerpoint-o',
                                   '.XLSX', 'fa-file-excel-o',
                                   '.DOCX', 'fa-file-word-o',
                                   '.TIFF', 'fa-file-image-o',
                                   'fa-file-o'))
       || '"></span>'   AS file_type,
    created,
    lower(created_by) created_by
from EBA_INTRACK_FILES f
where INCIDENT_ID = :P50_ID 
order by created desc

Step 4: Go To "File_Type" report column and set its "Type" property to "Percent Graph"

Step 5: Change the style of "File_Type" by adding the CSS to the page properties (Path : Page --> CSS --> Inline)

.file-icon.fa-file-powerpoint-o,
.file-icon.fa-file-excel-o,
.file-icon.fa-file-word-o,
.file-icon.fa-file-pdf-o,
.file-icon.fa-file-image-o,
.file-icon.fa-file-o {
    padding: 4px 8px;
    text-align: center;
    color: #FFF;
    border-radius: 2px;
}

.file-icon.fa-file-powerpoint-o {
    color: #D24726;
}

.file-icon.fa-file-excel-o {
    color: #217345;
}

.file-icon.fa-file-word-o {
    color: #2A579A;
}

.file-icon.fa-file-pdf-o {
    color: #F40700;
}

Step 6 : Run the application

Thanks for reading !!