Win a copy of Svelte and Sapper in Action this week in the JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

calling stored procedure from java

 
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
as
name varchar(10);
begin
select emp_name into name from EMP where sal=2000;
end;

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();
rs.next();
System.out.println(rs.getString(1));


please explain me how to call the procedure using prepareCall.

Thanks in advance
Haripriya

SCJP1.4
 
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Jules
 
Ew. You guys are ugly with a capital UG. Here, maybe this tiny ad can help:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic