APEX_UTIL.JSON_FROM_SQL No Rows Bug + Fix

I ran into an issue yesterday using APEX_UTIL.JSON_FROM_SQL with a query that returned no rows. This function is used in AJAX calls to return the results of a query as a JSON object.

To replicate this issue you can do the following in APEX:

1- Create an On Demand Application Process

-- AP_JSON_TEST
DECLARE
  v_sql VARCHAR2 (4000);
BEGIN
  -- Note: This query is meant to return no rows
  v_sql := 'SELECT ename FROM emp WHERE 1 = 2';
  
  -- Print JSON result set
  apex_util.json_from_sql (v_sql);
END;

2- Run the following JS code(easiest using firebug)

  var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=AP_TODO_DEL',$v('pFlowStepId'));  
  vReturn = get.get(); 

At this point if you're using FireBug you'll notice that "sqlerrm:ORA-06502: PL/SQL: numeric or value error" now appears in the console window.

At first glance it appears that you have something wrong with your Application Process or with your query. After some digging around I finally realized that it's a bug with APEX_UTIL.JSON_FROM_SQL.

The good news is that it's really easy to fix. All you need to do is catch any exceptions and return a JSON object with no rows:

-- AP_JSON_TEST

DECLARE
  v_sql VARCHAR2 (4000);
BEGIN
  -- Note: This query is meant to return no rows
  v_sql := 'SELECT ename FROM emp WHERE 1 = 2';
  -- Print JSON result set
  apex_util.json_from_sql (v_sql);
  
-- *** FIX ***  
EXCEPTION
  WHEN OTHERS THEN
    HTP.p ('{"row":[]}');
END;

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'