Re: Ref Cursors

Thanks for the expansion and clarification.

Dan Morgan

Thomas Kyte wrote:

> In article <3C3787A4.82E868AC_at_exesolutions.com>, "Daniel says...
> >
> >You wrote the following in the posting above:
> >
> >"absolutely FALSE. Using stored procedures and never putting
> >SELECTS/INSERTS/UPDATES/DELETES in any application is a very good practice"
> >
> >Could you please expand on this. If you don't put these into a stored procedure
> >what possible use could a stored procedure have? And if not there ... where?
> >
> >Thanks,
> >
> >Dan Morgan
> >
>
> We need a bit more context here to make this make sense for others, the
> discussion was:
>
> <quote>
> >A ref cursor will *always* return one row at a time. Hence, this
> >results in one roundtrip, and there is nothing you can do about that.
>
> absolutely FALSE. a ref cursor is just like a cursor. See
> http://groups.google.com/groups?q=group:comp.databases.oracle.*+insubject:ref+insubject:cursors+author:tkyte%40us.oracle.com&selm=a15i6h01cmm%40drn.newsguy.com&rnum=2
>
> which demonstrates this.
>
> >Remember, if you build pure Oracle applications (ie you don't port
> >them from sqlserver) you won't use stored procedure.
>
> absolutely FALSE. Using stored procedures and never putting
> SELECTS/INSERTS/UPDATES/DELETES in any application is a very good practice, one
> that I encourage (it makes it so much easier to tune when I can sit at a
> terminal, look at a tkprof, find the bad query, read the code out of the
> database, fix it in vi, put it back in -- no redeploy this
> war/ear/jar/zip/whatever file, bounce the app server, reload the application,
> yadda yadda yadda.....).
>
> I gain security enhancements by doing this.
> I gain reusability by doing this.
> I gain ease of maintainence by doing this.
> I gain control by doing this.
> </quote>
>
> I read:
>
> >Remember, if you build pure Oracle applications (ie you don't port
> >them from sqlserver) you won't use stored procedure.
>
> to mean that if you are using Oracle, putting SELECTS into stored procedures and
> returning result sets from stored procedures is a bad practice, something
> "oracle people" don't do and "MS people do do".
>
> My point was -- It is a pretty good practice in my opinion regardless of the
> database you are using. I would like to see NO inserts/updates/deletes/selects
> in the client application. I personally would love to see all of that logic
> centralized in the database. Need a result set -- fine, call a stored procedure
> to get it. That is my personal preference. The reasons for that preference --
> i listed above.
>
> I was trying to say -- the approach of using stored procedures to return result
> sets is one to be encouraged in Oracle. Not discouraged, it is not a "sql
> server 'ism" (although in sql server, its sort of a mandatory thing -- if you
> don't do it that way, you'll kill yourself on parses since they don't have
> shared sql like we do)
>
> --
> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
Received on Sun Jan 06 2002 - 09:25:36 CST