Oracle8i Java Stored Procedures Developer's Guide Release 2 (8.1.6) Part Number A81358-01 |
Library | Service | Contents | Index

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

    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:

- When you call it from a `SELECT` statement or a parallelized `INSERT`, `UPDATE`, or `DELETE` statement, the method cannot modify any database tables.

- When you call it from an `INSERT`, `UPDATE`, or `DELETE` statement, the method cannot query or modify any database tables modified by that statement.

- When you call it from a `SELECT`, `INSERT`, `UPDATE`, or `DELETE` statement, the method cannot execute SQL transaction control statements (such as `COMMIT`), session control statements (such as `SET` `ROLE`), or system control statements (such as `ALTER` `SYSTEM`). In addition, it cannot execute DDL statements (such as `CREATE`) because they are followed by an automatic commit.

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

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index