Building a Simple Web PL/SQL Application

Advantages

There are several things I like about developing small applications using Web PL/SQL in Oracle Portal:

Security and authentication are built thanks to Portal's Single Sign On,
You have total control over page layout,
Performance is great, even for very data-intensive applications.

Developing an Application

The way I create a web PL/SQL application is to start by creating it's own database schema in Portal. I then create a database link back to our central database where all the corporate data is held. Within this new schema I create synonyms for the tables, views and other objects I may need.

For a quick demonstration we can use the familiar EMP table from the scott/tiger schema.

Once you have logged into Portal, click on the link for the Navigator view, then choose the Database tab. You should be able to see a link to create a new schema (if you don't you'll need to get the DBA role granted to your user account). Click on this to create a new schema PLSQL_DEMO. Give it a password so you can log into the schema using other tools. In this schema you can create a database link to the scott/tiger schema on your database server and then create a sysnoym for the EMP table. If this is not possible for any reason then you can just create the table locally in the new schema (you can find the script here).

Let's then create a database package in the the new PLSQL_DEMO schema. You can do this using Portal itself or another tool such as SqlDeveloper, SQL Plus or TOAD. My personal favourite is SqlDeveloper - it's powerful and free.

We can call our package EMP_PKG (not very imaginative, but it does the job). We want to be able to search, view and edit the data in the EMP table, so our package will need the following procedures:

Show_Search_Criteria(...);

Show_Search_Results(...);

Show_Edit_Emp(...);

Action_Edit_Emp(...);

The parameters required will include empno and whatever search terms we wish to use. We can keep it simple by having a single search parameter. Our package will also include a variable for the url of the Portal page. I'm going to assume that we will display three elements on the same page at the same time.

The full code for the package header will therefore be:


create or replace package EMP_PKG as



  -- url for the portal page 

  gv_url varchar2(200) := 'http:\\host:port/...';

  gv_pageid varchar2(20) := '123456';

  gv_dad := 'PORTAL';

  -- we can cut and paste these from the browser later



  -- procedure to show the search criteria

  procedure Show_Search_Criteria(p_empno in number, p_search in varchar2);



  -- procedure to show the search results as a list

  procedure Show_Search_Results(p_empno in number, p_search in varchar2);



  -- procedure to show/edit a single record

  procedure Show_Edit_Emp(p_empno in number, p_search in varchar2);



  -- procedure to save any changes made

  procedure Action_Edit_Emp

  (

    p_empno in number,

    p_search in varchar2,

    p_ename in varchar2,

    p_job in varchar2,

    p_mgr in number,

    p_hiredate in date,

    p_sal in number,

    p_comm in number,

    p_deptno in number,

    p_submit in varchar2

  );



end;

For now, our package body will be just this:


create or replace package body EMP_PKG as



  -- procedure to show the search criteria

  procedure Show_Search_Criteria(p_empno in number, p_search in varchar2)

  as

  begin

    htp.p('Search');

  end;



  -- procedure to show the search results as a list

  procedure Show_Search_Results(p_empno in number, p_search in varchar2)

  as

  begin

     htp.p('Search Results');

  end;



  -- procedure to show/edit a single record

  procedure Show_Edit_Emp(p_empno in number, p_search in varchar2)

  as

  begin

     htp.p('View/Edit');

  end;



  -- procedure to save any changes made

  procedure Action_Edit_Emp

  (

    p_empno in number,

    p_search in varchar2,

    p_ename in varchar2,

    p_job in varchar2,

    p_mgr in number,

    p_hiredate in date,

    p_sal in number,

    p_comm in number,

    p_deptno in number,

    p_submit in varchar2

  )

  as

  begin

     htp.p('No changes saved.  Please press the back button.');

  end;



end;

Page Layout

Our single-page mini-application is going to look something like this:




  +---------------------------------------------------------+

  |         +-------------+   +----+                                     |

  |  Search |                  |   | Go |                                     |

  |         +-------------+   +----+                                     |

  +---------------------------------------------------------+

  +------------------------+ +------------------------------+

  | Id      Employee             | |      +------+                        |

  | 7369   SMITH             | | Id    | 7369 |                        |

  | 7499   ALLEN             | |      +------+                        |

  | 7521   WARD              | |      +---------------------+ |

  | 7566   JONES             | | Name | SMITH               | |

  +------------------------+ |      +---------------------+ |

                             |                                             |

                             | +------+                              |

                             | | Save |                     |

                             | +------+                              |

                                +------------------------------+

Creating the layout is the fun part, so let's do it now. Click on the Page Groups tab in Portal and create a new page group. We can call our page group PLSQL_DEMO with a display name like "Web PL/SQL Demo Application". Just accept the defaults for now.

Click on the link to edit the root page.

Use the pencil tool to edit the bottom region on the screen. Select Portlets and click Apply. You can then give the region a title of Search Criteria (I prefer to leave Display Title unchecked) and uncheck all the checkboxes at the bottom of the page. I normally set the two dimensions to zero as it makes it easier to line things up. Click Ok.

Use the tools to create another region below the one you've just worked on. You can then create a region to the right of the new bottom region. Use the pencil tools to edit each of these new regions. The will both be for Portlets and can be named "Search Results" and "View/Edit Employee" as appropriate. Make sure the checkboxes are all unchecked.

Now we need to create the portlets to display the content.

Creating Portlets

Click on the link for the navigator and select the middle tab. Choose Local Providers and create a new database provider. For consistency we can call it PLSQL_DEMO and make sure it is linked to the PLSQL_DEMO schema or you won't see your EMP_PKG package.

Click on the link to create a new Dynamic Page. We will call this DYN_SEARCH_CRITERIA with a display name of "Search Criteria". Remove the sample text and replace it with: <ORACLE> begin emp_pkg.Show_Search_Criteria(:p_empno, :p_search); end; </ORACLE>

The colon in front of each parameter is important. Click on the next button three times and check the boxes to make the bound variables publically visible. This will allow the Portal page to pass parameters from the url to the packages. You can now click on Finish. View the page to check it works before going on to create two more dynamic pages.

Page name: DYN_SEARCH_RESULTS
Display name: Search Results
<ORACLE> begin emp_pkg.Show_Search_Results(:p_empno, :p_search); end; </ORACLE>

Page name: DYN_EDIT_EMP
Display name: Edit Employee
<ORACLE> begin emp_pkg.Show_Edit_Emp(:p_empno, :p_search); end; </ORACLE>

Don't forget to make the bound variables visible in each case. Now we can add these portlets to our application.

Adding Portlets

Click on the Page Groups tab and edit the root page of our Page Group. On the toolbar of the Search Criteria region there is a button for adding a portlet. Clicking on this will display a list of portlet providers. You can find ours listed under the Staging Area. Click on Search Criteria to shuttle it across to the right and click Ok.

In the same way you can add the other two portlets to their appropriate regions.

Now we need to tell the portal page how to pass information to the portlets and so on to the packages. We do this by adding parameters to the page. This is just a way of telling Portal to automatically read any matching parameter values entered in the url. Click on the link near the top of the page title page properties. Choose the tab called parameters. In the entry box in the middle of the page type in p_empno and click on Add. Next add a parameter called p_search.

You can now expand out the dynamic page portlets you created and map the page parameters to the bound variables you made public. Click Ok when you've done all three.

Now all we have to do is complete procedures in the EMP_PKG package, but before leaving the edit page screen, copy the url in the browser to the clipboard.

Completing the Package

Open the package header and set the gv_url, gv_pageid and gv_dad variables by pasting from the clipboard. Make sure you remove the &_mode=16 from the end of the gv_url variable.

The first procedure is going to display a simple html form. When the submit button is pressed it will cause the page to be redisplayed with the search term added to the url. Here's the code:



  procedure Show_Search_Criteria(p_empno in number, p_search in varchar2)

  as

  begin

    htp.p('<form action="' || gv_url || '" method="get">');

    htp.p('<input type="hidden" name="_pageid" value="' || gv_pageid || '" />');

    htp.p('<input type="hidden" name="_dad" value="' || gv_dad || '" />');

    htp.p('Search: ');

    htp.p('<input type="text" name="p_search" value="' || p_search || '" />');

    htp.p('<input type="submit" value="Go" />');

    htp.p('</form>');

  end;

Test this before going any further. You should see the search term you entered appear in the page url when you press the Go button. It should also still be displayed in the search box.

Displaying the search results is easier:




  procedure Show_Search_Results(p_empno in number, p_search in varchar2)

  as

    lv_url varchar2(500);

  begin

    lv_url := gv_url || '&p_search=' || p_search;

    htp.p('<table>');

    htp.p('<tr><th>Id</th><th>Employee</th></tr>');

    for e in (select * from emp where upper(name) like '%' || upper(p_search) || '%' order by 2)

    loop

      if e.empno = p_empno then

        -- this row is currently selected so make it bold

        htp.p('<b><tr><td>');

        htp.p('<a href="' || lv_url || '&p_empno=' || e.empno || '">' || e.empno || '</a>');

        htp.p('</td><td>' || e.name || '</td></tr></b>');

      else

        -- this row is not selected

        htp.p('<tr><td>');

        htp.p('<a href="' || lv_url || '&p_empno=' || e.empno || '">' || e.empno || '</a>');

        htp.p('</td><td>' || e.name || '</td></tr>');

      end if;

    end loop;

    htp.p('</table>');

  end;

The next procedure shows the record for the selected employee:




  procedure Show_Edit_Emp(p_empno in number, p_search in varchar2)

  as

  begin

    for e in (select * from EMP where empno = p_empno)

    loop

      htp.p('<form action="PLSQL_EMP.EMP_PKG.Action_Edit_Emp" method="post">');

      htp.p('<table>');


      htp.p('<tr><td>Emp Id:</td><td>');

      htp.p('<input type="text" name="p_empno" value="' || p_empno || '" />');

      htp.p('</td></tr>');


      htp.p('<input type="hidden" name="p_search" value="' || p_search || '" />');


      htp.p('<tr><td>Name:</td><td>');

      htp.p('<input type="text" name="p_ename" value="' || e.ename || '" />');

      htp.p('</td></tr>');


      -- The other fields could be made visible quite simply

      htp.p('<input type="hidden" name="p_job" value="' || e.job || '" />');

      htp.p('<input type="hidden" name="p_mgr" value="' || e.mgr || '" />');

      htp.p('<input type="hidden" name="p_hiredate" value="' || e.hiredate || '" />');

      htp.p('<input type="hidden" name="p_sal" value="' || e.sal || '" />');

      htp.p('<input type="hidden" name="p_comm" value="' || e.comm || '" />');

      htp.p('<input type="hidden" name="p_deptno" value="' || e.deptno || '" />');


      htp.p('</table>');

      htp.p('<input type="submit" value="Save" />');

      htp.p('</form>');

    end loop;

  end;

Now we can write the code to update the database. procedure Action_Edit_Emp ( p_empno in number, p_search in varchar2, p_ename in varchar2, p_job in varchar2, p_mgr in number, p_hiredate in date, p_sal in number, p_comm in number, p_deptno in number, p_submit in varchar2 ) as begin if p_empno is not null then -- Update the record update EMP set ename = p_ename, job = p_job, mgr = p_mgr, hiredate = p_hiredate, sal = p_sal, comm = p_comm, deptno = p_deptno where empno = p_empno; commit; end if; end;

If you test this out you will find that you get a page not found error. This is easily fixed by granting execute rights on EMP_PKG to public. If I told you at the start you wouldn't know what was happening the next time it occurred. You can get the same error message if the number, order or type of fields in the html form do not match the procedures parameters.

Summary

At first glance you will probably think that this is a lot of code to hand write for a very simple application. You are right, it is. Other modern web development tools like Application Express or JDeveloper can achieve similar results very quickly. The first advantage of using Web PL/SQL is that you have full control over the HTML that the application produces - you are limited only by your imagination and your HTML skills. Secondly, you have access to all the Portal tools and packages (and there are many), including great security and identity management. Thirdly, the performance of Web PL/SQL is fantastic and the load on the server is minimal, allowing you to deliver more complex applications to more users from a smaller server.