Martin Giffy D'Souza on Oracle APEX: July 2009

Thursday, July 30, 2009

APEX: $x_disableItem

I had an issue where I disabled a radio button (after selecting a value) using the APEX JavaScript function ($x_disableItem) but my selected value wasn't being saved.

After some testing I noticed that if I selected a value, disabled the radio, then submitted the page item's value would be null (empty string).

You can view an example of this here: http://apex.oracle.com/pls/otn/f?p=20195:2200

They're several ways to fix this problem. The easiest would be to "undisable" (i.e. re-enable) the item before the page is submitted. I know "undisable" is not a word but to re-enable a page item you need to call the disable function: $x_disableItem('PX',false);

Tuesday, July 28, 2009

APEX: How to Pass Multiselect List Values in URL

When passing multiselect list values, or any multi LOV, in the URL you may have some unexpected behaviors. Here's an example: http://apex.oracle.com/pls/otn/f?p=20195:2100

If you take a look at the example you'll notice that the URL doesn't contain all the values that you may have submitted. For example I selected KING (7839), BLAKE (7698), and CLARK (7782). I would expect the URL to contain these values when I pass them via the URL. Instead the URL looks like this:

http://apex.oracle.com/pls/otn/f?p=20195:2100:1674288126968745::NO::P2100_EMPNO_LIST:7839:7698

Notice how only 2 values are passed in? That's because the delimiter used in LOVs is the same that is used in the URL. What can be even more confusing is that I selected 3 values but when I pass them in the URL only 1 is "accepted". This is because the last value in the URL is the "PrinterFriendly" parameter (please see: http://download.oracle.com/docs/cd/E14373_01/appdev.32/e11838/concept.htm#BEIJCIAG)

To fix the issue for all your mutli LOVs you can use a similar technique that I used to resolve the %null% issue. An example of the fix can be found here: http://apex.oracle.com/pls/otn/f?p=20195:2110. If you take a look at the example and select several employees the URL now looks like this:

http://apex.oracle.com/pls/otn/f?p=20195:2110:1674288126968745::NO::P2110_EMPNO_LIST:7839*7698*7782

Notice how the delimiters are *s for the empnos?

1- Create Application Process to replace colon delimiter with *
Note: You aren't limited to using * as your delimiter

Name: AP_REMOVE_URL_DELIM_FROM_ITEMS
Sequence: -10 (helps ensure that it is run before any other process
Point: On Submit: After Page Submission - Before Computations and Validations

BEGIN
  FOR x IN (SELECT item_name,
                   REPLACE (v (item_name), ':', '*') new_item_value
              FROM (SELECT item_name
                      FROM apex_application_page_items aapi
                     WHERE aapi.application_id = :app_id
                       AND aapi.page_id = :app_page_id
                       AND LOWER (display_as) IN ('checkbox', 'shuttle', 'select list', 'multiselect list') -- Limiting to these types. Can remove if you want to handle all types
                       AND ROWNUM > 0) x
             WHERE INSTR (v (x.item_name), ':') > 0) LOOP
    apex_util.set_session_state (x.item_name, x.new_item_value);
  END LOOP;
END;

Note: This will replace the colon delimiter with a *. This may change some of your validations, page processes etc.

2- Create Application Process to replace * with colon delimiter on page load

Name: AP_RESET_URL_DELIM_FROM_ITEMS
Sequence: -10 (helps ensure that it is run before any other process)
Point: On Load: Before Header (page template header)

BEGIN
  FOR x IN
    (SELECT item_name,
            REPLACE (v (item_name), '*', ':') new_item_value
       FROM (SELECT item_name
               FROM apex_application_page_items aapi
              WHERE aapi.application_id = :app_id
                AND aapi.page_id = :app_page_id
                AND LOWER (display_as) IN
                      ('checkbox', 'shuttle', 'select list', 'multiselect list')   -- Limiting to these types. Can remove if you want
                AND ROWNUM > 0) x
      WHERE INSTR (v (x.item_name), '*') > 0) LOOP
    apex_util.set_session_state (x.item_name, x.new_item_value);
  END LOOP;
END;

Friday, July 24, 2009

Oracle: How to update all sequences

If you ever do data refreshes from production to development or test environments you may run into an issue where your sequences are not up to date. It seems that Oracle exports the sequences first, then the data. If your sequence numbers change during the entire export process you may get errors when using them in your refreshed schema.

To fix this problem you can try to find where your sequences are used and get the MAX(value) to find the next value. Alternatively you can just add a large random number, say 1,000,000, to all your sequences. For most users this will fix the problem and is very easy to do. Here's how:

-- Update all sequences
DECLARE
  v_increase_by                     NUMBER;
  v_bkp_increment_by            NUMBER;
  v_str                         VARCHAR2 (1000);
  v_count                       NUMBER;
BEGIN
  v_increase_by                  := 1000000;
 
  FOR rec IN (SELECT *
              FROM   user_sequences) LOOP
    -- Backup current incrementation number
    v_bkp_increment_by         := rec.increment_by;
    -- Alter the sequence to increase by a defined amount
    v_str                      := 'alter sequence ' || rec.sequence_name || ' increment by ' || v_increase_by;
 
    EXECUTE IMMEDIATE v_str;
 
    -- Increase by that amount
    v_str                      := 'select ' || rec.sequence_name || '.nextval from dual';
 
    EXECUTE IMMEDIATE v_str
    INTO              v_count;
 
    -- Reset the increment factor
    v_str                      := 'alter sequence ' || rec.sequence_name || ' increment by ' || v_bkp_increment_by;
 
    EXECUTE IMMEDIATE v_str;
  END LOOP;
END;
/

Wednesday, July 22, 2009

Oracle: Advanced Error Messages

This is not an APEX specific post, however it can be useful for error handling.

A colleague showed me a great way to get more useful debug information. Normally I used SQLERRM and SQLCODE in an exception to display or store error messages. Using DBMS_UTILITY you can get more detailed Oracle error messages. Here's an example:

-- I put this in a package for demo purposes
CREATE OR REPLACE PACKAGE pkg_err_test
AS
  PROCEDURE sp_err_test (
    p_empno IN emp.empno%TYPE
  );
END pkg_err_test;
 
CREATE OR REPLACE PACKAGE BODY pkg_err_test
AS
  PROCEDURE sp_err_test (
    p_empno IN emp.empno%TYPE
  )
  AS
    v_ename emp.ename%TYPE;
  BEGIN
    SELECT ename
      INTO v_ename
      FROM emp
     WHERE empno = p_empno;
 
    DBMS_OUTPUT.put_line ('Employee name is: ' || v_ename);
  EXCEPTION
    WHEN OTHERS THEN
      -- Basic Error Message
      DBMS_OUTPUT.put_line ('Old Error Message: ' || SUBSTR (SQLERRM, 1, 255));
      DBMS_OUTPUT.put_line ('Old Err Code: ' || SQLCODE);
      -- Advanced Error Messages
      DBMS_OUTPUT.put_line ('-- New Error Messages --');
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);   -- Error Message
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);   -- Where it occurred
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack);   -- Call Stack
  END sp_err_test;
END pkg_err_test;
 
-- Run the error test with an invalid employee number so an exception will be raised
EXEC pkg_err_test.sp_err_test(p_empno => 123);

DBMS Output:

Old Error Message: ORA-01403: no data found
Old Err Code: 100
-- New Error Messages --
ORA-01403: no data found

ORA-06512: at "GIFFY.PKG_ERR_TEST", line 9

----- PL/SQL Call Stack -----
object line object
handle number name
362D7814 24 package body GIFFY.PKG_ERR_TEST
362D70E0 1 anonymous block

The error message is displayed as well as where the error occurred and the call stack. In large systems this can be very helpful. You should be aware that when called from a package, it does not list the procedure or function (as seen in this example) where the error occurred so you may need to hard code the function or procedure name in your error message.

Tuesday, July 21, 2009

How to resolve %null% issue in APEX LOVs

Patrick Wolf mentioned this at ODTUG Kaleidoscope this year.

After you implement your first LOV in an APEX application you'll quickly learn about the %null% problem. APEX substitutes an empty string for Null return value as %null%.

They're several workarounds, like using "-1" as the NULL value. Or modifying your query using "'%' || 'null%'". For example:

SELECT ename,
       empno
  FROM emp
 WHERE empno = DECODE (:p_empno, '%' || 'null%', empno, NULL, empno, :p_empno)

Instead of using workarounds you can convert %null% to NULL (empty string) by creating the following application process:

Application Process: AP_SET_LOV_NULLS
Process Point: On Submit - Before Computations and Validations

BEGIN
  FOR x IN (SELECT *
              FROM (SELECT item_name
                      FROM apex_application_page_items aapi
                     WHERE aapi.application_id = :app_id
                       AND aapi.page_id = :app_page_id
                       AND LOWER (aapi.lov_display_null) = 'yes'
                       AND aapi.lov_definition IS NOT NULL
                       AND aapi.lov_null_value IS NULL
                       AND ROWNUM > 0) x
             WHERE LOWER (v (x.item_name)) = '%' || 'null%') LOOP
    apex_util.set_session_state (x.item_name, NULL);
  END LOOP;
END;

Monday, July 20, 2009

JavaScript: ? = If Else

This is not directly related to APEX however it will help if you use a lot of JavaScript

When I first started to develop web based applications and used JavaScript I came across some JS code with a ? in it. I didn't know what it was for and Googling "JavaScript ?" didn't help either. Here's a quick summary on how it works

Boolean ? true action : false action

So this:

var x = 2;
if (x > 1)
  window.alert('True');
else
  window.alert('False');

Becomes this:

var x = 2;
x>1 ? window.alert('True') : window.alert('False') ;

Thursday, July 16, 2009

APEX_APPLICATION.DO_SUBSTITUTIONS

I've run into several instances where I needed to store HTML in a table. The problem is sometimes the HTML references APEX Items. For example if your HTML needs to reference a picture, odds are you'll need to reference &APP_IMAGES. (or some image location item). In the past I've done manual REPLACE calls for known items, but it was fairly restrictive.

APEX has a great function (not yet documented to my knowledge) called APEX_APPLICATION.DO_SUBSTITUTIONS. If you pass in a string, it will substitute any APEX values. Here's an example app: http://apex.oracle.com/pls/otn/f?p=20195:2000

To create demo:

1- Create table and insert values

CREATE TABLE tp2000(line_content CLOB NOT NULL);
 
INSERT INTO tp2000
     VALUES ('Google Canada Picture: <img src="' || CHR (38) || 'APP_IMAGES.google_logo.gif">');
 
INSERT INTO tp2000
     VALUES ('My Current Session: ' || CHR (38) || 'APP_SESSION.');

2- Create Report Region (with substitutions)

SELECT apex_application.do_substitutions (line_content) content_with_subs
  FROM tp2000

3- Create Report Region (without substitutions)

SELECT line_content content_without_subs
  FROM tp2000

Wednesday, July 15, 2009

APEX: How to Develop in 2 Browser Tabs

I saw this during a presentation by Anton Nielsen last year. If you've developed with APEX for a while then you've probably wanted to have 2 developer tabs open on your browser at the same time. You'll quickly find out that this doesn't work to well.

There's an easy way around it. Lets say you're developing on an instance running on your laptop. The URL you normally go to looks something like: http://localhost:8080/apex/

Go into your hosts file (C:\WINDOWS\system32\drivers\etc\hosts for Windows users). You should see an entry like this:

127.0.0.1 localhost

Add: 127.0.0.1 giffy01 on a new line (where "giffy01" is any arbitrary name).

Your hosts file should now look like:

127.0.0.1 localhost
127.0.0.1 giffy01

In your favorite web browser,

open the following URLs in 2 different tabs:

http://localhost:8080/apex/
http://giffy01:8080/apex/

You can now have 2 development tabs open at the same time.

Tuesday, July 14, 2009

APEX: How to Dynamically Render Regions

Suppose you needed to enable and disable report regions on each page based on a parameter. You could add a condition to each region. If the conditions were all the same, the smart thing to do would be to create a function and have your condition reference the function.

What if your application had 100 pages? Would you remember to apply the condition to each report region in the 100 pages?

This is not a problem that most developers run into, however when you are building large applications something similar may come up. If you can find a way to dynamically control items, regions, processes, etc this can save on development time.

At the ODTUG Kaleidoscope conference Dennis Vanill gave a presentation on how to use Page 0 items to enable and disable APEX objects dynamically. Using this logic, here's an example on how to dynamically disable a region.

Note: Use this when appropriate. For basic conditions stick with using "regular" conditions

A demo is available here: http://apex.oracle.com/pls/otn/f?p=20195:1900

1- Create a page with some report regions

-- Interactive Report:
SELECT *
FROM emp
 
-- Regular Report
SELECT ename, sal
FROM emp

2- Create Page Process: On Load - Before Header

DECLARE
BEGIN
  IF NVL (:p1900_hide_reports_flag, 'N') = 'Y' THEN
    FOR x IN (SELECT region_id
                FROM apex_application_page_regions
               WHERE application_id = :app_id
                 AND page_id = :app_page_id
                 AND source_type IN ('Report', 'Interactive Report')) LOOP
      FOR i IN 1 .. apex_application.g_plug_id.COUNT LOOP
        IF apex_application.g_plug_id (i) = x.region_id THEN
          apex_application.g_plug_display_condition_type (i) := 'NEVER';
        END IF;
      END LOOP;
    END LOOP;
  END IF;
END;

2- (For Demo purposes only)
I added the following on Page 0 to display in the example application. This shows that no conditions were applied to a region

SELECT region_id,
       region_name,
       source_type,
       condition_type,
       condition_expression1,
       condition_expression2,
       build_option,
       authorization_scheme
  FROM apex_application_page_regions
 WHERE application_id = :app_id
   AND page_id = :app_page_id

You can use the same logic to control computations, items, etc. Take a look at apex_application (desc apex_application) for more options.

Thursday, July 2, 2009

APEX: Saving item values for each user

Someone asked me today if APEX could remember input values for specific page items. For example if you have a page with report parameters could APEX remember the report parameters that the user last used the next time they logged in?

Note: Please read comments below as APEX does support this out of the box on an individual item basis. This solution is to make the option configurable for large applications.

APEX doesn't support this out of the box, however it does have some great features which can enable you to do this. You can use cookies for this but I wanted to make the solution work no matter where the user was accessing the application from.

To make things a bit more difficult, I don't want to remember all item values on a page so I must be able to control which items are "remembered" and which items aren't. I can do this by using a naming convention in my items, however I don't want to rename all my page items (I already have a lot of them). Instead I decided to create a table which will list all the items a user can remember.

You can try the demo here (follow the instructions on the page).

CREATE TABLE tapex_remember_page_item(
application_id NUMBER NOT NULL,
page_id NUMBER NOT NULL, 
item_name VARCHAR2(255) NOT NULL);
 
-- You don't need to add a UK, however it may be a good idea. 
ALTER TABLE tapex_remember_page_item ADD(
  CONSTRAINT tapex_remember_page_item_uk1
  UNIQUE (application_id, page_id, item_name));
   
-- Since I name all my APEX items in uppercase, just do this as an extra precaution
CREATE OR REPLACE TRIGGER trg_tapex_remember_pg_itm_buir
  BEFORE UPDATE OR INSERT
  ON tapex_remember_page_item
  FOR EACH ROW
BEGIN
  :NEW.item_name := UPPER (:NEW.item_name);
END;
/
 
INSERT INTO tapex_remember_page_item
            (application_id, page_id, item_name)
     VALUES (20195, 1800, 'P1800_DEPTNO');
             
             
INSERT INTO tapex_remember_page_item
            (application_id, page_id, item_name)
     VALUES (20195, 1800, 'P1800_MIN_SAL');            

For this example we'll store the values as APEX Preferences, however you could easily create your own preferences table to manage your data. I think they're several advantages to managing the preferences in your own table, however if you have a small application with a limited number of users then I'd recommend using the APEX_UTIL preference options

Create 2 Application Processes:

AP_GET_PAGE_ITEM_PREFS
On Load: Before Header (page template header)

DECLARE
BEGIN
  FOR x IN (SELECT item_name
              FROM tapex_remember_page_item
             WHERE :app_page_id = page_id
               AND :app_id = application_id) LOOP
    apex_util.set_session_state (p_name      => x.item_name,
                                 p_value     => apex_util.get_preference (p_preference     => x.item_name,
                                                                          p_user           => :app_user
                                                                         )
                                );
  END LOOP;
END;

AP_SET_PAGE_ITEM_PREFS
On Submit: After Page Submission - After Computations and Validations

DECLARE
BEGIN
  FOR x IN (SELECT item_name
              FROM tapex_remember_page_item
             WHERE :app_page_id = page_id
               AND :app_id = application_id) LOOP
    apex_util.set_preference (p_preference => x.item_name, p_value => v (x.item_name), p_user => :app_user);
  END LOOP;
END;

For those of you that are curious APEX Preferences are stored in : apex_030200.wwv_flow_preferences$ where apex_030200 is the schema name for APEX (could also be called flows_xxxxxx)


Oracle Application Express Plugins	Expert Oracle Application Express

Beginning Oracle Application Express 4	Expert Oracle Application Express 5

Pages