Oracle WebServer provides a set of Java classes to enable you to access the database and to generate HTML dynamically using the Java Interpreter. These classes are called the Java Web Toolkit. Using these Java classes, you can make PL/SQL calls from within your Java application, effectively combining the strengths of the two languages. This toolkit also includes a type wrapper for Java applets, to make it easy for you to store applets in the database, retrieve them through the Java Interpreter, and send them to the client for execution embedded in a Web page. You can also store and retrieve Java applets for execution on the client using the PL/SQL Agent by simply treating the applets as data.
Generally speaking, you should use Java to handle multimedia operations and to interface to objects on the net, and you should use PL/SQL for interfacing to the database. Whether this means using the PL/SQL Agent or the Java Interpreter will largely be determined by the following:
pl2java [flags] username/password[@connect-string] packagename...
There are certain PL/SQL datatypes that the pl2java utility cannot encapsulate. These are shown below, along with the recommended substitutes, if any:
You should try to logoff from the database explicitly when the session is no longer needed. Although Java garbage collection disconnects the session when the Session object is exited, this does not necessarily happen right away. Java does not perform garbage collection until resources are low or the program idles. Therefore, it is better to disconnect the session to free up the database connection resource immediately.
Here is the SQL and PL/SQL source code that defines the objects used by this example.
CREATE TABLE EMP (
emp_name VARCHAR2(30) NOT NULL,
emp_number NUMBER(10),
emp_dept VARCHAR2(30) NOT NULL
);
CREATE OR REPLACE package Employee as
type string_table is table of varchar2(30) index by binary_integer;
type number_table is table of number(10) index by binary_integer;
function count_employees(
dept_name in varchar2
) return number;
procedure list_employees(
dept_name in varchar2,
employee_name out string_table,
employee_no out number_table
);
end;
CREATE OR REPLACE package body Employee as
function count_employees(
dept_name in varchar2
) return number as
employee_count number;
begin
select count(*)
into employee_count
from EMP
where EMP_DEPT = dept_name;
return employee_count;
end;
procedure list_employees(
dept_name in varchar2,
employee_name out string_table,
employee_no out number_table
) as
i number;
cursor employee_rec(dept_name varchar2) is
select EMP_NAME, EMP_NUMBER
from EMP
where EMP_DEPT = dept_name;
begin
i := 1;
for employee in employee_rec(dept_name) loop
employee_name(i) := employee.EMP_NAME;
employee_no(i) := employee.EMP_NUMBER;
i := i + 1;
end loop;
end;
end;
For reference, the commands themselves are considered SQL, but the code inside the packages is PL/SQL. Since PL/SQL is a superset of SQL, you can think of it all as PL/SQL.
This example uses the oracle.html package to generate dynamic HTML. That package is documented under Dynamic HTML from Java.
The main program
In this example, the program is called EmployeeReport. This program will be saved in a file called EmployeeReport.java.import oracle.html.*;
public class EmployeeReport { public static void main (String args[]) { HtmlHead hd = new HtmlHead("Employee Listing");
HtmlBody bd = new HtmlBody();
HtmlPage hp = new HtmlPage(hd, bd);
hp.printHeader();
hp.print(); }
}Generating the Java Wrapper Class for the Employee Package
To access the Employee package, you must generate a Java wrapper class for it. This encapsulates the package as a Java class. The functions and procedures in the package will appear as methods in the wrapper class under the same names.pl2java scott/tiger@HR_DB Employee
public class Employee { public Employee(Session session) { ... } public PDouble count_employees(PStringBuffer dept_name)
throws ServerException { ... } public void list_employees(PStringBuffer dept_name,
PStringBuffer employee_name[],
PDouble employee_number[])
throws ServerException { ... }
}
<function name>_<overload number>_return_length
The overload number is the number of other functions that exist with the same name as this one. To find out about overloading of functions in PL/SQL, see Overloading Subprograms. For more information on overload numbers specifically, see the PL/SQL User's Guide and Reference. You can find out what the overload number of a function is by using the Oracle7 Server standard package dbms_describe, as covered in the Oracle7 Server Administrator's Guide. For non-overloaded functions, the overload number is 0.
For example, assume the following PL/SQL package:
CREATE OR REPLACE package Employee as
function employee_name (
employee_number in number
) return varchar2;
END; public class EmployeeReport {
public static void main(String args[]) {
Session session = new Session("scott", "tiger", "HR_DB");
Employee employee = new Employee(session);
PDouble pEmployeeNumber = new PDouble((double)12345);
// Set VARCHAR2 return length for function employee_name to 50 employee.employee_name_0_return_length = 50;
System.out.println("Employee ID: " + pEmployeeNumber +
" name: " + employee.employee_name(pEmployeeNumber)); }
} <function name>_<overload number>_return_arraylength
Making a connection to the database
The first thing that you have to do in the EmployeeReport program is to connect to a database. You do this by using the following code to create a Session object:import oracle.html.*;
import oracle.rdbms.*; // ADD: import Oracle classes which deal //with databasepublic class EmployeeReport { public static void main (String args[]) { HtmlHead hd = new HtmlHead("Employee Listing");
HtmlBody bd = new HtmlBody();
HtmlPage hp = new HtmlPage(hd, bd);
hp.printHeader();
// ADD: defines Oracle session properties like ORACLE_HOME
Session.setProperty("ORACLE_HOME", "/user/oracle");
Session.setProperty("TNS_ADMIN", "/user/oracle/network/admin"); // ADD: creates a database session and logon
Session session;
try {
session = new Session("scott", "tiger", "HR_DB");
} catch (ServerException e) {
bd.addItem(new SimpleItem("Logon fails: " + e.getSqlerrm()));
hp.print();
return;
}
hp.print()
}
}Invoking the Employee package
To invoke the Employee package, you need to create a new instance of the corresponding wrapper class. Then you can call the procedures and functions in the package by invoking the methods in the wrapper class. Add the following code to the program:import oracle.html.*;
import oracle.rdbms.*;
import oracle.plsql.*; // ADD: import Oracle classes which deal //with PL/SQL data typespublic class EmployeeReport { public static void main (String args[]) { HtmlHead hd = new HtmlHead("Employee Listing");
HtmlBody bd = new HtmlBody();
HtmlPage hp = new HtmlPage(hd, bd);
hp.printHeader(); Session.setProperty("ORACLE_HOME", "/user/oracle");
Session.setProperty("TNS_ADMIN", "/user/oracle/network/admin"); Session session;
try {
session = new Session("scott", "tiger", "HR_DB");
} catch (ServerException e) {
bd.addItem(new SimpleItem("Logon fails: " + e.getSqlerrm()));
hp.print();
return;
} // ADD: create a new instance of Employee package
Employee employee = new Employee(session); // ADD: find the department name from the input parameter
String deptName = null;
if ((args.length < 1) || !args[0].startsWith("DEPT=")) {
bd.addItem(new SimpleItem("No department name given"));
hp.print();
return;
} else {
deptName = args[0].substring(5);
} // ADD: create objects to encapsulate PL/SQL values that are
// used as parameters
PStringBuffer pDeptName = new PStringBuffer(30, deptName);
PStringBuffer pEmployeeName[];
PDouble pEmployeeNumber[];
PDouble pEmployeeCount; // ADD: print report header
bd.addItem("Department " + pDeptName + ":")
.addItem(SimpleItem.Paragraph); // ADD: call Employee package to count the number of employees in
// the department
try {
pEmployeeCount = employee.count_employees(pDeptName);
} catch (ServerException e) {
bd.addItem("Fail to retrieve employee information for department " +
deptName + ": " + e.getSqlerrm());
hp.print();
return;
}
int employeeCount = (int)pEmployeeCount.doubleValue();
if (employeeCount == 0) {
bd.addItem("No employee found under department " + deptName);
hp.print();
return;
} // ADD: allocate the arrays for employee names and numbers
pEmployeeName = new PStringBuffer[employeeCount];
pEmployeeNumber = new PDouble[employeeCount]; // ADD: allocate the buffers to retrieve employee information
for(int i = 0; i < employeeCount; i++) {
// max length of employee name is 30 (characters)
pEmployeeName[i] = new PStringBuffer(30);
pEmployeeNumber[i] = new PDouble();
} // ADD: call Employee package to look up employees in the dept
try {
employee.list_employees(pDeptName, pEmployeeName, pEmployeeNumber);
} catch (ServerException e) {
bd.addItem("Fail to retrieve employee information for department " +
deptName + ": " + e.getSqlerrm());
hp.print();
return;
} // ADD: generate report
DynamicTable tab = new DynamicTable(2);
TableRow row = new TableRow();
row.addCell(new TableHeaderCell("Employee Name"))
.addCell(new TableHeaderCell("Employee Number"));
tab.addRow(row);
for (int i = 0; i < employeeCount; i++) {
row = new TableRow();
if (pEmployeeNumber[i].isNull())
row.addCell(new TableDataCell(pEmployeeName[i].toString()))
.addCell(new TableDataCell("new employee"));
else
row.addCell(new TableDataCell(pEmployeeName[i].toString()))
.addCell(new TableDataCell(pEmployeeNumber[i].toString()));
tab.addRow(row);
}
hp.addItem(tab);
hp.print(); // ADD: logoff from database
try {
session.logoff();
} catch (ServerException e);
}
}
The oracle.html package provides a standard set of classes based on HTML2, HTML3, and popular browser-specific extensions. You are not limited to these, however. You can easily create your own customizable HTML classes by deriving them from the CompoundItem or Container classes. The oracle.html package also has the intelligence to generate output that is optimized for the browser at hand. For example, a browser that does not support tables will get table data in the form of preformatted strings.
In some cases, interfaces have been used to specify the attributes of HTML tags. This was done to simplify cases where tag assignments can be complex or where similar arguments are used by several types of tags.
If you have a body of HTML you want to use repeatedly, you can encapsulate it in an object of class Compounditem and thereafter treat it as a single HTMLitem.
The HTML tags that you can dynamically generate using the supplied objects are listed below:
As you can see, the Java objects that generate the main structural HTML tags begin with HTML; others are chiefly named for the tags they generate.
The following is a basic Java program that produces an HTML page whose title and content are both the famous "Hello World!":
import oracle.html.*;
public class HelloWorld {
public static void main (String args[]) {
// Create an HtmlHead Object titled "Hello World!"
HtmlHead hd = new HtmlHead("Hello World!");
// Create an HtmlBody Object
HtmlBody bd = new HtmlBody();
// Create an HtmlPage Object
HtmlPage hp = new HtmlPage(hd, bd);
// Adds a simple string "Hello World" in this page
bd.addItem("Hello World!");
// Print out the content of this Page
hp.print();
}
}
The following Java code creates an HTML anchor:
// Creates an anchor
Anchor anchor = new Anchor("expire_date", new SimpleItem("Expire Date: 02/96"));
The following Java code creates an HTML form:
// Create a form object
Form form = new Form("GET", "http://www.myhom.com/wrb/doit");
// Create a TextField object and add it to the form
form.addItem(new TextField("textfield"));
// Add the form object to the HtmlBody object
bd.addItem(form);
The following Java code creates an HTML table. To make this example realistic, we have added some user-defined functions:
// Some user-defined functions
Product product = getFirstProduct();
// create a dynamic table with 2 columns
DynamicTable tab = new DynamicTable(2);
// create the rows and add them to the table
TableRow rows[] = new TableRow[NUM_ROWS];
for (int i=0; i< NUM_ROWS; i++) {
// allocate TableRow
rows[i] = new TableRow();
// populate row with data
rows[i].addCell(new TableHeaderCell(product.getProductID()))
.addCell(new TableDataCell(product.getProductDescription()))
// add them to Table
tab.addRow(rows[i]);
}
The following Java code creates an HTML menu:
// Create a MenuList Object
MenuList menulist = new MenuList();
// Add new items to the list
menulist.addItem(new SimpleItem("Menu Item 1"))
.addItem(new SimpleItem("Menu Item 2"));
// Add the list object to the body
bd.addItem(menulist);
The following Java code creates an HTML definition list, encapsulated in a Container object:
// Creates a new Container Object
Container dterms = new Container();
dterms.addItem(new SimpleItem("DefTerm1.1"));
dterms.addItem(new SimpleItem("DefTerm1.2"));
DefinitionList dl = new DefinitionList();
// Creates a new Definition List Object, note the first argument
dl.addDef(dterms, new SimpleItem("Definition1"));
The following Java code creates an HTML ordered list:
// Create a OrderedList Object
OrderedList orderedlist = new OrderedList();
// Add new items to the list
orderedlist.addItem(new SimpleItem("Ordered Item 1"))
.addItem(new SimpleItem("Ordered Item 2"));
// Add the list object to the body (assuming it already exists)
bd.addItem(orderedlist);
The following Java code encapsulates a group of HTML tags as a single component that can be used repeatedly in a page:
// Create a new CompoundItem object
CompoundItem compoundItem = new CompoundItem();
// Set the default text attribute of all items in Compound Item
// Note that this operation cannot be done with a <b>Container</b>
compoundItem.setItal();
// Add a SimpleItem and a TextArea to the CompoundItem
compoundItem.addItem(new Simpleitem("How are you?").setBold())
.addItem(new TextArea("response", 30, 10));
// Add the CompoundItem to the body object (assuming it's been created)
bd.addItem(compoundItem);
The following Java code specifies an applet of Java bytecode to be included in the Web page output for execution on the client's browser.
// Create a new Applet object with the following attributes:
// Applet file name: "NervousText.class"
// Width of Applet Window: 400
// Height of Applet Window: 75
// Parameter: Name="text", Value="This is an applet test"
Applet applet = new Applet("NervousText.class", 400, 75);
applet.addParam("text", "This is an applet test.");
Go to the top of the section.
To report any problems or comments, e-mail Oracle WebServer Documentation.