APEX增加可交互表格下载日志

最近在使用oracel的apex，在开发过程中遇到了一些问题，国内的话很难找到相关资料，特此记录下。

关于apex中interactive grid表格增加下载日志的处理：

在apex常用的interactive grid中，如何记录下载操作，出于安全性考虑采用PL/SQL来完成，避免js带来的安全隐患，需要自定义过程并初始化执行，如下：

step 1: 首先创建日志表存储下载记录，例如：         

CREATE TABLE sys_download_log 
(
    app_user VARCHAR2(255), 
    app_id NUMBER, 
    app_page_id NUMBER, 
    request VARCHAR2(255),
    downloaded_on DATE, 
    report_id NUMBER, 
    report_name VARCHAR2(255), 
    report_type VARCHAR2(255),
    report_format VARCHAR2(255)
)

step 2: 创建存储过程，过滤下载请求，具体代码如下：

create or replace PROCEDURE dl_audit
 (
     p_request     IN VARCHAR2 DEFAULT v('REQUEST'),
     p_app_user    IN VARCHAR2 DEFAULT v('APP_USER'),
     p_app_page_id IN NUMBER   DEFAULT v('APP_PAGE_ID'),
     p_app_id      IN NUMBER   DEFAULT v('APP_ID'),
     p_app_session IN NUMBER   DEFAULT v('APP_SESSION')
 )
AS
     l_count NUMBER;
     l_id             NUMBER;
     l_report_name    VARCHAR2(255);
     l_report_format  VARCHAR2(255);
     l_json           VARCHAR2(10000);
BEGIN

    CASE WHEN p_request LIKE 'FLOW_EXCEL_OUTPUT%' THEN
    
        SELECT SUBSTR(p_request, 20, INSTR(p_request,'_',20)-20) INTO l_id FROM dual;
        SELECT region_name INTO l_report_name FROM apex_application_page_regions WHERE region_id = l_id;
    
    -- 记录下载内容
        INSERT INTO sys_download_log (app_user, app_id, app_page_id, request, downloaded_on, report_id, report_name, report_type, report_format)
        VALUES (p_app_user, p_app_id, p_app_page_id, p_request, SYSDATE, l_id, l_report_name, 'CLASSIC', 'CSV');

    WHEN p_request LIKE '%CSV' OR p_request LIKE '%HTMLD' OR p_request LIKE '%PDF' THEN
    
        SELECT COUNT(*) INTO l_count FROM apex_application_page_ir where page_id = p_app_page_id AND application_id = p_app_id;
    
        IF l_count = 1 THEN
            SELECT interactive_report_id, region_name INTO l_id, l_report_name
            FROM apex_application_page_ir where page_id = p_app_page_id AND application_id = p_app_id; ELSE
           -- 从请求中获取想要的字段
           SELECT SUBSTR(p_request,5, INSTR(p_request,']')-5) INTO l_id FROM dual;
           SELECT region_name INTO l_report_name FROM apex_application_page_ir where region_id = TRIM(l_id);
        END IF;
    
    -- 记录下载内容
        INSERT INTO sys_download_log (app_user, app_id, app_page_id, request, downloaded_on, report_id, report_name, report_type, report_format)
           VALUES (p_app_user, p_app_id, p_app_page_id, p_request, SYSDATE, l_id, l_report_name, 'IR',
           CASE WHEN p_request LIKE '%CSV' THEN 'CSV' WHEN p_request LIKE '%HTMLD' THEN 'HTML' WHEN p_request LIKE '%PDF' THEN 'PDF' ELSE 'OTHER' END);   
    -------------------------------------------------------------------------------------------------------------------------------
    -- interactive grid表格
    --------------------------------------------------------------------------------------------------------------------------------
        WHEN LOWER(owa_util.get_cgi_env('QUERY_STRING')) LIKE 'p_flow_id=' || p_app_id || '&p_flow_step_id=' || p_app_page_id || '&p_instance=' || p_app_session || '%&p_json%download%' THEN
        
             SELECT utl_url.unescape(substr(owa_util.get_cgi_env('QUERY_STRING'),
               INSTR(owa_util.get_cgi_env('QUERY_STRING'), 'p_json=') + 7)) INTO l_json FROM dual;
             apex_json.parse(l_json);
            -- 获取列表id
             l_id := apex_json.get_varchar2(p_path => 'regions[%d].id', p0 => 1);
             l_report_format := apex_json.get_varchar2(p_path => 'regions[%d].download.format', p0 => 1);
             -- 查询列表名称
             SELECT region_name INTO l_report_name FROM apex_application_page_regions where region_id = l_id;
            -- 记录下载内容
             INSERT INTO sys_download_log (app_user, app_id, app_page_id, request, downloaded_on, report_id, report_name, report_type, report_format)
               VALUES (p_app_user, p_app_id, p_app_page_id, p_request, SYSDATE, l_id, l_report_name, 'GRID', l_report_format);
    
        ELSE NULL;
   
    END CASE;

END dl_audit;

step 3: 设置app初始化执行该存储过程

至此，下载表格的时候就可以记录日志了，不局限与interactive grid,report类型也支持，基本包括所有表格类型。