Thanks, Thomas. You've answered my questions very thoroughly.
--
================================================
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."
"Thomas J. Kyte" <tkyte_at_us.oracle.com> wrote in message
news:8dkgl3$k34$1_at_nnrp1.deja.com...
> In article <8dk9fe$f7i$1_at_news2.isis.unc.edu>,
> "Ray Porter" <ray_porter_at_unc.edu> wrote:
> > Hi,
> > I'm in the process of migrating several moderately large applications
from
> > Sybase to Oracle. I have two questions:
> >
> > 1) 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.
> >
>
>
> its pretty much that simple.
>
> ultimately it'll boil down to something like:
>
> create or replace function sp_ListEmp return types.cursortype
> as
> l_cursor types.cursorType;
> begin
> open l_cursor for select ename, empno from emp order by ename;
> return l_cursor;
> end;
> /
>
>
> See http://osi.oracle.com/~tkyte/ResultSets/index.html for more info.
>
> > 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)?
> >
>
> The reason it goes faster as a stored proc in Sybase is because they do
> not have the concept of a SHARED POOL like we do. The first time we
> see a query/procedure we parse it and cache it for the next person.
> The actual parsing/optimization of a query may take upto 90% or more
> the total execution time of the query. By caching these plans, we can
> in some cases realize an order of magnitude or more increase in
> performance (now, if the query is scanning 1,000,000,000 rows -- it'll
> not be as great but for indexed reads and such, its huge).
>
> In sybase the only way to achive anything similar is to put the static
> queries into a stored procedure -- they will compile a query into a
> plan if you put it in a stored procedure.
>
> In general, the performance of a ref cursor opened in PLSQL and a
> cursor opened in the client will be the same. The mechanics are
> identical. I would keep the logic in the database however if at all
> possible for the simple reason that bug fixing and tuning is infinitely
> easier if you can fix the code in the database and avoid having to fix
> a 3gl client (and then redistribute and make sure everyone has the same
> version and so on).
>
>
> > 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."
> >
> >
>
> --
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries
> http://osi.oracle.com/~tkyte/index.html
> --
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Apr 19 2000 - 00:00:00 CDT
