Oracle8i Java Stored Procedures Developer's Guide
Release 8.1.5

A64686-01

Library

Product

Contents

Index

Prev  Chap Top Next

Calling Java from SQL DML

If you publish Java methods as functions, you can call them from SQL SELECT, INSERT, UPDATE, and DELETE statements. For example, assume that the executable for the following Java class is stored in the RDBMS:

public class Formatter {
  public static String formatEmp (String empName, String jobTitle) {
    empName = empName.substring(0,1).toUpperCase() + 
      empName.substring(1).toLowerCase();
    jobTitle = jobTitle.toLowerCase();
    if (jobTitle.equals("analyst"))
      return (new String(empName + " is an exempt analyst"));
    else
      return (new String(empName + " is a non-exempt " + jobTitle));
  }
}

The class Formatter has one method named formatEmp, which returns a formatted string containing a staffer's name and job status. First, you write the call spec for this method as follows:

CREATE OR REPLACE FUNCTION format_emp (ename VARCHAR2, job VARCHAR2)
  RETURN VARCHAR2 
AS LANGUAGE JAVA
NAME 'Formatter.formatEmp (java.lang.String, java.lang.String)
  return java.lang.String';

Then, you call the function format_emp to format a list of employees:

SQL> SELECT format_emp(ename, job) AS "Employees" FROM emp
  2    WHERE job NOT IN ('MANAGER', 'PRESIDENT') ORDER BY ename;

Employees
--------------------------------------------
Adams is a non-exempt clerk
Allen is a non-exempt salesman
Ford is an exempt analyst
James is a non-exempt clerk
Martin is a non-exempt salesman
Miller is a non-exempt clerk
Scott is an exempt analyst
Smith is a non-exempt clerk
Turner is a non-exempt salesman
Ward is a non-exempt salesman

Restrictions

To be callable from SQL DML statements, a Java method must obey the following "purity" rules, which are meant to control side effects:

If any SQL statement inside the method violates a rule, you get an error at run time (when the statement is parsed).




Prev

Top

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index