How to execute a Java Stored Procedure using Database links

Date: 06-Mar-2003

After reading this how-to document you should be able to:

• Create Database link

• Execute a Java Stored Procedure using a Database link

Introduction

This document demonstrates how to execute a Java Stored Procedure using a database link. Database links are connections between two databases on the same or different machines. Database links are used to query or do DML operations on a remote database from one database, both Oracle and non Oracle database.

In this How to, we are going to create a Java Stored procedure that calculates and returns employee pay from the EMP table in the SCOTT schema in second database. We are then going to create a database link to the second database from first database. Then we are going to see how to execute the Java Stored Procedure created in the second database from the first database using database link.

Software Requirements

• Two installations of Oracle9i Database version 9.0.1 or later. You can download the Oracle9i database from Oracle Technology Network. 

• JDK1.2.x or above This can be downloaded from here .

Description

Step 1: Creating the Java Stored Procedure

Create a Java Stored Procedures that takes Employee number as input parameter and returns Employee pay. Employee pay is calculated as sum of his salary and his commission. Click here to see the code for the Java Stored Procedure.

Java Stored Procedure

public static float getPay(int empno) {
try {

// Register Oracle JDBC driver
DriverManager.registerDriver(new OracleDriver());


// Obtain default connection
Connection conn = new OracleDriver().defaultConnection();

// Create the statement
PreparedStatement stmt = conn.prepareStatement(
    "select sal, comm from emp where empno=?");
stmt.setInt(1,empno);

// Query all columns from the EMP table
ResultSet rset = stmt.executeQuery();

if (rset.next()) {
  float totalPay = rset.getFloat(1)+rset.getFloat(2);
  return totalPay; 
} else 

  return 0;
..........
}

Load the Java class into the SCOTT schema of second database using loadjava utility. loadjava is located in the bin directory of your database or client installation.

>loadjava -thin -user scott/tiger@<hostname>:<port>:<SID> -resolve -verbose EmpData.java

where,

For example,

>loadjava -thin -user scott/tiger@insn104a.idc.oracle.com:1521:otn9idb -resolve -verbose EmpData.java

Next we create the call specification for the Java Stored Procedure. To create the call spec connect to the SCOTT/TIGER user of your second database and execute the following at the SQL prompt

create or replace function getemppay(empno number) return number is
language java name 'EmpData.getPay(int) return float';
/

In the above listing, call spec getemppay publishes the Java Stored Procedure getPay().

Step 2: Creating Database link

Connect to SCOTT/TIGER user of your first database and execute the following at the SQL prompt

Select * from emp@linkdemo

SQL>variable x number
SQL>execute :x := getemppay@linkdemo(<empno>);
SQL>print x

Java application accessing getEmpPay using link linkdemo

public static void main(String[] args){ 
..........
..........
  stmt = conn.prepareCall( "begin ? := getemppay@linkdemo(?); end;" );

  // Register the return type
  stmt.registerOutParameter(1, Types.FLOAT);

  // Set the value for employee number
  stmt.setInt(2,7499);

  // Execute the Java Stored Procedure
  stmt.executeUpdate();

  // Print pay returned from Java Stored Procedure
  System.out.println("Pay : " + stmt.getFloat(1));
...........
...........

Resources

• Java Stored Procedure Samples are available at /sample_code/tech/java/jsp/index.html
• A white paper on developing Java Stored Procedure is available at /tech/java/jsp/pdf/stored_procs_in_java_twp.pdf
• More details about creating database links is available in the Oracle9i Database Administrator's Guide

Summary

This document briefly explains about how to create a Database link and how to call a Java Stored Procedure using database link.