Log APEX Interactive Report Search Filter

A few days ago someone posted a question on the forums http://forums.oracle.com/forums/message.jspa?messageID=3365707 asking how to log the search filter in an IR. Here's how to do it. A working example is available here.


Please note you will need jQuery for this example


Step 1: Create Log Table

1 2 3 4 5 6

CREATE TABLE tir_search_filter_log(   search_filter VARCHAR2(255) NULL,   search_date DATE DEFAULT SYSDATE NOT NULL,   username VARCHAR2(255) NOT NULL);

Step 2: Create Application Process


Create an On Demand Application Process. Call it: AP_LOG_SEARCH_FILTER

1 2 3 4 5 6 7 8 9 10 11 12 13

BEGIN   INSERT INTO tir_search_filter_log               (search_filter,                search_date,                username               )   VALUES      (apex_application.g_x01,                SYSDATE,                :app_user               ); END;

Step 3: Create Interactive Report


Create a IR Page

1 2 3 4

SELECT * FROM   emp

Step 3: Javascript


Create a HTML region and add the following javascript code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

// Function to call our Application Process to log the search function fLogSearch(){   var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=AP_LOG_SEARCH_FILTER',$v('pFlowStepId'));      get.addParam('x01',$v('apexir_SEARCH')); // IR Search Filter Value   gReturn = get.get();  // Call AP Log Search Filter to insert log   // Call APEX IR Search   gReport.search('SEARCH'); }   // Run the following once the document is ready $(document).ready(function(){    // -- Handle Go Button --   // Unbind all events. Important for order of execution   $('input[type="button"][value="Go"]').attr('onclick',''); //unbind click event   // Rebind events   $('input[type="button"][value="Go"]').click(function(){fLogSearch()});       // -- Handle "Enter" in input field --   $('#apexir_SEARCH').attr('onkeyup',''); //unbind onkeyup event   // Rebind Events   $('#apexir_SEARCH').keyup(function(event){($f_Enter(event))?fLogSearch():null;}); });