Skip Headers

Oracle® Database Java Developer's Guide
10g Release 1 (10.1)

Part Number B12021-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

7 Calling Stored Procedures

After you load and publish a Java stored procedure, you can call it. This chapter demonstrates how to call Java stored procedures in various contexts. You learn how to call them from the top level and from database triggers, SQL DML statements, and PL/SQL blocks. You also learn how SQL exceptions are handled.

7.1 Calling Java from the Top Level

The SQL CALL statement lets you call Java methods published at the top level, in PL/SQL packages, or in SQL object types. In SQL*Plus, you can execute the CALL statement interactively using the syntax:

CALL [schema_name.][{package_name | object_type_name}][@dblink_name]

{  procedure_name ([param[, param]...])

 | function_name ([param[, param]...]) INTO :host_variable};

where param stands for the following syntax:

{literal | :host_variable}

Host variables (that is, variables declared in a host environment) must be prefixed with a colon. The following examples show that a host variable cannot appear twice in the same CALL statement, and that a parameterless subprogram must be called with an empty parameter list:

CALL swap(:x, :x);  -- illegal, duplicate host variables

CALL balance() INTO :current_balance;  -- () required

7.1.1 Redirecting Output

On the server, the default output device is a trace file, not the user screen. As a result, System.out and System.err print to the current trace files. To redirect output to the SQL*Plus text buffer, call the procedure set_output() in package DBMS_JAVA, as follows:

SQL> SET SERVEROUTPUT ON

SQL> CALL dbms_java.set_output(2000);

The minimum (and default) buffer size is 2,000 bytes; the maximum size is 1,000,000 bytes. In the following example, the buffer size is increased to 5,000 bytes:

SQL> SET SERVEROUTPUT ON SIZE 5000

SQL> CALL dbms_java.set_output(5000);

Output is printed when the stored procedure exits.

For more information about SQL*Plus, see the SQL*Plus User's Guide and Reference.

Example 1

In the following example, the method main accepts the name of a database table (such as 'emp') and an optional WHERE clause condition (such as 'sal > 1500'). If you omit the condition, the method deletes all rows from the table. Otherwise, the method deletes only those rows that meet the condition.

import java.sql.*;

import oracle.jdbc.*;

 

public class Deleter {

  public static void main (String[] args) throws SQLException { 

    Connection conn =

      DriverManager.getConnection("jdbc:default:connection:");

    String sql = "DELETE FROM " + args[0];

    if (args.length > 1) sql += " WHERE " + args[1];

    try {

      Statement stmt = conn.createStatement();

      stmt.executeUpdate(sql);

      stmt.close();

    } catch (SQLException e) {System.err.println(e.getMessage());}

  }

}

The method main can take either one or two arguments. Normally, the DEFAULT clause is used to vary the number of arguments passed to a PL/SQL subprogram. However, that clause is not allowed in a call spec. So, you must overload two packaged procedures (you cannot overload top-level procedures), as follows:

CREATE OR REPLACE PACKAGE pkg AS

  PROCEDURE delete_rows (table_name VARCHAR2);

  PROCEDURE delete_rows (table_name VARCHAR2, condition VARCHAR2);

END;


CREATE OR REPLACE PACKAGE BODY pkg AS

  PROCEDURE delete_rows (table_name VARCHAR2)

  AS LANGUAGE JAVA

  NAME 'Deleter.main(java.lang.String[])';


  PROCEDURE delete_rows (table_name VARCHAR2, condition VARCHAR2) 

  AS LANGUAGE JAVA

  NAME 'Deleter.main(java.lang.String[])';

END;

Now, you are ready to call the procedure delete_rows:

SQL> CALL pkg.delete_rows('emp', 'sal > 1500');


Call completed.


SQL> SELECT ename, sal FROM emp;


ENAME          SAL

--------- --------

SMITH          800

WARD          1250

MARTIN        1250

TURNER        1500

ADAMS         1100

JAMES          950

MILLER        1300


7 rows selected.

Example 2

Assume that the executable for the following Java class is stored in the Oracle database:

public class Fibonacci {

  public static int fib (int n) {

    if (n == 1 || n == 2)

      return 1;

    else 

      return fib(n - 1) + fib(n - 2);

  }

}

The class Fibonacci has one method named fib, which returns the nth Fibonacci number. The Fibonacci sequence (1, 1, 2, 3, 5, 8, 13, 21, . . .), which was first used to model the growth of a rabbit colony, is recursive. Each term in the sequence (after the second) is the sum of the two terms that immediately precede it. Because the method fib returns a value, you publish it as a function:

CREATE OR REPLACE FUNCTION fib (n NUMBER) RETURN NUMBER

AS LANGUAGE JAVA

NAME 'Fibonacci.fib(int) return int';

Next, you declare two SQL*Plus host variables, then initialize the first one:

SQL> VARIABLE n NUMBER

SQL> VARIABLE f NUMBER

SQL> EXECUTE :n := 7;


PL/SQL procedure successfully completed.

Finally, you are ready to call the function fib. Remember, in a CALL statement, host variables must be prefixed with a colon.

SQL> CALL fib(:n) INTO :f;


Call completed.


SQL> PRINT f


         F

----------

        13

7.2 Calling Java from Database Triggers

A database trigger is a stored program associated with a specific table or view. Oracle executes (fires) the trigger automatically whenever a given DML operation affects the table or view.

A trigger has three parts: a triggering event (DML operation), an optional trigger constraint, and a trigger action. When the event occurs, the trigger fires and either a PL/SQL block or a CALL statement performs the action. A statement trigger fires once, before or after the triggering event. A row trigger fires once for each row affected by the triggering event.

Within a database trigger, you can reference the new and old values of changing rows using the correlation names new and old. In the trigger-action block or CALL statement, column names must be prefixed with :new or :old.

To create a database trigger, you use the SQL CREATE TRIGGER statement. For the syntax of that statement, see theOracle Database SQL Reference. For a full discussion of database triggers, see the Oracle Database Application Developer's Guide - Fundamentals.

Example 1

Suppose you want to create a database trigger that uses the following Java class to log out-of-range salary increases:

import java.sql.*;

import java.io.*;

import oracle.jdbc.*;


public class DBTrigger {

  public static void logSal (int empID, float oldSal, float newSal)

  throws SQLException {

    Connection conn =

      DriverManager.getConnection("jdbc:default:connection:");

    String sql = "INSERT INTO sal_audit VALUES (?, ?, ?)";

    try {

      PreparedStatement pstmt = conn.prepareStatement(sql);

      pstmt.setInt(1, empID);

      pstmt.setFloat(2, oldSal);

      pstmt.setFloat(3, newSal);

      pstmt.executeUpdate(); 

      pstmt.close();

    } catch (SQLException e) {System.err.println(e.getMessage());}

  }

}

The class DBTrigger has one method, which inserts a row into the database table sal_audit. Because logSal is a void method, you publish it as a procedure:

CREATE OR REPLACE PROCEDURE log_sal (

  emp_id NUMBER, old_sal NUMBER, new_sal NUMBER)

AS LANGUAGE JAVA

NAME 'DBTrigger.logSal(int, float, float)';

Next, you create the database table sal_audit, as follows:

CREATE TABLE sal_audit (

  empno  NUMBER, 

  oldsal NUMBER, 

  newsal NUMBER);

Finally, you create the database trigger, which fires when a salary increase exceeds twenty percent:

CREATE OR REPLACE TRIGGER sal_trig

AFTER UPDATE OF sal ON emp

FOR EACH ROW

WHEN (new.sal > 1.2 * old.sal)

CALL log_sal(:new.empno, :old.sal, :new.sal);

When you execute the following UPDATE statement, it updates all rows in the table emp. For each row that meets the trigger's WHEN clause condition, the trigger fires and the Java method inserts a row into the table sal_audit.

SQL> UPDATE emp SET sal = sal + 300;


SQL> SELECT * FROM sal_audit;


     EMPNO     OLDSAL     NEWSAL

---------- ---------- ----------

      7369        800       1100

      7521       1250       1550

      7654       1250       1550

      7876       1100       1400

      7900        950       1250

      7934       1300       1600


6 rows selected.

Example 2

Suppose you want to create a trigger that inserts rows into a database view defined as follows:

CREATE VIEW emps AS

  SELECT empno, ename, 'Sales' AS dname FROM sales

  UNION ALL

  SELECT empno, ename, 'Marketing' AS dname FROM mktg;

where the database tables sales and mktg are defined as:

CREATE TABLE sales (empno NUMBER(4), ename VARCHAR2(10));

CREATE TABLE mktg (empno NUMBER(4), ename VARCHAR2(10));

You must write an INSTEAD OF trigger because rows cannot be inserted into a view that uses set operators such as UNION ALL. Instead, your trigger will insert rows into the base tables.

First, you add the following Java method to the class DBTrigger (defined in the previous example):

public static void addEmp (

  int empNo, String empName, String deptName)

throws SQLException {

  Connection conn =

    DriverManager.getConnection("jdbc:default:connection:");

  String tabName = (deptName.equals("Sales") ? "sales" : "mktg");

  String sql = "INSERT INTO " + tabName + " VALUES (?, ?)";

  try {

    PreparedStatement pstmt = conn.prepareStatement(sql);

    pstmt.setInt(1, empNo);

    pstmt.setString(2, empName);

    pstmt.executeUpdate(); 

    pstmt.close();

  } catch (SQLException e) {System.err.println(e.getMessage());}

}

The method addEmp inserts a row into the table sales or mktg depending on the value of the parameter deptName. You write the call spec for this method as follows:

CREATE OR REPLACE PROCEDURE add_emp (

  emp_no NUMBER, emp_name VARCHAR2, dept_name VARCHAR2)

AS LANGUAGE JAVA 

NAME 'DBTrigger.addEmp(int, java.lang.String, java.lang.String)';

Then, you create the INSTEAD OF trigger:

CREATE OR REPLACE TRIGGER emps_trig 

INSTEAD OF INSERT ON emps

FOR EACH ROW

CALL add_emp(:new.empno, :new.ename, :new.dname);

When you execute each of the following INSERT statements, the trigger fires and the Java method inserts a row into the appropriate base table:

SQL> INSERT INTO emps VALUES (8001, 'Chand', 'Sales');

SQL> INSERT INTO emps VALUES (8002, 'Van Horn', 'Sales');

SQL> INSERT INTO emps VALUES (8003, 'Waters', 'Sales');

SQL> INSERT INTO emps VALUES (8004, 'Bellock', 'Marketing');

SQL> INSERT INTO emps VALUES (8005, 'Perez', 'Marketing');

SQL> INSERT INTO emps VALUES (8006, 'Foucault', 'Marketing');


SQL> SELECT * FROM sales;


     EMPNO ENAME

---------- ----------

      8001 Chand

      8002 Van Horn

      8003 Waters


SQL> SELECT * FROM mktg;


     EMPNO ENAME

---------- ----------

      8004 Bellock

      8005 Perez

      8006 Foucault


SQL> SELECT * FROM emps;


     EMPNO ENAME      DNAME

---------- ---------- ---------

      8001 Chand      Sales

      8002 Van Horn   Sales

      8003 Waters     Sales

      8004 Bellock    Marketing

      8005 Perez      Marketing

      8006 Foucault   Marketing

7.3 Calling Java from SQL DML

If you publish Java methods as functions, you can call them from SQL SELECT, INSERT, UPDATE, DELETE, CALL, EXPLAIN PLAN, LOCK TABLE, and MERGE 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

7.3.1 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).

7.4 Calling Java from PL/SQL

You can call Java stored procedures from any PL/SQL block, subprogram, or package. For example, assume that the executable for the following Java class is stored in the Oracle database:

import java.sql.*;

import oracle.jdbc.*;


public class Adjuster {

  public static void raiseSalary (int empNo, float percent)

  throws SQLException { 

    Connection conn =

      DriverManager.getConnection("jdbc:default:connection:");

    String sql = "UPDATE emp SET sal = sal * ? WHERE empno = ?";

    try {

      PreparedStatement pstmt = conn.prepareStatement(sql);

      pstmt.setFloat(1, (1 + percent / 100));

      pstmt.setInt(2, empNo);

      pstmt.executeUpdate();

      pstmt.close();

    } catch (SQLException e) {System.err.println(e.getMessage());}

  }

}

The class Adjuster has one method, which raises the salary of an employee by a given percentage. Because raiseSalary is a void method, you publish it as a procedure, as follows:

CREATE OR REPLACE PROCEDURE raise_salary (empno NUMBER, pct NUMBER)

AS LANGUAGE JAVA

NAME 'Adjuster.raiseSalary(int, float)';

In the following example, you call the procedure raise_salary from an anonymous PL/SQL block:

DECLARE

   emp_id  NUMBER;

   percent NUMBER;

BEGIN

   -- get values for emp_id and percent

   raise_salary(emp_id, percent);

   ...

END;

In the next example, you call the function row_count (defined in Writing Top-Level Call Specs) from a standalone PL/SQL stored procedure:

CREATE PROCEDURE calc_bonus (emp_id NUMBER, bonus OUT NUMBER) AS

  emp_count NUMBER;

  ...

BEGIN

  emp_count := row_count('emp');

  ...

END;

In the final example, you call the raise_sal method of object type Employee (defined in "Implementing Object Type Methods") from an anonymous PL/SQL block:

DECLARE

  emp_id NUMBER(4);

  v emp_type;

BEGIN

  -- assign a value to emp_id

  SELECT VALUE(e) INTO v FROM emps e WHERE empno = emp_id;

  v.raise_sal(500);

  UPDATE emps e SET e = v WHERE empno = emp_id;

  ...

END;

7.5 Calling PL/SQL from Java

JDBC and SQLJallows you to call PL/SQL stored functions and procedures. For example, suppose you want to call the following stored function, which returns the balance of a specified bank account:

FUNCTION balance (acct_id NUMBER) RETURN NUMBER IS

  acct_bal NUMBER;

BEGIN

  SELECT bal INTO acct_bal FROM accts

    WHERE acct_no = acct_id;

  RETURN acct_bal;

END;

From a JDBC program, your call to the function balance might look like this:

CallableStatement cstmt = conn.prepareCall("{? = CALL balance(?)}");

cstmt.registerOutParameter(1, Types.FLOAT);

cstmt.setInt(2, acctNo);

cstmt.executeUpdate();

float acctBal = cstmt.getFloat(1);

7.6 How OracleJVM Handles Exceptions

Java exceptions are objects, so they have classes as their types. As with other Java classes, exception classes have a naming and inheritance hierarchy. Therefore, you can substitute a subexception (subclass) for its superexception (superclass).

All Java exception objects support the method toString(), which returns the fully qualified name of the exception class concatenated to an optional string. Typically, the string contains data-dependent information about the exceptional condition. Usually, the code that constructs the exception associates the string with it.

When a Java stored procedure executes a SQL statement, any exception thrown is materialized to the procedure as a subclass of java.sql.SQLException. That class has the methods getErrorCode() and getMessage(), which return the Oracle error code and message, respectively.

If a stored procedure called from SQL or PL/SQL throws an exception not caught by Java, the caller gets the following error message:

ORA-29532 Java call terminated by uncaught Java exception

This is how all uncaught exceptions (including non-SQL exceptions) are reported.