Re: Newbie Oracle sp question

This is a boring question.
All sqlserver developers have one thing in common: they don't read manuals.
Also this question has been answered numerous times in this newsgroup, so a search on www.deja.com is not going to turn up a blank. If you are using jdbc and/or odbc to communicate with Oracle there is no real need to use stored procedures for this purpose as they both can use parameters.
In fact you should try to avoid porting all your sql-server habits to Oracle.
That all said, if you really want to use a stored procedure for this purpose, check out REF CURSOR in your documentation, and or buy the PL/SQL book by Steven Feuerstein, and/or refer to http://osi.oracle.com/~tkyte

Regards,

Sybrand Bakker, Oracle DBA

"Thelonious Georgia" <keepberthasurfin_at_hotmail.com> wrote in message news:8spqmg$59f$1_at_news.panix.com...
> 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 - 12:08:07 CDT