Re: Can a procedure contain only a SELECT statement?

From: Galen Boyer <>
Date: Sat, 20 Mar 2010 18:18:30 -0400
Message-ID: <>

Thomas Gagne <> 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:// - complaints: ---
Received on Sat Mar 20 2010 - 17:18:30 CDT

Original text of this message