Friday, December 05, 2008

Oracle Application Express (APEX): Three Kinds of Session State

One of the best features of APEX is its ability to manage session state for you. One of the trickiest things you run in to when getting in to advanced development, though, is understanding just when session state is updated and when it is persisted. The easiest way I can describe this is with some examples:

1. PERSISTED Session State
Most of the time you will be dealing with persisted session state--session state that resides within the database and that is available from subsequent page requests. Any time you set an item's value with a computation or process you get PERSISTED session state. If you pass an item and value on the URL you get PERSISTED session state. If you submit a page with a text item (or radiobox, checkbox, select list, hidden item, etc.) the item will get persisted session state.

That seems like most of the ways you set item session state, but there are a couple of other ways.

2. IN MEMORY Session State (IMSS)
This category really falls in to two parts: Item Rendering and Automated Row Fetch
So . . .

2A. Item Rendering
You can set item attributes of Source and Default. Let's say we set an text item (P2_VALUE10) to have default value to C2C. Run the page and you will see an item on the page with C2C in a text box. Check the session state (use the Session link at the bottom of the page) and you will find that P2_VALUE10 does not have any session state. How is this possible? It's right there on the page with C2C, right?

The value C2C was set IN MEMORY session state. It disappears immediately when the page is done rendering; it is not saved in the database. This is not usually an issue, because you are probably going to submit the page, in which case (according to point 1 above) it will store as PERSISTED session state.

A special note on item rendering in memory session state (IRIMSS :) ): IRIMSS happens as the item is rendered. Say you have 20 items on the page, with the tenth item, P2_VALUE10, having default value of C2C. If you have any computations or conditions inside of items 1..9 that depend on P2_VALUE10, it will not have the default value. For example, say P2_VALUE3 has a display condition: Display when P2_VALUE10 = C2C. P2_VALUE3 will not show. Items 11..20, though, will see P2_VALUE10 = C2C. It can be a bit confusing.

2B. Automated Row Fetch (ARF)
Using ARF produces IN MEMORY session state as described above, with a little twist. The ARF process allows you to choose whether to put the value of the item values into IMSS right when the process is run, or as the items are rendered. In early versions of APEX it always happened as the items were rendered. In recent versions the default is to put the value into IMSS when the ARF process runs.

Why Does It Matter
Why does it matter if it is PERSISTED Session State or IMSS? I have found many cases where IMSS caused confusion and bugs. A good example is breadcrumbs. Say we have a department report, which drills in to a department form, which in turn drills in to an employee form. You could have a breadcrumb like this:

Dept Report > Dept Form > Employee

But that is not very enlightening. Wouldn't it be better to have the following?

Dept Report > Accounting > Nielsen

Which means you would define something like this

Dept Report > &P2_DNAME. > &P3_LAST_NAME.

If you used ARF to set P2_DNAME, however, but did not submit page 2, instead clicking on an employee link, P2_DNAME will be null on the employee page, resulting in the following breadcrumb:

Dept Report > > Nielsen

There are many other similar scenarios. But, the underlying cause is IMSS.

If you need persisted session state, just do your own fetch process or computation. You can leave the ARF process if you want, it won't hurt to have both.

I hope this sheds a little light on APEX session state.

Thursday, December 04, 2008

Oracle Portal and Oracle BI Publisher

If you are using Oracle Portal and are used to using the Portal Forms Builder, you may want to create parameter forms for your BI reports in the Portal Forms Builder. This article provides a methodology for generating and redirecting to the BI Publisher URL from a Portal Form.

Step 1: Create a package that will do the redirect for you given the appropriate parameters.

--
--
create or replace
package bip1 as
g_url varchar2(32767);

-- add additional parameters as required
procedure bip_r
(
p_xf IN VARCHAR2 DEFAULT NULL,
p_custom1name IN VARCHAR2 DEFAULT NULL,
p_custom1val IN VARCHAR2 DEFAULT NULL,
p_custom2name IN VARCHAR2 DEFAULT NULL,
p_custom2val IN VARCHAR2 DEFAULT NULL);
end;
/

--
create or replace
package body bip1 as

procedure bip_r
(
p_xf IN VARCHAR2 DEFAULT NULL,
p_custom1name IN VARCHAR2 DEFAULT NULL,
p_custom1val IN VARCHAR2 DEFAULT NULL,
p_custom2name IN VARCHAR2 DEFAULT NULL,
p_custom2val IN VARCHAR2 DEFAULT NULL)
as
-- for information on determining the URL below, see
-- http://blogs.oracle.com/xmlpublisher/2006/07/accessing_xmlp_enterprise_repo.html
l_url varchar2(32767) := 'http://[Your_BIP_Server]:9704/xmlpserver/[your_folder]/[your_report]/[your_report].xdo?_xpf=&_xpt=1&...';
begin
l_url := l_url || '&_xf=' || p_xf || '&'
|| p_custom1name || '=' || p_custom1val || '&'
|| p_custom2name || '=' || p_custom2val ;

bip1.g_url := l_url;
end;
end;
/


Step 2: Create a Portal Form against a procedure and choose the bip1.bip_r procedure.

Step 3: Modify the Portal Form

a. p_xf is the allowable output formats: html, pdf, csv, etc.
b. p_custom1name (and all p_custom#name items) should be hidden and should be the name of your BI Publisher report variables
c. p_custom1val (and all p_custom#val items) are the values you want your users to enter, associated with the corresponding p_custom#name
d. At the Portal Form level, where you see the text area following
On successful submission of a form, execute this PL/SQL block or PL/SQL procedure:
Hint:
You can redirect your browser to a PL/SQL procedure, for example a procedure that creates a Web page, using either of these methods:

1. call('', '');
Redirects the browser to the procedure and passes a parameter containing the URL back to the form.
2. go('');
Redirects the browser to the procedure but does not pass a URL to return to the form.


Enter: go(bip1.g_url);


Running this form will redirect you to the correct BI Publisher URL.