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

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

-- 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)

1 2 3 4

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

-- 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:

1 2 3 4 5 6

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

1 2 3 4 5 6 7 8 9

-- 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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54

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:

1 2 3 4 5 6 7 8

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:

1 2 3 4 5 6 7 8

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91

/**  * 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:

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

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

1 2 3 4 5 6 7 8 9

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

1 2 3 4 5 6 7 8 9 10

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

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

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:

1 2 3 4 5 6

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 7369@J92388502378540C (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:

1 2 3

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

Compile the following code in your schema

pkg_apex_sec.pks

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151

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 <schema>;  * @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; </schema>

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.

1 2 3 4 5

BEGIN   pkg_apex_sec.sp_set_page_sec_values; END;

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

1 2 3 4 5

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).

1 2 3 4 5 6

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)

1 2 3 4 5

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:

1 2 3 4 5

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

Use:

1 2 3 4 5

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

1 2 3 4 5 6 7

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.