# Oracle9iAS Portal Developer Kit (PDK)
How to Build a PL/SQL Portlet

Last Update: | November 1, 2000
Status: | Production
Version: | Any PDK Release

## Introduction

Once you have successfully installed and deployed the PL/SQL sample portlets supplied by the PDK, you may want to build your own PL/SQL portlets. You can do this by simply modifying the code of the given samples. This will enable you to implement any additional functionality to the default behavior of the portlets. This will also accelerate the process of building portlets from scratch since you only write minimal code.

This article describes how to build a basic PL/SQL portlet using a given sample. This provides a guideline for building upon given samples to add any new features. This article will specifically deal with modifying the way in which a portlet is displayed on a page. After reading this article, please review the An Overview of Writing Portlets for Database Providers article for information on adding additional modes to your portlet.

## Assumptions

You have already installed the samples downloaded with the PDK and understand the steps required to display a PL/SQL portlet on a portal page.

## Creating a New Portlet

This section describes how to build your own PL/SQL portlet using the HelloWorld sample in PDK.

The HelloWorld sample is a portlet that is contained in the Starter Provider sample. The Starter Provider sample consists of the following files:

1. starter_provider.pks: The package specification of the starter provider.
2. starter_provider.pkb: The package body of the starter provider.
3. helloworld_portlet..pks: The package specification of the hello world portlet.
4. helloworld_portlet.pkb: The package body of the hello world portlet.
5. snoop_portlet.pks: The package specification of the snoop portlet.
6. snoop_portlet.pks: The package body of the snoop portlet.
7. insintpr.sql: The installation script for the starter provider.

### STEP 1 - Implement Portlet Package

The instructions below guide you through the steps of how to modify the helloworld_portlet.pks and helloworld_portlet.pkb files to create your own portlet package. The lines of code in blue describe what you need to change.

From the downloaded samples, take the helloworld_portlet.pks and helloworld_portlet.pkb files which are the package specification and package body for this portlet.

- Copy these files into your own file versions such as my_first_portlet.pks and my_first_portlet.pkb and change the package names at the start and end of the files. i.e. perform the following 2 replacements:

- Replace helloworld_portlet with my_first_portlet in the create or replace package line (both in package specification and package body).
- Replace helloworld_portlet with my_first_portlet in the end line at the end of the file (both in package specification and package body).

- Scroll my_first_portlet.pkb file to the section that contains the procedure get_portlet_info. Modify the relevant portlet record values highlighted below. i.e.

- Change the portlet id.
- Change the portlet title.
- Change the portlet name.

- Then scroll to the section that contains the procedure show. Add simple functionality that displays a welcome message along with the logged-in user. The piece of code that we are adding is in BOLD. You may add your own lines of code in this section that display additional information.

>     create or replace package body my_first_portlet is

>         ...

>         function get_portlet_info(
>             p_provider_id in integer,
>             p_language in varchar2
>         ) return wwpro_api_provider.portlet_record is
>             l_portlet wwpro_api_provider.portlet_record;
>         begin
>             l_portlet.id := starter_provider.PORTLET_FIRST;
>             l_portlet.provider_id := p_provider_id;
>             l_portlet.title := 'My First Portlet';
>             l_portlet.name := 'My_First_Portlet';
>             ...
>         end get_portlet_info;

>         ...

>         procedure show(
>             p_portlet_record in out wwpro_api_provider.portlet_runtime_record
>         ) is
>             l_portlet wwpro_api_provider.portlet_record;
>             l_text_name varchar2(200);
>             l_text varchar2(200);
>         begin
>             ...

>             /*
>             Display the content of the portlet in the show mode.
>             */
>             htp.p(wwui_api_portlet.portlet_text(
>                 p_string  => 'Hello World - Mode Show',
>                 p_level   => 1)
>             );

>             /*
>             Add the functionality you want. In this case we are adding
>             a welcome message addressed to the current user.
>             */
>             l_text_name := 'Welcome to my first portlet ' || wwctx_api.get_user;

>             l_text := wwui_api_portlet.portlet_text(
>                 p_string   =>  l_text_name
>                 p_level    =>  1
>             );

>             htp.p(l_text);
>             htp.para;

>             if (p_portlet_record.has_border) then
>                 wwui_api_portlet.close_portlet;
>             end if;

>             ...

>         end show;

>         ...

>     end my_first_portlet;
>     /

- Save this file as my_first_portlet.pkb.

### STEP 2 - Implement Provider Package

The instructions in this section guide you through the steps that you need to follow to implement the provider package for your portlet. Two options are presented. The first option is to add the portlet to an existing provider package and the second option is to add the portlet in a new package. As a first time portlet developer you may find it easier to add the portlet to an existing provider package.

#### Option 1 : Add new Portlet to an existing Provider

- Copy starter_provider.pks and starter_provider.pkb to starter_provider2.pks and starter_provider2.pkb.
- Add a constant for your portlet in the provider package specification, i.e. add the PORTLET_FIRST constant as indicated by the line in BOLD in the code section below in the file starter_provider2.pks. This constant is used as the ID for the new portlet within the provider. The value used must be unique within that provider.

create or replace package starter_provider is /** * This package is used as an example to show how providers can be created * in the portal system. * * This provider contains the following portlets: * * Hello World (PORTLET_HELLOWORLD) * Snoop (PORTLET_SNOOP) * My First Portlet (PORTLET_FIRST) */ PORTLET_HELLOWORLD constant integer := 1; PORTLET_SNOOP constant integer := 2; PORTLET_FIRST constant integer := 3; ...

- Add a call for the new portlet's get_portlet_info in the get_portlet of the provider package. This is done by adding the call my_first_portlet.get_portlet_info in get_portlet. The get_portlet function allows the portal to retrieve information for the new portlet when it needs to. The new lines are indicated in BOLD in the section of code below.

create or replace package body starter_provider is /** * Public APIs for the Starter_Provider provider. * * These methods are required by the provider framework in order * to support this provider implementation. */ ... function get_portlet( p_provider_id in integer, p_portlet_id in integer, p_language in varchar2 ) return wwpro_api_provider.portlet_record is begin if (p_portlet_id = PORTLET_HELLOWORLD) then return helloworld_portlet.get_portlet_info( p_provider_id = > p_provider_id, p_language = > p_language ); elsif ... elsif (p_portlet_id = PORTLET_FIRST) then return my_first_portlet.get_portlet_info( p_provider_id => p_provider_id, p_language = > p_language ); ... end get_portlet; ...

- Add the new portlet in the list of portlets returned by the provider. This is done by adding the new portlet in the get_portlet_list method of the provider. The get_portlet_list method is used to indicate to the portal the portlets that the provider implements. The new lines are indicated in BOLD in the section of code below.

function get_portlet_list( ... ) return wwpro_api_provider.portlet_list is ... begin if (p_security_level = false ) then ... l_cnt := l_cnt + 1; l_portlet_list(l_cnt) := get_portlet( p_provider_id => p_provider_id, p_portlet_id => PORTLET_FIRST, p_language => p_language ); ... else ... if (my_first_portlet.is_runnable( p_provider_id => p_provider_id, p_reference_path => null) ) then l_cnt := l_cnt + 1; l_portlet_list(l_cnt) := get_portlet( p_provider_id => p_provider_id, p_portlet_id => PORTLET_FIRST, p_language => p_language ); end if; ... end if; return l_portlet_list; end get_portlet_list;

- Modify the provider's is_portlet_runnable method to add a call to the is_runnable method of the new portlet. The new lines are indicated in BOLD in the section of code below.

function is_portlet_runnable( p_portlet_instance in wwpro_api_provider.portlet_instance_record ) return boolean is begin if (p_portlet_instance.portlet_id = PORTLET_HELLOWORLD) then return helloworld_portlet.is_runnable( p_provider_id => p_portlet_instance.provider_id, p_reference_path => p_portlet_instance.reference_path ); ... elsif (p_portlet_instance.portlet_id = PORTLET_FIRST) then return my_first_portlet.is_runnable( p_provider_id => p_portlet_instance.provider_id, p_reference_path => p_portlet_instance.reference_path ); ...

- Using a similar pattern modify the provider methods indicated in the table below.

Provider Method | Change
`procedure register` | `my_first_portlet.register(p_portlet_instance)`
`procedure deregister` | `my_first_portlet.deregister(p_portlet_instance)`
`function describe_portlet_parameters` | `my_first_portlet.describe_parameters( p_provider_id, p_language)`
`procedure show_portlet` | `my_first_portlet.show(p_portlet_record)`
`procedure copy_portlet` | `my_first_portlet.copy(p_portlet_record)`

- Save and close the file (i.e. starter_provider2.pkb).
- Login to Oracle Portal and from the Administer tab, click on 'Display Portlet Repository' link in the Portlet Repository portlet and check to see whether the Provider you are modifying (i.e. Starter Provider) is currently listed with the two old portlets (HelloWorld and Snoop).
- Connect to SQL*Plus as the provider schema owner (i.e. the schema in which the starter provider is installed).
- Compile the modified and new packages. The following files need to be compiled in the following sequence.

- starter_provider2.pks
- my_first_portlet.pks
- starter_provider2.pkb
- my_first_portlet.pkb

- Check that everything compiles without any errors. If any errors are reported fix them prior to continuing with the following step.
- Login to Oracle Portal and perform a refresh of the portlet repository. This can be done as follows:

- Go to the Administer tab.
- From the portlet repository portlet click on Refresh Portlet Repository link.

- Once the portlet repository refresh is complete your new portlet should show up under the Starter Provider.
- Troubleshooting Note: If your portlet does not show up after you performed a refresh of the portlet repository, check the schema owner of the Provider to make sure it is the same schema in which you compiled your packages. You can find out the schema owner of the provider by checking the Owning Schema field of the Starter Provider in the Provider Registration UI.

#### Option 2 : Add new Portlet to a new Provider

- Create a new provider package using the existing packages for starter provider. Copy the sample Provider package specification and body, starter_provider.pks and starter_provider.pkb, to my_provider.pks and my_provider.pkb respectively.
- Change the package names at the start and end of these files from starter_provider to my_provider.
- Then ensure that your Provider makes calls to only your portlet procedures. Use the guidelines detailed above for making calls to the portlet procedures and functions. Effectively you can remove the references to the HelloWorld and Snoop Portlets.
- Create a provider schema and compile your packages.
- Finally register the new Provider using the same information from the PDK installation instructions.

### STEP 3 - Add the New Portlet in a Portal Page

- Create a new page and add your new portlet to your page. Note the title and text that you display in your portlet.

## Utilities

Alternatively, you can also make use of the PL/SQL Generator for creating a PL/SQL Portlet. The PL/SQL Generator is a utility which generates PL/SQL provider and portlet code. It accepts an XML file (similar in format to the provider.xml file) as input, and generates corresponding provider & portlet skeleton code, which you can then modify according to your requirements.
You can access the hosted version of the PL/SQL Generator here. For more information on the PL/SQL Generator, please refer to this document.
Given below is the sample format of the XML file accepted by the PL/SQL Generator as input.
Please NOTE: The tags shown below in BOLD are mandatory tags which are expected by the PL/SQL Generator. The value of these mandatory tags can be either true or false.

<!-- This is a sample provider.xml for PLSQL Generator --> <provider> <portlet> <name>Test_Portlet</name> <title>Test Portlet Title</title> <description>This is a Test portlet</description> <timeout>30</timeout> <timeoutMsg>Test Portlet Timed Out</timeoutMsg> <showEdit>true</showEdit> <showEditDefault>true</showEditDefault> <showDetails>true</showDetails> <showPreview>true</showPreview> <hasHelp>true</hasHelp> <hasAbout>true</hasAbout> <defaultLocale>en.us</defaultLocale> <imageUrl>http://us.a1.yimg.com/us.yimg.com/i/ww/m5v5.gif</imageUrl> <thumbnailUrl>http://www.yahoo.com/</thumbnailUrl> </portlet> </provider>

## Conclusion

> - For the solution to this exercise refer to the howtostarter source files under the plsql category of PDK.
> - Now that you have successfully built a PL/SQL portlet, review the Database Services example source code to add additional features to your portlet such as session storage and customization.

Revision History: |
Revision No | Last Update
1.0 | July 29, 2002

Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA 94065, USA http://www.oracle.com/ | Worldwide Inquiries: 1-800-ORACLE1 Fax 650.506.7200 | Copyright and Corporate Info