Portal Tutorial - Adding a List of Employees

Introduction

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

Now we can start adding some content to our page. We will start with the simplest of dynamic pages: a select list. In the firs lesson we created a database provider, so lets navigate to that (its on the Providers tab of the navigator, listed under Database Providers).

Creating a Dynamic Page

When you click on it it will open up to display an empty list. Above this empty space is a list of the new things you can create. Click on Dynamic Page and give our new page a name, display name and description and then click on next

Now we are prompted to enter some html information. Most of this is standard stuff, but tucked in the middle is a tag you're probably not familiar with: <ORACLE> ... </ORACLE>. This is unique to dynmica pages and functions just like the <% ... %> tags in jsp pages or the <?PHP ?> tags in PHP: that is, they allow you to enter code into an html page. This being Oracle, you get to use PL/SQL code.

We're going to enter a select statement. Very similar to the default example Portal kindly provided us with. Edit the heading to read "Employee List" and the change the SQL to be:

select *
from scott.emp
where deptno = nvl(:p_deptno, deptno)

Just in case you're not familiar with Oracle SQL syntax (sorry if you already know this, please skip on to the next step), the where clause compares the deptno value to a bound variable called p_deptno (the : in front of it indicates that it is a variable). The nvl( , ) function checks to see if the first value evaluates to null. If it is not null, it returns the value, if it is null, it returns the second value. We are going to use this to display only those employees which are in the same department as the one we have specified in the variable. If we haven't specified anything then p_deptno will be null and we will be shown all employees. Now click on next

We now get to zoom in on the PL/SQL code between the ORACLE tags. If there was a syntax error in your code you would have got an error message instead. If you see this it's Ok so you can hit next to go to the next page.

Click next again to get to this page: you need to do something here. The bind variable you entered in the SQL statement is shown here. You have the opportunity to make it public. This means that the variable can be visible to other Portal components and they can assign a value to it. Check this (it's off by default) and you can click on Finish.

You are presented with the Manage Portlet screen. Check that the page is going to work by clicking on the Run button or the Run as Portlet. It doesn't make much difference.

The main thing to notice is that the dynamic page automatically gives us column headers and makes an attempt to present it nicely. The decoration around the page will be lost when we add this to our own page. Let's do that next.

Adding a Dynamic Page

Close the preview window and click on Close in the Manage Portlet screen. Click on the Page Groups tab in the navigator and edit the root page of our Demo page group. Click on the add portlet icon on the Emp region. On the left of the screen is a list of all the available portlets, the blank area on the right shows the one's we've added to this region. Click on the Portet Staging Area link in the list on the left and you should see the Demo Database Provider. Click on this and the Employee List dynamic page should appear. Clicking on this causes an instance of this portlet to appear in the region as shown below. Click Ok.

The list of employees should now be displayed in all it's glory:

Congratulations, you now have a web page with dynamic content. Next we're going to add a combobox to make it interactive, so that the user can filter the employee list by department.