Re: Can a procedure contain only a SELECT statement?
Date: Sat, 20 Mar 2010 18:18:30 -0400
Thomas Gagne <TandGandGAGNE_at_gmail.com> writes:
> Thank you all for your help.
> My background is Sybase & SqlServer. On both, due I'm sure to a
> common heritage, a stored procedure is capable of being as simple or
> complex as the programmer wants. Sometimes, all that is needed is a
> select statement. Sometimes even simple projections may require
> multiple steps to prepare the last SELECT. Additionally, stored
> procedures are capable of returning multiple result sets. I assumed,
> incorrectly, such a thing was not so complicated that it couldn't be
> easily done inside Oracle.
> I'm curious about packages and the procedures inside them. I take it
> the cursor must be treated as a cursor inside the application rather
> than as a result set? I'm using .Net, OracleDataAdapter, and DataSet.
> It seems as though result sets from stored procedures will require
> something else.
The fact that a procedure can also return a resultset doesn't really fit the model of what a procedure is intended for. Most SQLServer/Sybase guys, first coming into Oracle look for it and are quite frustrated that the concept does not exist because it was "easy" in other environments, and I know because I was one of them many moons ago. But, now, after having programmed in Oracle for a long time, I very much like the clear distinction.
As others have said, use a function, or have your procedure fill an output parameter with a ref cursor.
-- Galen Boyer --- news://freenews.netfront.net/ - complaints: news_at_netfront.net ---Received on Sat Mar 20 2010 - 17:18:30 CDT