wood burning stoves 2.0
The moose likes JDBC and Relational Databases and the fly likes  calling stored procedure from java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark " calling stored procedure from java" Watch " calling stored procedure from java" New topic

calling stored procedure from java

harry psll

Joined: Jun 17, 2004
Posts: 17
hai all,
I am new this concept , can any one explain me how to call a stored procedure from java.

stored procedure is

create or replace procedure show_emp
name varchar(10);
select emp_name into name from EMP where sal=2000;

this procedure( which does not take any parameters and there exists a row in EMP table with salary 2000 ) was created successfully
but when i am using CallableStatement and calling the procedure , the output is 'No ResultSet is produced'.

code snippet is as follows

CallableStatement cs = con.prepareCall("{call show_emp}");

ResultSet rs=cs.executeQuery();

please explain me how to call the procedure using prepareCall.

Thanks in advance

Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Hi Haripriya,

Your Java code is fine. The problem is with your SP. If you run it directly in Oracle you will find that it returns nothing. This is because you select the name into a variable. Oracle does not directly return data from stored procedures in a ResultSet as, for example, MS SQL Server and Sybase do. I believe that you need to declare a REF CURSOR, return it as an output parameter and then use some proprietory classes from your Oracle JDBC installation to work with it. I've never done it myself but you should be able to find examples by searching this forum.

I agree. Here's the link: http://aspose.com/file-tools
subject: calling stored procedure from java
It's not a secret anymore!