Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
  • 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 ...
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Jeanne Boyarsky
  • Tim Cooke
  • Knute Snortum
  • Junilu Lacar
  • Devaka Cooray
Saloon Keepers:
  • Ganesh Patekar
  • Tim Moores
  • Carey Brown
  • Stephan van Hulst
  • salvin francis
  • Ron McLeod
  • Frits Walraven
  • Pete Letkeman

calling oracle procedures in java  RSS feed

Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

i am trying to call oracle procedure in java and it aslo running without errors but i couldn't get the correct results.

i created one procedure in oracle using cursors beacue it retrives more than one record form shows only last record when we call the procedure in java. here the code.

oracle procedure

create or replace procedure Proc_SearchStdInfo_ByName(id out varchar,name in out varchar,add
out varchar,year out varchar ) is
cursor Cursor_SearchByName(name student.sname%type) is select sid,sname,sadd,syear from student where sname=name;
open Cursor_searchByName(name);
fetch Cursor_SearchByName into id,name,add,year;
exit when Cursor_SearchByName%notfound;
---dbms_output.put_line(id||' '||name||' '||add||' '||year);
end loop;
close Cursor_SearchByName;
end Proc_SearchStdInfo_ByName;

i have written code in java like
Student std=new Student();
CallableStatement cs=con.prepareCall("{call Proc_SearchStdInfo_ByName(?,?,?,?)}");
ResultSet rs=cs.executeQuery();

it shows only one record that is the last result of table.

how can we retrive all results from table using oracle procedure
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Even if you execute the procedure from Oracle you get only the last record values fetched into the out parameters before closing the cursor.I mean if you put "dbms_output.put_line(id||' '||name||' '||add||' '||year);" statement after "end loop;" and before "close Cursor_SearchByName;" statement you will know that.

If you would like to iterate through the cursor from the java code, then you need to have the cursor as out parameter.

Hope this may help you.
Consider Paul's rocket mass heater.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!