I'm not going to bash Oracle...it does a good job of bashing itself. For those of us used to being productive using SQL Server (I've heard it said that Oracle sells its products to managers, Microsoft sells to developers), here's some interesting info on calling an Oracle proc.
OK, so you are used to SQL Server and you want to create a proc that ultimately (at some point) returns some results in the form of...well, a bunch of records!
create procedure foo as
...
select a, b, c
from bar
where blah = 1
You jump into Oracle and try the same thing you'll get this nice error message:
PLS-00428: an INTO clause is expected in this SELECT statement. Also we are better than you M$ noobcakes and Bill Gates is a jerk. Windoze is teh suxxorz. ZOMG barrens chat.
The way around this is to use a ref cursor. It's silly and it's juvenile and it's like going back in time, but it works. http://support.microsoft.com/?id=309361
But before you get to pissed off about having to create an Oracle package and define a cursor type, with 9i and 10g you can use sys_refcursor and save the trouble.
CREATE OR REPLACE PROCEDURE Foo (io_cursor IN OUT sys_refcursor) AS
v_cursor sys_refcursor;
...
begin
open v_cursor for
select a, b, c
where blah = 1;
io_cursor := v_cursor;
end;
That's it, you're done! Yay!
Oh wait no you're not!!! Because if you want to test it you're probably thinking "Hey I can just type EXEC Foo in Query Analyzer." Sorry there's more pig $#!+ you have to go through than that. Isn't DBArtisan a great tool?!?! *vomits*
declare
v_cursor sys_refcursor;
a number;
b number;
c number
begin
Foo(io_cursor => v_cursor);
loop
fetch v_cursor into a, b, c;
exit when v_cursor%NOTFOUND;
dbms_output.put_line(a || b || c);
end loop;
close v_cursor;
end;
Hopefully you're not stuck in Oracle hell (god I miss SQL Server 2005), but if you've had your nose jammed in it like a bad dog and you're not getting help from the elitist oracle buff community, and you run across this problem...hope it helps. :-)