Oracle Stored Procedures and result sets

From: Ray Porter <ray_porter_at_unc.edu>
Date: 2000/04/19
Message-ID: <8dk9fe$f7i$1@news2.isis.unc.edu>#1/1

Hi,
I'm in the process of migrating several moderately large applications from Sybase to Oracle. I have two questions:

How do you create a stored procedure in Oracle to return a result set? In Sybase, all you have to do is:

create proc myProcName (@parm1 char(9)) as select * from myTablename
where myField = @parm1
go

I know it's not that simple in Oracle but I need a clear example of how to do it.

2) Is there any performance gain from using stored procedures in Oracle to retrieve read-only data? Our experience with Sybase has been that a stored procedure gains us a performance boost on the order of a factor of 10-20 over SQL embedded in an application. If there is no similar performance gain in Oracle, is there any other reason to use a stored procedure in this manner (other than the obvious security benefits)?

Any help will be greatly appreciated,

Ray Porter
Applications Analyst Programmer
Administrative Information Services, UNC-CH Phone: 966-5878
email: ray_porter_at_unc.edu

dragon_at_email.unc.edu
Home Page: http://www.unc.edu/~dragon/

"Meddle not in the affairs of dragons,
for thou art crunchy and taste good with ketchup." Received on Wed Apr 19 2000 - 00:00:00 CDT