Oracle9iAS Portal Developer Kit (PDK)
How to build PL/SQL Portlets using the PL/SQL Generator

Introduction

The PL/SQL Generator utility generates installable PL/SQL template code for the database provider and its portlets from the provider definition file (same style as the provider.xml). The generated SQL file contains blank portlets with appropriate show modes, as defined by the values parsed from the .xml file. You can then tailor your provider and its portlets to your specific needs. You can use the PL/SQL Generator in the following manner:

• You can get access to our hosted PL/SQL Generator on the Portal Studio at the following URL: PLSQL Provider Generator
• Create your provider.xml file based on the PDK-Java provider.xml conventions. You can see these conventions below. Then select your provider.xml.
• Type in the name of your provider. Make sure that the name does not contain any spaces.
• Click on Generate.
• Download and save the generated file of type ".sql".
• Customize the generated PL/SQL code to suit your specific needs with your favorite text editor.
• Follow the instructions within the .sql file to install the provider and portlets within your database.
• You will need to manually register the provider you installed.
  a.Schema Name: schema where you installed the script
  b.Package Name: <Name entered within generator.html>_PROVIDER
  For example: MYFIRSTPORTLET_PROVIDER

Provider.xml Conventions

Please review our article Provider Definition XML Tag Reference for full details about the provider.xml conventions and tags for PDK-Java. The provider.xml used for the PL/SQL Generator follows the same style but some of the tags mentioned in this document only apply to PDK-Java.

The list of the supported provider.xml parameters:

• <provider>
• <portlet>
• <id>
• <name>
• <title>
• <description>
• <timeout>
• <timeoutMsg>
• <defaultLocale>
• <showEdit>
• <showEditDefault>
• <showPreview>
• <hasHelp>
• <hasHelp>
• <hasAbout>
• <imageUrl>
• <thumbnailUrl>

Take a look at a small example provider with two portlets to illustrate the usage of the provider.xml.

Important Portlet Functions and Procedures

A PL/SQL portlet, besides others, always has to implement the following important functions:

• show - Renders the portlet.
• get_portlet_info - Returns the portlet attributes, such as provider_id, title, name, description, timeout, etc.
• is_runnable - Secures the portlet.
• register/deregister - Performs initialization or cleanup.

Now let us examine these functions in details.

SHOW Portlet Procedure

The show is the most important procedure of a portlet. It is responsible for rendering the portlet when showing a page. It gets called automatically by the portal framework. When called, the portlet should produce the necessary HTML or XML/XSL to visualize itself for the selected mode. The page produced should contain the restricted set of HTML tags that would be legal to place within a cell of an HTML table (TD element). For example, it should not have the HEAD or BODY HTML tags since these will be applied by the portal framework. The content generated by the portlet should use the tags from the cascading style sheet (CSS). The show function should first decide in what show mode it was called then should render the portlet accordingly with the help of the HTP and HTF PL/SQL Web Toolkit packages. This function is also responsible for drawing the portlet header with the wwui_api_portlet.draw_portlet_header call and the portlet borders with passing the appropriate flag to the wwui_api_portlet.open_portlet procedure. The following are the valid show modes for a portlet:

• MODE_SHOW - Shows the portlet's content on a page
• MODE_SHOW_ABOUT - Shows the about content of the portlet on a page. The about information of the portlet may contain the portlet's version number, date, etc.
• MODE_SHOW_EDIT - Shows the Edit (i.e. Customize) UI
• MODE_SHOW_HELP - Shows the Help for the portlet
• MODE_SHOW_EDIT_DEFAULTS - Shows the UI to edit the default customizations for the portlet
• MODE_SHOW_DETAILS - Shows the details page for the portlet
• MODE_PREVIEW - Shows the preview of the portlet

Look at the following example: show.sql

GET_PORTLET_INFO Portlet Function

This function returns the specifications of the portlet. It actually returns, in a wwpro_api_provider.portlet_record structure, the details of the portlet implemented by this package. It returns information about the portlet name, title, description, available show modes, etc. An example may look like this: get_portlet_info.sql

IS_RUNNABLE Portlet Function

The is_runnable function stands for securing the portlet. It basically returns whether this portlet can be viewed by the current user by calling the wwctx_api.is_logged_on PDK function. This method may be called in one of the following two situations:

1. When a request is made to display the portlet in any of the show modes. This case a non-null value is passed in the p_reference_path parameter to this function. This is a situation in which portlet instance security check should be made.
2. When the portlet repository is displayed a call is made to the provider (i.e. is_portlet_runnable) for each portlet to check whether this portlet should be shown to the user. This is the situation in which general portlet security check is performed. In this case a null value is passed in the p_reference_path parameter to this function.

The portlet may use the context package wwctx_api in the implementation of the portlet-specific security mechanism for each situation described above. Here is a little example: is_runnable.sql

REGISTER / DEREGISTER Portlet Procedures

The register portlet procedure performs initialization. The framework will call upon this function when the portlet is added to a page. This provides the portlet an opportunity to perform instance-level initialization such as the establishment of defaults for end-user customization.

The deregister portlet procedure performs cleanup. The framework will call upon this function when a portlet is removed from a page. This provides the portlet an opportunity to perform instance-level cleanup such as the removal of end-user and default customizations.

Here is an example for the register function.

Here is an example for the deregister function.

Important Provider Functions and Procedures

The provider is a mediator between the portal and its portlets. The portal framework always calls the provider in case it wants to operate on a portlet and then the provider delegates the request to the appropriate portlet.

The PL/SQL providers, besides others, always have to implement the following important functions or operations:

• Define the portlet identifier (portlet_id)
• show_portlet - Shows the selected portlet
• get_portlet_list - Gets a list of the portlets of the provider
• get_portlet - Collects information about the selected portlet
• register_provider/deregister_provider - Performs provider level intialization or cleanup
• is_portlet_runnable - Checks if the connected user has the appropriate privileges to run the selected portlet

Let us take a closer look at these functions.

Define the Portlet identifier

The first thing the provider has to do is maintaining a portlet id as a package constant. The provider will identify its portlets with these constants. For example:

PORTLET_MYFIRSTPORTLET constant integer := 1;

SHOW_PORTLET Provider Procedure

The show_portlet is the most important provider procedure. It is responsible for showing the individual portlets. It first decides which portlet it has to contact based on the portlet_id then it calls the show procedure of the requested portlet. Take a look at our example: show_portlet.sql

GET_PORTLET_LIST Provider Function

The get_portlet_list provider function returns a list of its portlets in a wwpro_api_provider.portlet_table by calling the get_portlet function of each of its portlets. This function may be called in 2 modes. These 2 modes are determined by the value of the p_security_level parameter. If p_security_level is false this method should generate a list of portlets implemented by this provider without respect to any portlet security checks. The framework will call this method with p_security_level set to false when it performs a refresh of the portlet repository in order to get the complete list of portlets for this provider that will be made available in the portal. If p_security_level is true this method should generate a list of portlets in which a security check is performed for the portlets. Such a call may be performed by the framework to retrieve the list of portlets to display on the Add Portlets screen. When generating this list the currently logged on user may be taken into account so that only the portlets that the user can access are returned. Here is an example: get_portlet_list.sql

GET_PORTLET Provider Function

The get_portlet provider function returns the specification of the selected portlet. It first decides which portlet it has to contact based on the portlet_id then it calls the get_portlet_info function of the requested portlet. Take a look at our example: get_portlet.sql

REGISTER_PROVIDER / DEREGISTER_PROVIDER Provider Procedures

The register_provider procedure is called when a provider is registered using the provider registry UI or APIs. This provides the provider an opportunity to perform provider level initializations. The deregister_provider procedure is called when a provider is deregistered using the provider registry UI or APIs. This provides the providers an opportunity to perform provider level cleanup.

IS_PORTLET_RUNNABLE Provider Procedure

This function returns whether the requested portlet can be viewed by the current user. It first decides which portlet it has to contact, based on the portlet_id, then it calls the is_runnable function of the requested portlet.

Example: is_portlet_runnable.sql

Creating a PL/SQL Portlet using the PL/SQL Generator

Use the PL/SQL Generator to generate the template

1. Create a provider.xml file that describes your portlet and provider.

2. Now go to http://ophsws1.us.oracle.com/generator.html to access the PL/SQL Generator.

3. Click on Browse to find your provider.xml file.

4. Provide a name for your provider ie. myfirstportlet.

5. Click on Generate.

6. Save the generated file as myfirstportlet.sql.

You can take a look at the solution here: myfirstportlet.sql. Please note, that the generated code may change in the future with the newer PL/SQL Generator versions.

Modify the Show procedure of the portlet to render "Hello <username> - This is my first portlet!"

1. Open the generated myfirstportlet.sql with your favorite text editor.

2. Locate the following code in the show procedure of the portlet package:

3. You can get the username with the wwctx_api.get_user PDK function. Change the above code to the following in order to display the requested text:

You can check the solution here: myfirstportlet.sql

Modify Edit mode to display the current title

1. Locate the following code in the show procedure of the portlet package:

2. Change it to the following:

You can take a look at the complete solution here: myfirstportlet.sql

Install your portlet in the Database

1. connect <portal_schema>/<portal_password>@<connect_string> ie. connect portal/portal

2. grant connect, resource to plsqlprovider identified by plsqlprovider;

Note: It is important to call the provider schema plsqlprovider because otherwise the personalization will not work. In case you cannot or do not want to call it plsqlprovider, you have to replace the schema name in the htp.formopen call in the edit_prefs procedure once you get to the personalization practice.

3. @provsyns plsqlprovider

4. connect plsqlprovider/plsqlprovider@<connect_string> ie. connect plsqlprovider/plsqlprovider

5. @myfirstportlet

6. Enter myfirstportlet.log when the script asks for the log file.

Register your provider in Portal

1. Go to the Administer tab on the Portal homepage.

2. Click on Add a Portlet Provider

3. Set the following values for your provider:

• Name: myfirstportlet_provider
• Display Name: My First Portlet Provider
• Timeout: 100
• Timeout Message: My First Portlet Provider Timed out
• Implementation Style: Database
• Owning Schema: PLSQLPROVIDER
• Package Name: myfirstportlet_provider

You can leave the rest of the settings on default and click OK.

Add the portlet to a Portal Page

1. Go to Navigator.

2. On the Pages tab click on My Pages.

3. Click on the name of the page you would like to add your portlet on.

4. Click on Edit Page.

5. Click on the Add Portlets icon.

6. Find and click on My First Portlet Provider in the provider content area.

7. Click on the My First Portlet to select your portlet.

8. Click on OK to get back to the Edit Page.

9. Click on Close to take a look at your portlet.

Enhancing the PL/SQL Portlet

Add Personalization to EDIT mode for changing the Portlet Title

1. You first have to create a preference path. Name the the preference path 'oracle.portal.pdk.myfirstportlet'. You have to add a package constant to the package body of the portlet package:

PORTLET_PATH constant varchar2(256):= 'oracle.portal.pdk.myfirstportlet';

2. You have to create a package constant to store the preference in the preference storage in the same spot as for the preference path. Name the preference 'myfirstportlet_title':

PREFNAME_TITLE constant varchar2(30) := 'myfirstportlet_title';

3. You have to write the register procedure for he portlet, which creates a path and the preferences for the portlet instance in the preference store. Your register function should look like this: register.sql

4. Create the deregister procedure to delete the preferences: deregister.sql

5. Now you should add the code to render the EDIT mode of your portlet. Place this code in a procedure called edit_prefs. This procedure retrieves the title preference value and shows it as the default value for the Title edit box. If the value of this preference is null then the title is received from the portlet info record. The procedure also renders 3 buttons to handle the interaction: edit_prefs.sql

6. In case the user submits the customization form the value for the Title field should be stored. Write the code for this and place it into a procedure named save_prefs.sql.

7. Please note, that this procedure gets called from the browser right after submitting the customization form. This cannot be done unless you make the save_prefs procedure a public package procedure. In order to achieve this you have to add the following code in the package specification of the myfirstportlet_portlet package:

8. It is also necessary to grant execute privilege to public on the portlet package so that the save_prefs procedure can be publicly called.

grant execute on myfirstportlet_portlet to public;

9. Modify the show procedure in order to render the portlet with the customized title and to call the edit_prefs procedure for the EDIT mode. First modify the show procedure to add a local variable l_title with the title preference assigned. In case the preference store is empty the title needs to be taken from the portlet_record. This initialization should happen after retrieving the portlet information by calling get_portlet_info:

10. In the draw_portlet_header call the l_title local variable should be passed to the p_title parameter:

11. Finally the edit_prefs procedure should be called in EDIT mode:

Take a look at the complete show procedure for the personalization: show.sql

12. Test your portlet whether the title of the header changes after the customization.

You can take a look at the solution here: myfirstportlet.sql.

NOTE: In case you chose not to complete all the above test but to run the solution script, you have to grant execute privilege on the portlet package to public exactly as described in step 8 after running the myfirstportlet.sql script:

grant execute on myfirstportlet_portlet to public;

Also note, that you will have to remove the portlet from the page and add it again in order to carry out the initialization task of the register procedure.

Add NLS to enable multiple languages

NOTE: You can only take advantage of the NLS service after installing a foreign language, in our case German, in the Portal. You can find more information on this in the Portal help system.

1. Create a seed file that loads English and German NLS strings for the welcome message and for the error handling into the database and save it as myfirstportlet_seed.sql.

2. Create a domain and a sub-domain definition for your NLS strings and error messages in the body of your portlet package as package constants:

3. Correct the show procedure of your portlet to retrieve the appropriate welcome text based on the language setting of the browser. First modify the show procedure to add a local variable l_welcome_text to hold the language specific welcome text. Retrieve its value with the get_string function of the NLS service:

4. You have to print the value of this variable in the SHOW mode of the portlet. Locate the following code in the body of the show procedure of your portlet:

5. Change it to the following:

Take a look at the complete show procedure for the NLS service: show.sql

6. Run a SQL*Plus and connect as plsqlprovider.

7. Run the myfirstportlet_seed.sql with the following commands:

@myfirstportlet_seed
/

8. You can test your portlet now if the welcome message shows up in English with English Portal setting and in German with German Portal setting.

You can check out the solution here: myfirstportlet.sql.

Add Error handling to check for invalid portlet titles

1. Create a function that checks for errors in the portlet title. It ensures that the passed p_string is not longer than 60 characters and does not contain invalid characters. Name it entered_text_is_valid.

2. Define an exception for the invalid title in the body of your portlet package as a package variable:

INVALID_TITLE_EXCEPTION exception;

3. Add the code to raise INVALID_TITLE_EXCEPTION in case of invalid title in the save_prefs procedure you wrote earlier:

4. Add the code to catch the INVALID_TITLE_EXCEPTION in the save_prefs procedure:

Take a look at the complete save_prefs procedure: save_prefs.sql

You can find the complete solution here: myfirstportlet.sql