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

getting ResultSet from Java Stored Procedure  RSS feed

 
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, I'm running a Java Stored Procedure on an Oracle Database. I want to call the Stored Proc. from a Java application.
I'm able to do so and return values, however I want to be able to return a result set. Is this possible in Java Stored Procedures?
If so, do i have to use Oracle Cursors?
I thought about having my stored proc. simply return a result set, but then how would i publish it (what sql type does a result set map to)?
Thanks.
 
Sheriff
Posts: 6450
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your stored proc needs to return a cursor. I usually do something like:
package

package body

HTH
 
A Sundaram
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But in my Stored Procedure Class, I'd return a resultset object?
 
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Sundaram,
Jason has given you PL/SQL code for a database stored procedure that returns a "result set". A more complete example is available here:
http://osi.oracle.com/~tkyte/ResultSets/index.html
Hope this helps you.
Good Luck,
Avi.
 
A Sundaram
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey Avi, thanks. I've actually already looked at that website. I understand that when i publish the procedure, I should return an Oracle cursor. The issue that I'm having is that I'm using a Java Stored Procedure:
public static ResultSet spResultSet()
{
ResultSet rset;
try
{
Connection conn =
DriverManager.getConnection("jdbc efault:connection:");
String sql = "select * from mytable_name";
Statement stmt = conn.createStatement();
rset = stmt.executeQuery(sql);
return rset;

}catch (SQLException e)
{
System.err.println(e.getMessage());
return null;
}
}
Is my code correct for returning a resultset? Im using Oracle 8i, i read somewhere that Java Stored Procs can only return cursors/resultsets in 9i. Is that true? thanks a lot for your help.
 
Ranch Hand
Posts: 925
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
From the java client side you can pick up either as resultset type?:

stmt.registerOutParameter(x,oracle.jdbc.driver.OracleTypes.CURSOR);
ResultSet rs = (ResultSet) stmt.getObject(x);
What error message do you get?
 
Jason Menard
Sheriff
Posts: 6450
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Originally posted by A Sundaram:
Hey Avi, thanks. I've actually already looked at that website. I understand that when i publish the procedure, I should return an Oracle cursor. The issue that I'm having is that I'm using a Java Stored Procedure:
public static ResultSet spResultSet()
{
ResultSet rset;
try
{
Connection conn =
DriverManager.getConnection("jdbc efault:connection:");
String sql = "select * from mytable_name";
Statement stmt = conn.createStatement();
rset = stmt.executeQuery(sql);
return rset;

}catch (SQLException e)
{
System.err.println(e.getMessage());
return null;
}
}
Is my code correct for returning a resultset? Im using Oracle 8i, i read somewhere that Java Stored Procs can only return cursors/resultsets in 9i. Is that true? thanks a lot for your help.


A "Java Stored Procedure" is something different. Java Stored Procedures are Java code stored in the database that is accessed like a stored procedure. In 99% of situations, I would say a normal PL/SQL stored procedure is the way to go.
In your Java app of course you must write code to call that stored proc, be it a normal stored procedure or a Java stored procedure. The code I gave you is the PL/SQL code for a stored procedure which returns a cursor (as far as your concerned, a result set). The code you gave looks like code for your java application which will call a stored proc in oracle, reading the result of that stored proc into a ResultSet.
Additionally, you have the code in a method which returns the ResultSet. There is nothing wrong with having a method in your java app which returns a result set, but it is not necessary. It would just depend on how you wanted to architect your app.
 
A Sundaram
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The code i gave was actually the Java Stored Procedure itself.
I've published it as follows:
CREATE OR REPLACE PACKAGE types
AS
TYPE rs_cursor IS REF CURSOR;
END;
CREATE OR REPLACE FUNCTION CURRENCIES_SPRESULTSET
return rs_cursor
AS LANGUAGE JAVA
NAME 'Currencies.spResultSet() return java.sql.ResultSet';
It publishes fine.
Then i call the stored proc from my java application as follows:
cs = db_connection.prepareCall("{? = call CURRENCIES_PACKAGE.CURRENCIES_SPRESULTSET()}");
cs.registerOutParameter(1,OracleTypes.CURSOR);

cs.execute();
ResultSet rs = (ResultSet)cs.getObject(1);
I get a fatal error when running cs.execute. I'm about to try it on Oracle 9i, i'll let you know what happens. but is what I'm trying ok?
 
Jason Menard
Sheriff
Posts: 6450
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry I wasn't understanding you at first. Check out the links off of these pages and see if anything points you in the right direction.
Java Stored Procedures- Sample Corner
Java Center - OracleJVM and Java Stored Procedures
 
A Sundaram
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks for the links. none of the documentation i find out there gives me examples of what I need to do. I ran it on Oracle 9i and i get mismatched types error because my Java stored procedure returns a ResultSet and my java app says:
cs.registerOutParameter(1,OracleTypes.CURSOR);
So apparently my stored procedure should be returning something else. but what? I'm still not sure if it's even possible to return a result set from a java Stored Proc. because I haven't seen it done elsewhere yet.
 
Jason Menard
Sheriff
Posts: 6450
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just as a style note, from what I understand it is preferable to use normal PL/SQL based stored procs over java stored procs when possible. We had initially started using java stored procs in certain places on one project I had worked on, but decided it was more trouble than it was worth and kept the Java on the application side and used regular PL/SQL stored procs on the Oracle side.
 
A Sundaram
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I want to use Java Stored Procs. so that it doesn't matter whether I'm using Oracle or Sybase, my actual Java code stays the same (as opposed to PL/SQL and Transact SQL)
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!