Oracle9iAS Portal Developer Kit (PDK)
Implementing Event Logging in PL/SQL Portlets

Introduction

Oracle9iAS Portal provides a set of APIs for logging events that occur during transactions with its objects. These logs are stored in the database and are available through standard SQL calls and reporting tools.

You can choose the events you would like to log and organize them categorically based on user-defined domains and sub-domains. While viewing the logged events, you can view information about the event, the time the event started and stopped, the host/IP address of the remote user, the type of browser being used, and the language used when the event occurred.

For an introductory overview of Event Logging Services, please refer to the Primer on Event Logging.

Event logging services are available through the wwlog_api & wwlog_api_admin packages.

Assumptions

• You have already installed the Services Example downloaded from the PDK and understand the steps required to display a PL/SQL portlet on an Oracle Portal page.
• You have read the Primer on Event Logging document for an introduction to this service.

How to use Event Logging

The general model for working with the event logging can be described as follows:

1. Add the event object, with an appropriate domain and sub-domain combination, using the wwlog_api_admin.add_log_event method. This ensures that the LOVs and other UI components invoked when the user is monitoring the events, would show this new event in their lists.
2. Register the log event record through the UI or by using the wwlog_api_admin.add_log_registry method. The Log Registry record represents the events you want to log in the future and provides a means to filter the events that need to be logged. You can easily do this from the 'Log Registry Administration' link under the 'Administer' tab.
3. Use the wwlog_api.start_log and wwlog_api.stop_log methods to mark the events you want to log in your code. Alternatively for entering single-step event log information, just call the wwlog_api.log method to mark that event.

Implementing Event Logging

1. From the downloaded Services Example, review the services_portlet.pkb file which is the package body for this portlet. An excerpt is provided below.
2. The domain, and sub-domain definitions for your log messages are provided aliases at the start. These are used later while logging an event by domain and sub-domain. This code segment is highlighted below. You may choose your own domain and sub-domain names too instead.
3. Scroll the file to the section that contains the procedure save_prefs. The portlet provides a customizable functionality where the user can save personalized text and portlet title in the 'Edit' mode. The save_prefs saves these customizations to the database. While saving the changes, it is advisable to log the customizations the user is performing. Hence this is an ideal example of implementing the logging service. Here, a single step event is being logged using the wwlog_api.log API. This code segment is highlighted below.

   CREATE OR REPLACE package body SERVICES_PORTLET is -- Constants -- DOMAIN constant varchar2(30) := 'provider'; SUBDOMAIN constant varchar2(32) := 'services'; ... procedure save_prefs ( p_string in varchar2, p_title in varchar2, p_back_url in varchar2, p_page_url in varchar2, p_action in varchar2, p_request in varchar2, p_language in varchar2, p_reference_path in varchar2, ) is l_time varchar2(20) := to_char (sysdate,'DD.MM.YYYY-HH24:MI'); l_action varchar2(15); l_url varchar2(256) := p_back_url; l_row_count integer := 1; l_elapsed_time number := 0; l_user varchar2(30) := wwctx_api.get_user; ... begin if ((p_action = 'finish') or (p_action = 'apply')) then ... if (l_prefs.string_id is null or to_number(l_prefs.string_id) = 0) then l_action := 'insert'; ... else -- string exists in at least one language l_action := 'update'; ... end if; l_information := l_user||'%'||l_time||'%completed%'||p_title; wwlog_api.log (p_domain => DOMAIN, p_subdomain => SUBDOMAIN, p_name => l_user, p_action => l_action, p_information => l_information, p_url => l_url, p_row_count => l_row_count, p_elapsed_time=> l_elapsed_time); if p_action = 'finish' then ... end save_prefs; ... end SERVICES_PORTLET; /

4. You can also add event logs in the exception handler for other errors as shown below.

   CREATE OR REPLACE package body SERVICES_PORTLET is ... procedure save_prefs ( ... ) is ... begin ... exception when INVALID_TEXT_EXCEPTION then l_information := l_user||'%'||l_time ||'%INVALID_TEXT_EXCEPTION%'||p_string; l_action := 'failed'; wwlog_api.log (p_domain => DOMAIN, p_subdomain => SUBDOMAIN, p_name => l_user, p_action => l_action, p_information => l_information, p_url => l_url, p_row_count => 0, p_elapsed_time=> l_elapsed_time); end save_prefs; ... end SERVICES_PORTLET; /

View Portlet functionality

1. Create a page and add the Services portlet to your page. Take note to view the log messages you generated by saving the customizations. Click on the 'Customize' button and enter some text in the Portlet title text and save. Repeat this a few times.
2. Query the log messages for your domain, sub-domain and your event types to view your logs. You can do this from the 'Monitor by Event' link under the Monitor tab. Choose the event types you created ( namely insert, update or failed) and view the report for all the log records.