# 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:

- Store multiple portlets in a single Provider according to application type (e.g. HR), and not language.
- Backup all Oracle9iAS Portal application content in the database only, i.e. don't want to back up files as well.
- Access PL/SQL APIs, Framework and Services.
- Take advantage of a CORBA ORB in the database.
- Access objects in a privileged, secure schema.
- Access databases other than Oracle.
- Display content from a URL.
- Perform lots of string operations.
- etc.

## 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