Home arrow Java arrow Page 4 - Java Stored Procedure in Oracle, Database Interaction
JAVA

Java Stored Procedure in Oracle, Database Interaction


In this article, we will examine working with Java Stored Procedures in Oracle database.

Author Info:
By: Jagadish Chaterjee
Rating: 4 stars4 stars4 stars4 stars4 stars / 34
November 01, 2005
TABLE OF CONTENTS:
  1. · Java Stored Procedure in Oracle, Database Interaction
  2. · A Simple Java Class
  3. · Getting Java Classes into Oracle Database
  4. · How to make Classes available to Oracle
  5. · How to execute the Java Stored Procedures

print this article
SEARCH DEVARTICLES

Java Stored Procedure in Oracle, Database Interaction - How to make Classes available to Oracle
(Page 4 of 5 )

Making Java classes available to Oracle can also be called “publishing Java classes.”  Any class that will be directly called from SQL or PL/SQL must be published. Now we shall proceed with publishing. 

A Java class is published by creating and compiling a call specification for it. The call specification is often referred to as a call spec or even a PL/SQL wrapper. It maps a Java method's parameters and return type to Oracle SQL types. Here's the call specification for the “addEmp” method:

CREATE OR REPLACE PROCEDURE addEmp (empno NUMBER,ename VARCHAR2,
  sal NUMBER, deptno NUMBER)
  AS LANGUAGE JAVA
  NAME 'Employee.addEmp(int, java.lang.String,
                          float, int)';
/

The “addEmp” procedure provides an SQL interface to the Java “Employee.addEmp method”. The Java method must be fully specified, including package name if relevant. Also, when developing a call specification, Java objects such as String must be fully qualified.

One should make sure that Java methods with “no return” values are wrapped as procedures, and those with return values are wrapped as functions. Consider a second Java method in “Employee” that obtains a count of employees for a given department:

//Get the total number of employees for a given department.
public static int getEmpCountByDept(int deptno) {

   Connection conn =
      DriverManager.getConnection("jdbc:default:connection:");

   String sql = "SELECT COUNT(1) FROM emp WHERE deptno = ?";
   int c = 0; //containing the count
 .
 .
 .
 
   return c;
}

Its call specification specifies that it returns a NUMBER.

CREATE OR REPLACE FUNCTION getEmpCountByDept (deptno NUMBER)
   RETURN NUMBER AS LANGUAGE JAVA
   NAME 'Employee.getEmpCountByDept(int) return int';
/

Once executed, call specifications join the other files in the database as members of the SCOTT schema.


blog comments powered by Disqus
JAVA ARTICLES

- Java Too Insecure, Says Microsoft Researcher
- Google Beats Oracle in Java Ruling
- Deploying Multiple Java Applets as One
- Deploying Java Applets
- Understanding Deployment Frameworks
- Database Programming in Java Using JDBC
- Extension Interfaces and SAX
- Entities, Handlers and SAX
- Advanced SAX
- Conversions and Java Print Streams
- Formatters and Java Print Streams
- Java Print Streams
- Wildcards, Arrays, and Generics in Java
- Wildcards and Generic Methods in Java
- Finishing the Project: Java Web Development ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials