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: ');

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)