Re: Ref Cursors

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