Portal Tutorial - Adding a Filter

Introduction

If you've completed the first three tutorials, you will have a page something like this:

In this example the list of employees is quite short, but in a real-world situation you've probably got far more items to display than can reasonably fit on a single page. What we need is to be able to limit the list, and we're going to add a drop down combobox that will allow us to filter by department.

We'll start by creating the package that will ultimately display the combobox as a simple "Hello World" type package. I find this is a good technique to use when adding new functionality to a page, because it allows you to get the page working properly before adding complexity.

Creating the Package

Switch to the navigator view, select your schema and click on the link to create a new package:

Add in the code in the package declaration for a procedure called show() with a parameter called p_deptno. This is so we can display the currently selected value. Here's what it looks like:

And here's the code for you to copy:

create or repalce package demo.dept_pkg
as
  procedure show(p_deptno in varchar2);
end;

create or replace package body demo.dept_pkg
as
  gv_url varchar2(500);

  procedure show(p_deptno in varchar2) as
  begin
    htp.p('Hello');
  end;
end;

The global variable gv_url can be used to contain the url of the portal page. The command htp.p() sends the varchar2 parameter to the page which is returned to the user. We'll call this from a new dynamic page.

Creating the Dynamic Page

As we did to display the list of employees, switch to the Providers tab and the DEMO database provider. Create a new dynamic page:

Remove all the excess code to leave just the <ORACLE> and </ORACLE> tags. Within those tags write small pl/slq block to call our new procedure using a bound variable as the parameter. Remember to tick the Public check box when you get to it.

Adding the Dynamic Page

In the navigator view switch to the Page Groups tab and edit the Demo root page. In the region on the left of the screen click on the icon to add a new portlet. Click through the Portlet Staging Area and the Demo database provider to find the new dynamic page we called Select Department. Shuttle it across to the selected portlets list and click Ok to see our page:

Both our portlets have parameters, but we have to explain to Portal how these parameters will be sourced. In our case we want them to be picked up from the url. Click on the link for page properties near the top of the screen. Choose the tab called Parameters, type the name of the parameter p_deptno into the New Page Parameter field and click add:

Now expand the two portlets by clicking on the triangle beside them. Set the source value for these both to be a page parameter: we have only defined one page parameter so it will default to that for us:

We can now test that the filter will work by manually setting the parameter value in the url. Add the following text to the end of the url: &p_deptno=20 and press enter.

This will cause our page to redisplay (still in edit mode because there is a parameter called _mode=16) but the p_deptno parameter should be passed to both dynamic page portlets. The department portlet does nothing with this, but our list of employees should be filtered. If it's not working, check the url first: it must be letter perfect and it is case sensitive. The next place to try is the sql select statement in the employee dynamic page.

Creating a ComboBox

Setting parameters manually in the url works but is far from user-friendly: a drop down list of departments is what we need. Navigate back to the Demo schema and edit the dept_pkg package. We need to change the package body to this:

Create or Replace Package BODY DEMO.DEPT_PKG 
as
    gv_url varchar2(500);

    procedure show(p_deptno in varchar2) as
    begin
        htp.p('<form action="" method="get">');

        htp.p('<input type="hidden" name="_dad" value="portal">');
        htp.p('<input type="hidden" name="_schema" value="PORTAL">');
        htp.p('<input type="hidden" name="_pageid" value="1061">'); -- copy from the url

        htp.p('<select name="p_deptno">');
        for d in (select * from scott.dept)
        loop
          if to_char(d.deptno) = p_deptno then
            htp.p('<option value="' || d.deptno || '" selected="selected">' || d.dname || '</option>');
          else
            htp.p('<option value="' || d.deptno || '">' || d.dname || '</option>');
          end if;
        end loop;
        htp.p('</select>');
        htp.p('<input type="submit" value="Go">');
        htp.p('</form>');
    end;
end;

I'll just run through what we've got here. The first part of the procedure opens an html form. Leaving the action null causes the form to display the current page, which is what we want. Using the get method means that the values in the forms will be added to the url in the format ?name=value&name=value&... We then have three hidden form values: the first two are likely to be the same on your server, but the third will be different. You can get the value for all these from looking at the url when displaying your new portal page. You need to get this right or Portal will show either nothing or a random page.

Next we start the instruct html to display a drop down box using the select instruction. Each value in the list is added as an option. I've wrapped this in a for cursor loop, with a test to check to see if the value in the loop matches the page parameter passed in. If so, that option is marked as selected. This is a useful feature that makes your application a little more user friendly.

Finally, a submit button is added with the text "Go" and the form is closed. Click ok and go back to your page to see if it's working. You should be able to select department names from the drop down list and when you click Ok the list of employees should be filtered appropriately.