File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JRUN Oracle and Stored Procedures

 
J Fideler
Greenhorn
Posts: 1
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I cannot find a single example in nature of an Oracle Stored Procedure that returns a set of results (i.e. select * from user) along with the java code that would read that set of results through a jdbc connection pool

Why is this such an obscure thing?? In the SQL Server world this is the normal way to do things and there are examples galore (simple ones!!)

What gives here? Or are stored procs simply not done in Oracle?

Please Help
 
Carl Trusiak
Sheriff
Posts: 3340
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ref Cursor

Getting a connection from a pool is the same for Oracle, MySQL, M$Sql etc. The only difference is the configuration of the pool to specify the DriverManager class.
 
Mark Spritzler
ranger
Sheriff
Posts: 17276
6
IntelliJ IDE Mac Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Moving this to the Oracle forum, since it is not a Servlets question.

Mark
 
Mark Spritzler
ranger
Sheriff
Posts: 17276
6
IntelliJ IDE Mac Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oracle and SQL Servers stored procedures work differently. In most cases SQL Server stored procedures are really just stored queries. Whereas in Oracle a stored procedure is actually a procedure of code. Meaning you can do many things, not necessarily getting data. Although in that case why make it a stored procedure on the back-end when you can move that type of code closer to where it is needed.

In Oracle to get a Result set back, you have to actually pass an empty result set to the procedure as an IN/OUT parameter. That is waht is called a ref cursor, or reference cursor.

Good Luck.

Mark
 
Consider Paul's rocket mass heater.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic