Skip to Main Content

APEX Comments


This plug-in helps to build a comment region to application express developers.
  • This demo uses a modified script.js file due to table manipulation.
  • In order to get the proper javascript file please visit the Github site of APEX Comments.
  • When the application is being installed the content of the blob colum (PP_URL) in table APEX_COMMENTS_USERS will be lost.

New

Source Table Structure


A basic structure for a table where the comments will be stored.

  CREATE TABLE "APEX_COMMENTS" 
   (	"ID_COLUMN" VARCHAR2(2000), 
	"PARENT_ID" VARCHAR2(2000), 
	"CONTENT_STR" CLOB, 
	"CREATED" DATE, 
	"MODIFIED" DATE, 
	"CREATED_BY" VARCHAR2(2000), 
	"PROF_PIC_URL" VARCHAR2(2000), 
	"FILTER_COL" NUMBER, 
	"USER_ID" NUMBER
   ) ;

Application Process Code


When an image is retrieved from a BLOB column then it is advised to define/use an Application Process which can display the profile picture(s) for/of the user.


Be careful how this is used. If you don't implement some form of browser caching, then a report which displays 500 images inline on a page will result in 500 requests to the APEX engine and database, per user per page view! Ouch! And then it's a matter of time before a DBA starts hunting for the person slamming their database and reports that "APEX is killing our database". There is an excellent explanation of cache headers here.

begin
    for c1 in (select *
                 from apex_comments_users
                where empno = :FILE_ID) loop
        --
        sys.htp.init;
        sys.owa_util.mime_header( c1.profile_picture_mimetype, FALSE );
        sys.htp.p('Content-length: ' || sys.dbms_lob.getlength( c1.profile_picture));
        sys.htp.p('Content-Disposition: attachment; filename="' || c1.profile_picture_filename || '"' );
        sys.htp.p('Cache-Control: max-age=3600');  -- tell the browser to cache for one hour, adjust as necessary
        sys.owa_util.http_header_close;
        sys.wpg_docload.download_file( c1.profile_picture );
     
        apex_application.stop_apex_engine;
    end loop;
end;

Plug-in Query


This query example will return the comments from the table where it's stored.

select   ID_COLUMN      as comment_id
       , PARENT_ID      as reply_id
       , CONTENT_STR    as comment_text
       , CREATED        as created_date
       , MODIFIED       as modified_date
       , CREATED_BY     as username
       , case when prof_pic_url is null
                then null
              else 
                'f?p=&APP_ID.:&APP_PAGE_ID.:&APP_SESSION.:APPLICATION_PROCESS=GETIMAGE:::FILE_ID:' || PROF_PIC_URL
         end as prof_pic_url_display
       , case when CREATED < sysdate - 2
              then 1
              else 0
         end as new_comment
       , PROF_PIC_URL as prof_pic_url_save
from apex_comments

Pinging List Query


Users can be listed and tagged in the textarea section by defining a query in the Pinging List attribute.

select   empno                                  as ID 
       , ename                                  as USERNAME
       , ename || ' ' || job                    as NAME 
       , ename || '.' || job || '@company.com'  as EMAIL
       , 'f?p=&APP_ID.:&APP_PAGE_ID.:&APP_SESSION.:APPLICATION_PROCESS=GETIMAGE:::FILE_ID:' || empno as PROFILE_PICTURE_URL
from apex_comments_users;

JavaScript Initialization Code


A basic customisation for the Comments Region. More option can be found at Viima Jquery Comments Configuration.

function(config){
    
    config.profilePictureURL = 'f?p=&APP_ID.:&APP_PAGE_ID.:&APP_SESSION.:APPLICATION_PROCESS=GETIMAGE:::FILE_ID:' + apex.item('P3_PPURL_ID').getValue();
    config.enableHashtags = true;
    config.roundProfilePictures = true;
    config.timeFormatter = function(time) {
    return moment(time).format('MMMM Do YYYY, h:mm:ss a');
    };

    return config;
}