JRUN Oracle and Stored Procedures

[J Fideler]

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]

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]

Moving this to the Oracle forum, since it is not a Servlets question.

Mark

[Mark Spritzler]

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