About Oracle and Oracle ā'pěks (#orclapex)

Fig 1:  Logo Cards Here I am giving an elegant way for displaying logos (images) as cards using  Oracle APEX  classical report. This has been inspired by apex.oracle.com .  Cards:  A card is any type of custom element used for easy showing of contents that comprises various types of objects like title, image, button, etc. These cards could be anything like CSS material based cards, blog-based cards, business cards, informative cards or analytical cards and etc. Step 1: Create list of below required objects by using below script.              1. Table - Store the data              2. Sequence - Generate the primary key value              3. Trigger - Fire the action, when DML operations happening on the table Table: (fxgn_customer_logos) CREATE   TABLE  fxgn_customer_logos    (     logo_id        NUMBER ,     customer_name  VARCHAR2 ( 240   BYTE ),      description     VARCHAR2 ( 4000   BYTE ),     image_blob  BLOB ,     mime_type   VARCHAR2 ( 240   BYTE ),     sort_order  NUMBER ,     

Fig 1: Multiple Columns Popup LOV Here I am giving an elegant way for displaying multiple columns in  Oracle APEX  Popup LOV. This has been inspired by Oracle E-Business Suite.  List of Values:  A List of Values is a static or dynamic definition used to display a specific type of page item, such as popup lists of values, a select list, a check box, a radio group, or multiple select lists. Step 1: Keep your LOV query ready. SELECT  emp . ename employee ,        emp . empno ,        dept . dname department ,        emp . job ,        mgr . ename  manager ,         to_char ( emp . hiredate , 'DD-Mon-RRRR' )  hire_date ,         to_char (( nvl ( emp . sal , 0 )  +  nvl ( emp . comm , 0 )),   '999G999G999G999G990D00' )                                            AS  total_salary    FROM  eba_demo_chart_emp emp ,        eba_demo_chart_emp mgr ,        eba_demo_chart_dept dept   WHERE   1           =  1     AND  emp . mgr    = mgr . empno     AND  emp . deptno = dept . deptno

When a file gets uploaded, the icon of the file type is displayed without opening the file. This has been inspired by Microsoft - Outlook. Here I am giving an elegant way for displaying the file type icon in Oracle APEX reports. Step 1:  Create a table by using below script, CREATE   TABLE  ebs_project_files    (       id              NUMBER ,      project_id     NUMBER ,      filename       VARCHAR2 ( 4000 ),      file_mimetype  VARCHAR2 ( 512 ),      file_charset   VARCHAR2 ( 512 ),      file_blob      BLOB ,      file_comments  VARCHAR2 ( 4000 ),      created_on     TIMESTAMP   ( 6 )   WITH   local   TIME   zone ,      created_by     VARCHAR2 ( 255 ),      updated_on     TIMESTAMP   ( 6 )   WITH   local   TIME   zone ,      updated_by     VARCHAR2 ( 255 ),       PRIMARY   KEY   ( id )   USING   INDEX   ENABLE    ); Step 2:  Populate some records on ebs_project_files . Step 3:  Create a new blank page. Step 4:  Create a new Classical Report region to the page. (Refer below script)

I had a question on my mind that, which is the most efficient way to print totals and subtotals in a single row.  I was thinking PIVOT would be the significant way compare to case statement. Let's see which is more efficient way to do that? Here I am giving an example to write a SQL query that performs a aggregate functions (count, sum, avg and etc) of a column while including case statement. Step 1:  Create DEPT table which will be the parent table of the EMP table. DROP TABLE dept; ------------------------------------------------------------ CREATE TABLE dept  (    deptno NUMBER(2,0),    dname  VARCHAR2(14 BYTE),    loc    VARCHAR2(13 BYTE),    CONSTRAINT pk_dept PRIMARY KEY (deptno) ENABLE   ); Step 2:   Insert row into DEPT table using named columns. INSERT INTO dept (deptno,dname,loc) VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO dept (deptno,dname,loc) VALUES (20,'RESEARCH','DALLAS'); INSERT INTO dept (deptno,dname,loc) VALUES (30,'SALES