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?
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.