Newbie Oracle sp question

Hey all-

I've been using sql server for a number of years, and this is the first time I've used Oracle 8i heavily. I have a very large sql query I'd like to put in a stored procedure so that I merely have to pass the start and end dates, and get back a dataset based on that.

Problem is, I cannot seem to write one properly. In transact-sql I'd write:

create procedure foo
as
-- super simple
select * from a_table

which I assumed translated into (with the help of the manual):

create procedure foo
as
begin
select a_column from a_table;
end;
/

except that this gives me compilation errors, specifically

4/1      PLS-00428: an INTO clause is expected in this SELECT statement
4/1      PL/SQL: SQL Statement ignored

The select, when run by itself in sql+, works fine.

I whipped up a quick procedure that did an update:

create procedure foo2
as
begin
update a_table set a_column = 3;
end;
/

and it works perfectly.

So suddenly I've got to ask a very silly question: Can you return a result set from a stored procedure? I can't believe that with all the power of pl/sql, java, etc., that the only way to return a result set is to push dynamic sql. So what am I doing wrong?

Thanks for any info,

Theo Received on Fri Oct 20 2000 - 11:07:08 CDT