Home arrow Java arrow Page 5 - 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 execute the Java Stored Procedures
(Page 5 of 5 )

We have developed, loaded, and published our Java classes. The final step is to execute them.  The following statements would make you see the Java based output.

SQL> SET SERVEROUTPUT ON
SQL> CALL dbms_java.set_output(2000);
SQL> EXECUTE add_emp(1,'Jag',40000.00,1);
Creating new employee...

PL/SQL procedure successfully completed.

The first two statements are necessary one time (after the first login), because it allows the redirection of tracing.

As you can see, from the caller's perspective, there is no difference between calls made to Java stored procedures and calls to a PL/SQL procedure or function.

VARIABLE x NUMBER;
CALL getEmpCountByDept(1) INTO :x;
Getting Number of Employees for Dept...

Call completed.

PRINT x

         X
----------
         1

The SQLException class has the getErrorCode() and getErrorMessage() methods to help report errors. Any uncaught exception in a Java Stored Procedure results in an 'ORA-29532 Java call terminated by uncaught Java exception' for the caller. How you choose to handle errors will vary by application. The addEmp method simply catches and displays the error. We receive an error message when we attempt to add an emp record with an invalid dept_id.

SQL> execute addEmp(2,'aaa', 45000.00,2);
Creating new employee...
ERROR! Adding Employee : ORA-02291: integrity constraint
                         (OPS$AK4353.FK_DEPT_ID) violated -
                         parent key not found

Because there is a need to call Java from PL/SQL, it is reasonable to assume that we will also need a way to call PL/SQL from Java code. This is very easy to achieve by using a CallableStatement object in our Java methods.

CallableStatement cstmt = conn.prepareCall("{your procedure}");

Thus, it is possible to create a seamless environment of PL/SQL procedures calling Java and vice versa.

Summary

Even though I examined only a stored procedure in the above scenario, you can even embed those Java stored procedures in PL/SQL Packages. Don't consider only Java stored procedures; there also exists the concept of .NET based stored procedures (primarily called .NET CLR stored procedures).  .NET based stored procedures are supported by both SQL Server 2005 and Oracle 10g Version 10.2.  Remember, it is possible to create a seamless environment of PL/SQL procedures calling Java and vice versa.


DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

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