Oracle9iAS Portal Developer Kit (PDK)
Developing Portlets Using Java Stored Procedures

Creation Date: September 26, 2001
Status: Production
Version: PDK Release 1, 2 (3.0.9, 9.0.2, and later)

Introduction

You can deploy stored procedures written in Java on an Oracle database. The Java stored procedures are executed as if they are PL/SQL stored procedures.

This article explains how to build portlets which use Java stored procedures. This technique allows developers to take advantage of both the PL/SQL and Java language and is particularly useful if you want to:

Creating a Portlet

It is important to remember that the framework language for developing the portlet is still PL/SQL. However, the logic behind the framework can be moved to Java stored procedures. The Java stored procedures are then wrapped by PL/SQL functions in the portlet body.

Hello World Sample (using Java stored procedures)

Let us consider a 'Hello World'  portlet that displays the corresponding mode name whenever a SHOW mode is selected, i.e. SHOW, SHOW_EDIT, SHOW_EDIT_DEFAULTS, SHOW_PREVIEW, SHOW_DETAILS, SHOW_HELP and SHOW_ABOUT. Each mode renders content by calling the corresponding method in a Java class residing in the database. The Java file containing methods corresponding to all the SHOW modes is shown below.

Note: Java class methods which are to be called by PL/SQL procedures/functions should always be static.

HelloWorldJava.java
 

class HelloWorldJava
{
    public static String aboutMode()
    {
        return new String("<b>MODE Show About.<br>Hello World From Java Stored Procedure.</b>");
    }
    public static String editMode()
    {
        return new String("<b>MODE Show Edit.<br>Hello World from Java Stored Procedure.</b>");
    }
    public static String helpMode()
    {
        return new String("<b>MODE Show Help.<br>Hello World From Java Stored Procedure.</b>");
    }
    public static String editDefaultsMode()
    {
        return new String("<b>MODE Show Edit Defaults.<br>Hello World From Java Stored Procedure.</b>");
    }
    public static String showMode()
    {
        return new String("<b>MODE Show.<br>Hello World From Java Stored Procedure.</b>");
    }
    public static String detailsMode()
    {
        return new String("<b>MODE Show Details.<br>Hello World From Java Stored Procedure.</b>");
    }
    public static String previewMode()
    {
        return new String("<b>MODE Show Preview.<br>Hello World From Java Stored Procedure.</b>");
    }
}

  1. Compile the Java class file HelloWorldJava.java.

  2. Ensure that the ORACLE_HOME environment variable is set.

  3. Deploy the compiled class file HelloWorldJava.class in the database.

From the directory where HelloWorldJava.class resides, execute the command outlined below:

C:\%ORACLE_HOME%\bin\loadjava  -v -resolve -user %provider_schemaa%/%provider_password%@%connect_string% HelloWorldJava.class

  1. Once the class file is deployed, you can call methods inside the Java class.

    For example:

    function get_preview_mode_content
        return varchar2
        as
        language java name 'HelloWorldJava.previewMode() return oracle.sql.CHAR';

The PL/SQL procedures/functions that invoke methods in the Java class are called from the PL/SQL show() procedure (see below). When they call their corresponding Java methods, they receive the java.lang.String object as oracle.sql.CHAR. This in turn is passed to the show() procedure.

procedure show
(
    p_portlet_record        wwpro_api_provider.portlet_runtime_record
)
is
...
...
    l_edit varchar2(1000);
    l_help varchar2(1000);
    l_about varchar2(1000);
    l_edit_defaults varchar2(1000);
    l_show varchar2(1000);
    l_details varchar2(1000);
    l_preview varchar2(1000);
begin
...
...
        if (p_portlet_record.exec_mode = wwpro_api_provider.MODE_SHOW) then
            if (p_portlet_record.has_title_region) then
                wwui_api_portlet.draw_portlet_header
                (
                     p_provider_id       => p_portlet_record.provider_id
                    ,p_portlet_id        => p_portlet_record.portlet_id
                    ,p_title             => l_portlet.title
                    ,p_has_details       => true
                    ,p_has_edit          => true
                    ,p_has_help          => true
                    ,p_has_about         => true
                    ,p_referencepath     => p_portlet_record.reference_path
                    ,p_back_url          => p_portlet_record.page_url
                );
            end if;
            wwui_api_portlet.open_portlet(p_portlet_record.has_border);
      /*
           Calls get_show_mode_content function - display the content of the portlet in the show mode.
             */
            l_show := get_show_mode_content();
            htp.p(l_show);

            if (p_portlet_record.has_border) then
                wwui_api_portlet.close_portlet;
            end if;
        elsif (p_portlet_record.exec_mode = wwpro_api_provider.MODE_SHOW_ABOUT) then
      /*
            Calls get_about_mode_content function - display the about page for the portlet.
            */
            l_about := get_about_mode_content();
            htp.p(l_about);
            ...
           ...
/*
Code for other modes goes here. 
*/
...
...
    end show;

/*
    Functions that call the Java Methods
*/

    function get_show_mode_content
    return varchar2
    as
    language java name 'HelloWorldJava.showMode() return oracle.sql.CHAR';

    function get_about_mode_content
    return varchar2
    as
    language java name 'HelloWorldJava.aboutMode() return oracle.sql.CHAR';
    ...
    ...
/*
    Calls made here for other modes
*/

 

The functions that call the Java methods should be declared in the package specification (.pks) file. Here, the show() functionality is transferred from the PL/SQL procedure to the Java class. This can be extended to any procedure inside a PL/SQL package.

This example is a primer describing how to use Java code while developing PL/SQL portlets. You can use a similar approach when creating other PL/SQL portlets that utilize Java code.

 
Revision History:
Revision No Last Update
1.0 July 30, 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