This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes calling oracle procedures in java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "calling oracle procedures in java" Watch "calling oracle procedures in java" New topic
Author

calling oracle procedures in java

subbaraju nadimpalli
Greenhorn

Joined: Sep 12, 2006
Posts: 1
hi

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 table.it 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;
begin
open Cursor_searchByName(name);
loop
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(?,?,?,?)}");
cs.registerOutParameter(1,Types.VARCHAR);
cs.registerOutParameter(2,Types.VARCHAR);
cs.registerOutParameter(3,Types.VARCHAR);
cs.registerOutParameter(4,Types.VARCHAR);
cs.setString(2,sname);
ResultSet rs=cs.executeQuery();
if(rs!=null)
{
while(rs.next())
{
std.setID(cs.getString(1));
std.setName(cs.getString(2));
std.setAdd(cs.getString(3));
std.setYear(cs.getString(4));
}
}

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

how can we retrive all results from table using oracle procedure
Sujith Kanaparthi
Ranch Hand

Joined: Sep 04, 2005
Posts: 45
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: calling oracle procedures in java
 
Similar Threads
Handling cursor declared in oracle in java program
ORA-06550: line 1, column 36: PLS-00103: Encountered the symbol ";"
Problem in executing Oracle Function from JSP Page
JdbcOdbcDriver/Stored procedures. Please help
problem executing stored procedure from jsp