APEX Logs: Storing Log Data

For those of you that use the APEX Logs you may not be aware that they store at best 4 weeks of data and at worst 2 weeks of data. They’re actually 2 log tables, each one gets purged roughly every 2 weeks. For those of you who don’t know about or use the APEX logs I suggest you read up on this.

You can get a list of the APEX logs by running the following query:

SELECT *
  FROM apex_dictionary
 WHERE column_id = 0
   AND apex_view_name LIKE '%LOG%'

I strongly recommend that you explicitly store the log data into your own tables. I’ve encountered several instances where the APEX Logs have helped get me out of some sticky situations. It can also help you get some usage stats and page stats.

Here’s how to keep a copy of the APEX log tables:

Note: You’ll need to run this in each of your schemas that you have APEX applications in since the APEX Log Views only display application information who’s parsing schema matches the current Oracle user

1- Create the APEX log tables

-- Login information
CREATE TABLE tapex_workspace_access_log
AS SELECT * FROM apex_workspace_access_log;

-- Page access information
CREATE TABLE tapex_workspace_activity_log
AS SELECT * FROM apex_workspace_activity_log;

2- Update the APEX log tables

Note: You may want to store this in a procedure and run as a nightly scheduled job so you don’t forget to update the tables

INSERT INTO tapex_workspace_access_log
            (workspace, application_id, application_name, user_name, authentication_method, application_schema_owner,
             access_date, ip_address, authentication_result, custom_status_text, workspace_id)
  SELECT alog.workspace,
         alog.application_id,
         alog.application_name,
         alog.user_name,
         alog.authentication_method,
         alog.application_schema_owner,
         alog.access_date,
         alog.ip_address,
         alog.authentication_result,
         alog.custom_status_text,
         alog.workspace_id
    FROM apex_workspace_access_log alog,
         tapex_workspace_access_log x
   WHERE alog.access_date = x.access_date(+)
     AND x.ROWID IS NULL
     AND alog.application_schema_owner = USER;

INSERT INTO tapex_workspace_activity_log
            (workspace, apex_user, application_id, application_name, application_schema_owner, page_id, page_name,
             view_date, think_time, log_context, elapsed_time, rows_queried, ip_address, AGENT, apex_session_id,
             error_message, error_on_component_type, error_on_component_name, page_view_mode, regions_from_cache,
             workspace_id)
  SELECT alog.workspace,
         alog.apex_user,
         alog.application_id,
         alog.application_name,
         alog.application_schema_owner,
         alog.page_id,
         alog.page_name,
         alog.view_date,
         alog.think_time,
         alog.log_context,
         alog.elapsed_time,
         alog.rows_queried,
         alog.ip_address,
         alog.AGENT,
         alog.apex_session_id,
         alog.error_message,
         alog.error_on_component_type,
         alog.error_on_component_name,
         alog.page_view_mode,
         alog.regions_from_cache,
         alog.workspace_id
    FROM apex_workspace_activity_log alog,
         tapex_workspace_activity_log x
   WHERE alog.view_date = x.view_date(+)
     AND alog.apex_session_id = x.apex_session_id(+)
     AND alog.application_schema_owner = USER
     AND x.ROWID IS NULL;

Presenting at COUG (Calgary Oracle User Group) this week

Frank Hoogendoorn and I will be giving a presentation on APEX at the Calgary Oracle User Group (COUG) on this Thursday, May 28th, at 8:00 am. For those of you that are thinking about implementing APEX within your organization this will be an excellent presentation as we will go over both the business and technical reasons why we are using APEX. We’ll also be demonstrating some of our newest APEX applications

Thursday, May 28th
Registration: 8:00 am
Presentation: 8:30 am

PetroCanada Building - West Tower
150 6th Avenue S.W.
17th Floor, Meeting room B/C

For more information, please go to: http://www.coug.ab.ca/events/20090528.htm

How to determine if user can view an APEX region.

If you just want to see how to determine if user is allowed to view a region scroll to bottom of this post, however I suggest you read the explanation as to why/when you may want to use this function

I recently received a request to build an internal application to generate many reports for one of the teams in my organization. At a high level, the application is supposed to have (it’s still not built) several “Report Result” pages. Each Report Result page will contain 10 to 15 reports. Their will be a menu page in which end users can select the Report Result page they’d like to run, and the individual reports they want to run on that page:

Query for Checkboxes:

SELECT region_name d,
       region_id r
  FROM apex_application_page_regions
 WHERE application_id = 103
   AND page_id = 2
ORDER BY UPPER (d) ASC   

When users click “Go”, they will go to the selected “Report Result” page and execute the selected reports.

So far this is fairly straight forward (using the APEX Dictionary to help out). To make things more difficult, certain reports should only be accessible depending on the parsing schema. For example if the parsing schema is “SCOTT” then Report/Region 2 should not be displayed.

To meet this requirement I can add a condition (SQL Exists) to the Report 2 region on Page 2:

SELECT 1
  FROM DUAL
 WHERE :p2_region_static_id_list IS NULL
    OR (    :f_parsing_schema != 'SCOTT' -- F_PARSING_SCHEMA is an application_item I added
        AND INSTR (':' || 'REPORT2' || ':', :p2_region_static_id_list) > 0)
    -- "REPORT2" is the Region's Static ID

When I run Page 2, Reports 1, 3, and 4 are executed which is correct. However, on the menu page the user had the option to select Report 2 when they should have never been allowed to see it in the check list since the parsing schema was SCOTT.

To resolve this issue, I’ve created the following function to determine if a user can view a specified region:

/**
 * Determine if current user has permissions to view region
 * @param p_region_id region to test
 * @return Y or N
 * @author Martin Giffy DSouza - http://apex-smb.blogspot.com/
 */

CREATE OR REPLACE FUNCTION f_apex_permission_flag (
  p_region_id IN apex_application_page_regions.region_id%TYPE
)
  RETURN VARCHAR2
AS
  TYPE v_apex_region_rec_type IS RECORD (
    page_authorization_scheme apex_application_page_regions.authorization_scheme%TYPE,
    page_build_option_status apex_application_build_options.build_option_status%TYPE,
    reg_authorization_scheme apex_application_page_regions.authorization_scheme%TYPE,
    reg_build_option_status apex_application_build_options.build_option_status%TYPE,
    reg_condition_expression1 apex_application_page_regions.condition_expression1%TYPE,
    reg_condition_expression2 apex_application_page_regions.condition_expression2%TYPE,
    reg_condition_type apex_standard_conditions.d%TYPE
  );

  v_apex_region v_apex_region_rec_type;
BEGIN
  -- If region is null return N to access
  IF p_region_id IS NULL THEN
    RETURN 'N';
  END IF;

  SELECT aap.authorization_scheme page_authorization_scheme,
         aap_bo.build_option_status page_build_option_status,
         aapr.authorization_scheme reg_authorization_scheme,
         aapr_bo.build_option_status reg_build_option_status,
         aapr.condition_expression1 reg_condition_expression1,
         aapr.condition_expression2 reg_condition_expression2,
         asc_reg.d reg_condition_type
    INTO v_apex_region
    FROM apex_application_pages aap,
         apex_application_build_options aap_bo,
         apex_application_build_options aapr_bo,
         apex_application_page_regions aapr,
         apex_standard_conditions asc_reg
   WHERE aapr.region_id = p_region_id
     AND aapr.page_id = aap.page_id
     AND aapr.application_id = aap.application_id
     AND UPPER (aap.build_option) = UPPER (aap_bo.build_option_name(+))
     AND aapr.build_option_id = aapr_bo.build_option_id(+)
     AND aapr.condition_type = asc_reg.r(+);

  -- PAGE VALIDATIONS
  -- Check Page Build Option
  IF UPPER (NVL (v_apex_region.page_build_option_status, 'INCLUDE')) != 'INCLUDE' THEN
    RETURN 'N';
  END IF;

  -- Check Page Authorization
  IF v_apex_region.page_authorization_scheme IS NOT NULL THEN
    IF apex_util.public_check_authorization (p_security_scheme => v_apex_region.page_authorization_scheme) = FALSE THEN
      RETURN 'N';
    END IF;
  END IF;

  -- REGION VALIDATIONS

  -- Check Region Build Option
  IF UPPER (NVL (v_apex_region.reg_build_option_status, 'INCLUDE')) != 'INCLUDE' THEN
    RETURN 'N';
  END IF;

  -- Check Region Authorization
  IF v_apex_region.reg_authorization_scheme IS NOT NULL THEN
    IF apex_util.public_check_authorization (p_security_scheme => v_apex_region.reg_authorization_scheme) = FALSE THEN
      RETURN 'N';
    END IF;
  END IF;

  -- Check the region condition
  IF v_apex_region.reg_condition_type IS NOT NULL THEN
    IF wwv_flow_conditions.standard_condition (p_condition_type     => v_apex_region.reg_condition_type,
                                               p_condition          => v_apex_region.reg_condition_expression1,
                                               p_condition2         => v_apex_region.reg_condition_expression1
                                              ) = FALSE THEN
      RETURN 'N';
    END IF;
  END IF;

  -- All test passed
  RETURN 'Y';
END f_apex_permission_flag;

Using the new function, the Checkbox Query (on Page 1) now becomes:

SELECT   region_name d,
         region_id r
    FROM (SELECT region_id,
                 region_name,
                 f_apex_permission_flag (region_id) permission_flag
            FROM apex_application_page_regions
           WHERE application_id = 103
             AND page_id = 2) x
   WHERE x.permission_flag = 'Y'
ORDER BY UPPER (d) ASC

A word of precaution: If you’re using this function to verify region access on another page you should be aware of how the condition is defined. If it is referencing items specific to its own page then you may get some false positives as those items may not have been defined yet.

APEX: Page Access Protection and Session State Protection

APEX’s Page Access Protection (PAP - For Pages) and Session State Protection (SSP - For Items) are excellent security tools to help prevent users from altering session values. What some people may not be aware of is that if you enable PAP for page it does not prevent users from altering the session state of items on that page. All it does is require that any items passed through that page via the URL require a checksum. Malicious users can still alter the item’s session state using AJAX or from other pages. Long story short, if you want to lock your application down you need to enable SSP for all required items.

APEX has a great tool to do this quickly for you rather than having to go into each page item. Shared Components / Session State Protection / Page / (click page number). You can now set the PAP and the SSP for all the page items.

If you do use PAP and SSP the following queries will help you do some quick validations to ensure all your security checks are in place

Pages without Page Access Protection

SELECT aap.application_id,
       aap.application_name,
       aap.page_id,
       aap.page_name
  FROM apex_application_pages aap
 WHERE LOWER (aap.page_access_protection) = 'unrestricted'
   AND aap.application_id = :app_id

Page items without Session State Protection

SELECT aapi.application_id,
       aapi.application_name,
       aapi.page_id,
       aapi.page_name,
       aapi.item_name
  FROM apex_application_page_items aapi
 WHERE aapi.application_id = :app_id
   AND LOWER (aapi.item_protection_level) = 'unrestricted'

Pages which have Page Access Protection, but have page items with no Session State Protection

This query helps identify pages which you think are locked down, but end users could set the session state of item values

SELECT aapi.application_id,
       aapi.application_name,
       aapi.page_id,
       aapi.page_name,
       aapi.item_name
  FROM apex_application_pages aap,
       apex_application_page_items aapi
 WHERE LOWER (aap.page_access_protection) != 'unrestricted'
   AND aap.application_id = :app_id
   AND aapi.application_id = aap.application_id
   AND aap.page_id = aapi.page_id
   AND LOWER (aapi.item_protection_level) = 'unrestricted'

Enhancing APEX Security - Explanation

I recently posted an entry called Enhancing APEX Security. I may have jumped the gun and not given a full explanation as to what problem this was trying to solving and the logic behind this solution. Here’s the full explanation to the problem and a high level overview of the proposed solution.

In APEX, Session State Protection (SSP) can help secure your applications by preventing users to set session state value of items. Developers should be aware that it has 2 issues regarding it. First, it prevents items from being set using AJAX. If I try to set using AJAX I get the following error (using Firebug):

Second, when a user is on a page, they can alter the value before it is submitted to try to access data they aren’t allowed to. For example, let’s say users are only allowed to see information about people within their own department. So my LOV is something like:

SELECT e.ename d,
       e.empno r
  FROM emp e
WHERE job = 'CLERK'

Using Firebug I can see that this is what my option list looks like

Now what if I were to modify the select list and change the value for “SMITH” from 7369 to 7839 (This is “KING” / President) then submit the page? It still works and sets the value in session state:

Notice how the value in session is set to 7839, which we really shouldn’t have access to? If you’re not careful in your application this can lead to serious security holes.

Now on to the proposed solution. When you have a Select List, use a hashed value for the IDs so that users can’t alter the select list values (well they can but they’ll require the hash value as well). Instead of P1_EMPNO being a select list, set it as a Hidden and Protected item and enable SSP for it. This allows your application to still reference P1_EMPNO as it normally would. Create a new item, P1_EMPNO_SEC, which will be a select list with the altered LOV code (i.e. the return value has a hash associated to it). So if the value is 7369, it will actually be [email protected] (i.e. VALUE[DELIMITER][HASH]). The code (in my example) handles the rest so that when a user submits a page, it will take the submitted value from the secure item and then set the “regular” item with the unhashed value. If someone were to try to alter the select list, they’d be unsuccessful since the unhashing method returns NULL when an invalid hash value is present.

The reason why I create 2 items is I don’t want to have to alter any existing code. So all code referencing P1_EMPNO will remain the same. If users need to pass P1_EMPNO in via the URL they can (provided they pass in the checksum since it has SSP enabled).

Enhancing APEX Security

Note: Full explanation available here. Please read before continuing with this post.

The best way to secure data in your APEX application, or any application, is to secure your data in the database. You can do this using Oracle’s Virtual Private Database (VPD), also known as Fine Grained Access Control (FGAC). APEX also has Session State Protection (SSP) which helps prevent front-end modification of data by preventing URL tampering.

I’m a big advocate of using both VPD and SSP. In some situations VPD may not be implemented for various reasons so developers must rely on SSP. SSP is great and allows developers to quickly and easily help prevent malicious users from trying to access data that they aren’t supposed to. It’s important to note that SSP only prevents URL tampering. If users wanted to alter data on a form they could easily do that with Firebug (I won’t go into that in this post). SSP also prevents users from setting values using AJAX. This can be circumvented using the “x01” .. “x10” parameters (please see Carl’s post for more info) but still leaves the door open for users to alter items in session state that they shouldn’t.

How can you use SSP, make AJAX calls, and ensure that users aren’t altering data on a form? This question has been bugging me for a while and I think I’ve come up with a solution that should work with minimal changes to existing code. For this example I’m going to use a select list containing a list of IDs (this is where the issue has come up the most for me). I’ll use the emp table, with empno as the id, for this example.

Some notation before we begin: “Secure” items (i.e. ones that have a hashed value appended to them) will be called PX_ITEM_NAME_SEC while it’s corresponding unsecured item is called PX_ITEM_NAME. The code only looks at “secure” items that have a matching “unsecured” item. “Unsecure” items are really items that are Hidden and Protected so they have a checksum associated to them.

The demo can be found here

Step 1- Compile Package

Since I don’t have access to DMBS_CRYPTO on apex.oracle.com I’ve used a dummy encryption method. You’ll need to grant execute on DBMS_CRYPTO from SYS:

GRANT EXECUTE ON DBMS_CRYPTO TO giffy; -- Where "giffy" is your schema name

Compile the following code in your schema

pkg_apex_sec.pks

CREATE OR REPLACE PACKAGE pkg_apex_sec
AS
  /**
   * Returns secure value
   * @param p_value
   * @return
   */
  FUNCTION f_get_sec_val (
    p_value IN VARCHAR2
  )
    RETURN VARCHAR2;

  /**
   * Checks if secured value is valid
   * @param p_hashed_val (case sensitive
   * @return 'Y' or 'N'
   */
  FUNCTION is_valid_hashed_val (
    p_hash IN VARCHAR2
  )
    RETURN VARCHAR2;

  /**
   * unsecure value given the hash
   * @param p_hash
   * @return unsecure number
   */
  FUNCTION f_get_val (
    p_hash IN VARCHAR2
  )
    RETURN VARCHAR2;

   /**
  * Sets unsec values in the page given the secure values
  * @param p_page_id Page ID to set. Default current page
  */
  PROCEDURE sp_set_page_unsec_values (
    p_page_id IN apex_application_pages.page_id%TYPE DEFAULT v ('APP_PAGE_ID')
  );

  /**
   * Set all the secure values given the unsecure values
   * @param p_page_id Page ID. Default current page
   */
  PROCEDURE sp_set_page_sec_values (
    p_page_id IN apex_application_pages.page_id%TYPE DEFAULT v ('APP_PAGE_ID')
  );
END pkg_apex_sec;

pkg_apex_sec.pkb

CREATE OR REPLACE PACKAGE BODY pkg_apex_sec
AS
  -- Constants
  gc_delim CONSTANT VARCHAR2 (1) := '@';

/**
 * Returns hashed value
 * May require sys to grant access to dbms_crypto
 *  - GRANT EXECUTE ON DBMS_CRYPTO TO ;
 * @param p_source
 * @param p_key
 * @return hashed value
 */
  FUNCTION f_get_md5 (
    p_source IN VARCHAR2,
    p_key IN VARCHAR2
  )
    RETURN VARCHAR2
  IS
    v_key VARCHAR2 (4000) := p_key;
  BEGIN
    -- Normally your key should be different from the source. (this is up to you to maintain)
    -- For simplicity this function will see if they are the same. If so we'll append the app_id
    IF p_source = p_key THEN
      v_key := p_key || v ('APP_ID');
    END IF;

    -- Can't use DBMS_CRYPTO in apex.oracle.com. Using generic coding
    -- RETURN DBMS_CRYPTO.mac (src => UTL_RAW.cast_to_raw (p_source), typ => 2, KEY => UTL_RAW.cast_to_raw (v_key));
    RETURN p_source || p_key || 123;                                                                     -- DELETE THIS.
  END f_get_md5;

/**
 * Returns secure value
 * @param p_value
 * @return
 */
  FUNCTION f_get_sec_val (
    p_value IN VARCHAR2
  )
    RETURN VARCHAR2
  AS
  BEGIN
    -- For the Key value I'm arbitrarily appending the app id. You should change this to something that is secure to your code.
    -- You can add :app_session as well, provided you're not using session 0 or external links (ie. from emails etc)
    RETURN p_value || gc_delim || f_get_md5 (p_source => p_value, p_key => p_value || v ('APP_ID'));
  END f_get_sec_val;

  /**
   * Checks if secured value is valid
   * @param p_hashed_val (case sensitive
   * @return 'Y' or 'N'
   */
  FUNCTION is_valid_hashed_val (
    p_hash IN VARCHAR2
  )
    RETURN VARCHAR2
  AS
    v_value VARCHAR2 (4000);
  BEGIN
    v_value := REPLACE (REGEXP_SUBSTR (p_hash, '^[[:print:]]+' || gc_delim), gc_delim);

    IF p_hash = f_get_sec_val (p_value => v_value) THEN
      RETURN 'Y';
    ELSE
      RETURN 'N';
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN 'N';
  END is_valid_hashed_val;

  /**
   * unsecure value given the hash
   * @param p_hash
   * @return unsecure number
   */
  FUNCTION f_get_val (
    p_hash IN VARCHAR2
  )
    RETURN VARCHAR2
  AS
  BEGIN
    IF is_valid_hashed_val (p_hash => p_hash) = 'N' THEN
      RETURN NULL;
    END IF;

    RETURN (REPLACE (REGEXP_SUBSTR (p_hash, '^[[:print:]]+' || gc_delim), gc_delim));
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END f_get_val;

  /**
   * Sets unsec values in the page given the secure values
   * @param p_page_id Page ID to set. Default current page
   */
  PROCEDURE sp_set_page_unsec_values (
    p_page_id IN apex_application_pages.page_id%TYPE DEFAULT v ('APP_PAGE_ID')
  )
  AS
    v_app_id apex_applications.application_id%TYPE := v ('APP_ID');
  BEGIN
    BEGIN
      -- Set all the unsecure values from the secure values
      FOR x IN (SELECT a1.item_name item_name_sec,
                       a2.item_name
                  FROM apex_application_page_items a1,
                       apex_application_page_items a2
                 WHERE a1.application_id = v_app_id
                   AND a1.page_id = p_page_id
                   AND a1.item_name LIKE '%_SEC'
                   -- Find corresponding item name
                   AND a2.application_id = a1.application_id
                   AND a2.page_id = a1.page_id
                   AND RTRIM (a1.item_name, '_SEC') = a2.item_name) LOOP
        apex_util.set_session_state (x.item_name, pkg_apex_sec.f_get_val (v (x.item_name_sec)));
      END LOOP;
    END;
  END sp_set_page_unsec_values;

  /**
   * Set all the secure values given the unsecure values
   * @param p_page_id Page ID. Default current page
   */
  PROCEDURE sp_set_page_sec_values (
    p_page_id IN apex_application_pages.page_id%TYPE DEFAULT v ('APP_PAGE_ID')
  )
  AS
    v_app_id apex_applications.application_id%TYPE := v ('APP_ID');
  BEGIN
    -- Set all the secure values from the secure values
    FOR x IN (SELECT a1.item_name item_name_sec,
                     a2.item_name
                FROM apex_application_page_items a1,
                     apex_application_page_items a2
               WHERE a1.application_id = v_app_id
                 AND a1.page_id = p_page_id
                 AND a1.item_name LIKE '%_SEC'
                 -- Find corresponding item name
                 AND a2.application_id = a1.application_id
                 AND a2.page_id = a1.page_id
                 AND RTRIM (a1.item_name, '_SEC') = a2.item_name) LOOP
      IF v (x.item_name) IS NOT NULL THEN
        apex_util.set_session_state (x.item_name_sec, pkg_apex_sec.f_get_sec_val (v (x.item_name)));
      END IF;
    END LOOP;
  END sp_set_page_sec_values;
END pkg_apex_sec;

Step 2- Create Application Processes

Create Application Processes (On Load Before Header) called: AP_SET_SEC_PAGE_ITEMS. This will allow you to pass IDs in the URL as you normally would.

BEGIN
  pkg_apex_sec.sp_set_page_sec_values;
END;

Create Application Processes (On Submit and Before Computation ) called: AP_SET_UNSEC_PAGE_ITEMS

BEGIN
  pkg_apex_sec.sp_set_page_unsec_values;
END;

Create Application Process (On Demand) called AP_NULL to set values in session (using AJAX).

BEGIN
  pkg_apex_sec.sp_set_page_unsec_values; -- Update the unsec values from secure values for the current page
  NULL;
END;

Step 3 - Create page with Interactive Report (IR)

SELECT *
  FROM emp
 WHERE empno = NVL (:p1500_empno, empno)

Step 4 - Create “Secure” Id LOV and a “Hidden and Protected” unsecured fields

Create P1500_EMPNO as Hidden and Protected. It’s extremely important that you make the field Hidden and Protected so users can’t alter the value in the form.
Create P1500_EMPNO_SEC as a Select List. LOV:

Instead of:

SELECT e.ename d,
       e.empno r
  FROM emp e

Use:

SELECT e.ename d,
       pkg_apex_sec.f_get_sec_val (e.empno) r
  FROM emp e

Besides adding the application processes etc, this is the only significant change that you’ll need to make to your application

At this point your application is good to go. Steps 5 and 6 are included only for demonstration purposes.

Step 5 - JavaScript
This JavaScript will be used to simulate the onChange event for a select list

function onLovChange(pThisId){
  var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=AP_NULL',$v('pFlowStepId'));  
  get.add(pThisId, $v(pThisId));
  vReturn = get.get();  
}

Step 6 - Button
Create a Submit button that will submit and branch to the same page. This will demonstrate a “normal” submit process
Create a button called “AJAX”. URL = javascript:onLovChange(‘P1500_EMPNO_SEC’);

I haven’t used this in production yet so their may be some changes that I add to the code. If you have any feedback please leave a comment.

APEX Interactive Reports - Customize Wait Display Part 2

APEX Interactive Reports - Custom Wait Display Part 2

Note: If you’re using APEX 4.0 please see this post: http://www.talkapex.com/2010/08/apex-40-interactive-reports-customize.html

A few weeks ago I wrote about customizing the default APEX IR wait logo: http://apex-smb.blogspot.com/2009/04/apex-interactive-reports-customize-wait.html. Here is the second part of that article which focuses on overwriting APEX’s dispIRBusyGraphics function.

Overriding the default “Busy Graphic” function can be used to customize messages/images as well as preventing users from resubmitting IR request which may take a long time.

An example of this can be found here

- 1: Create IR Report

-- Trying to make this a slow query to demonstrate the IR loader
SELECT     e.*, sum(e.sal) over() test
      FROM emp e
CONNECT BY LEVEL <= 5

- 2: Download jQuery and Simple Modal and upload the JavaScript files in the Application’s Static Files:

jQuery: http://jquery.com/
Simple Modal: http://www.ericmmartin.com/projects/simplemodal/

- 3: Create an HTML region and add the following:





Custom Download for APEX Interactive Reports

A few months ago I needed to modify the downloaded Interactive Report (IR) to CSV function so that we could include group headings, report description, and report parameters (please see Column Groups in APEX Interactive Reports on how to display column groups in IR). Denes Kubicek has a package to download regular reports in Excel format, however it does not return the query that the user is currently viewing. This is really important since users may add columns, change the ordering of columns, etc. When users download a report it should reflect what is currently being displayed on the screen.

After digging through the APEX packages I was able to write some code to generate my own custom download function for IRs. Since the code is fairly long you’ll need to get a copy of the code here (please save the file as pkg_apex_report.zip). Note: This code is NOT ready for a production environment!. It’s also important to note that some of the grant privileges could have security implications and is not recommend to be run for public applications. If you need to use these grants in a production instance I suggest creating a special “APEX” schema and write some custom wrapper functions. Since grants are required from the APEX schema I can’t post a working example of this application on apex.oracle.com.

Now that I’m done with my disclaimers here’s how to customize IR downloads (don’t forget to download the code). Please note that the schema I developed in this was called “giffy”.

1- Grant Privileges

  -- Change "apex_030200" to the version of APEX that you are using
  -- Change "giffy" to your schema
  GRANT EXECUTE ON apex_030200.wwv_flow_worksheet_standard TO giffy;
  CREATE OR REPLACE SYNONYM giffy.wwv_flow_worksheet_standard FOR apex_030200.wwv_flow_worksheet_standard

  GRANT EXECUTE ON apex_030200.wwv_flow_conditions TO giffy;
  CREATE OR REPLACE SYNONYM giffy.wwv_flow_conditions FOR apex_030200.wwv_flow_conditions;

  GRANT EXECUTE ON apex_030200.wwv_flow_worksheet TO giffy;
  CREATE OR REPLACE SYNONYM giffy.wwv_flow_worksheet FOR apex_030200.wwv_flow_worksheet;

  GRANT EXECUTE ON apex_030200.wwv_flow_render_query TO giffy;
  CREATE OR REPLACE SYNONYM giffy.wwv_flow_render_query FOR apex_030200.wwv_flow_render_query;

2- Create Package apex_report

Download code here (save the file as pkg_apex_report.zip)
In SQL*Plus run:

  @pkg_apex_report.pks
  @pkg_apex_report.pkb

3- Create Interactive Report region

  SELECT *
    FROM emp

4- Create “Download Page”

  • Create a new HTML Page.

  • Create a page item P3_BASE_REPORT_ID

  • Create a page item P3_PAGE_ID

  • Create a PL/SQL Process

    • Point: On Load Before Header
    • PL/SQL:
  DECLARE
    v_region_id apex_application_page_regions.region_id%TYPE;
    v_base_report_id apex_application_page_ir_rpt.base_report_id%TYPE;
    v_page_id apex_application_page_ir_rpt.page_id%TYPE;
  BEGIN
    v_base_report_id := :p3_base_report_id;
    v_page_id := :p3_page_id;

    SELECT ir.region_id
      INTO v_region_id
      FROM apex_application_page_ir_rpt irr,
           apex_application_page_ir ir
     WHERE irr.application_id = :app_id
       AND irr.page_id = v_page_id
       AND irr.session_id = :app_session
       AND irr.base_report_id = v_base_report_id
       AND irr.interactive_report_id = ir.interactive_report_id;

    pkg_apex_report.sp_download_report (p_page_id            => v_page_id,
                                        p_region_id          => v_region_id,
                                        p_base_report_id     => v_base_report_id,
                                        p_format_typ         => 'CSV'
                                       );
  END;

5- Alter Interactive Report Download Link
Please see APEX Interactive Report - Download with One Click for more information about this code. You’ll need jQuery for this.

//Note: "3" is my download page
$(document).ready(function() {
  $('.dhtmlSubMenuN[title="Download"]').attr('href','f?p=' + $v('pFlowId') + ':3:' + $v('pInstance') + '::NO:3:P3_PAGE_ID,P3_BASE_REPORT_ID:' + $v('pFlowStepId') + ',' + $v('apexir_REPORT_ID'));  
});

At this point you should be able to run your interactive report and download the custom CSV file. You’ll notice that there’s a block of HTML that appears at the top of the CSV file. This is caused by the call (wwv_flow_worksheet.get_worksheet_report_query) to get the SQL the user is currently looking at. I don’t have a fix or a work around (if you know of one please post in comment section)

Please post any bugs, suggestions, or updates to the code as I’d like to create a production version for this code.

APEX Interactive Reports - Customize Wait Display Part 1

We recently needed to change, and move, the default Interactive Report (IR) AJAX “Loading” image (spinning wheel):

After some investigation they’re several things you can do depending on your requirements. A demo application is available here.

Part 2 is here: http://apex-smb.blogspot.com/2009/04/apex-interactive-reports-customize-wait_28.html

Before we go over the solution it’s important to review where the image is located and how it gets toggled on and off. The image is stored in a span region which is hidden by default. The code looks like this:

  
    ![](/i/ws/ajax-loader.gif)
  

If you want to manually make it visible you can run the following code:

  $('#apexir_LOADER').show();

The JS code to toggle the image is stored in: apex_ns_3_1.js. You can view an uncompressed version of this file in your installation directory: apex_3.1.2\apex\images\javascript\uncompressed\apex_ns_3_1.js To toggle the image, APEX calls the following JS function: apex.worksheet.ws._BusyGraphic (We won’t be using this function in this post but I’ll do another posting on how we can use it for a completely customizable solution)

First Let’s build a IR page with the emp table:

select *
from emp
connect by level <= 3

The easiest option is if all you need to do is change the image:

  $('#apexir_LOADER img')[0].src="#IMAGE_PREFIX#processing3.gif";

The new image, processing3.gif, is part of APEX as well. You can reference your own images if you’d like.

In Part 2 I’ll go over changing the actual function that shows and hides the loading image. This will give you complete control of the loading message.