Can a procedure contain only a SELECT statement?
in [Oracle]
|
Prev: semvmx
Next: 10.2.0.5 Patchset
From: Galen Boyer on 20 Mar 2010 18:18 Thomas Gagne <TandGandGAGNE(a)gmail.com> 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://freenews.netfront.net/ - complaints: news(a)netfront.net ---
From: Galen Boyer on 20 Mar 2010 18:20 Robert Klemme <shortcutter(a)googlemail.com> writes: > On 03/19/2010 08:39 PM, Sybrand Bakker wrote: >> On Thu, 18 Mar 2010 22:26:44 -0400, Thomas Gagne >> <TandGandGAGNE(a)gmail.com> wrote: >> >>> 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 Oracl >> >> Mickeysoft has never understood the Procedure concept, and ignored the >> formal defintiion and abused it to return a result set. >> It seems like you belong to the class of sqlserver 'developers' which >> is so narrow-minded they automatically reject everything done >> differently by Oracle and start bashing Oracle for it. >> Luckily sqlserver is incapable of being an enterprise class product, >> just because of its poor architecture and vendor lock-in, so your >> 'objections' are futile. > > I would not be too sure of that. SQL Sever isn't as bad as people are > trying to make it look - and it's gaining ground, especially in the > area of dealing with larger data sets. Maybe it's not as "enterprise > class" as Oracle is (or is claimed to be) but the management tools > with good graphical user interface were there before Oracle had Grid > Control. Yes, I know - real DBA's use command line, but there are > situations where a graphical visualization can greatly help. One of the biggest winning arguments for Oracle, is that it run on almost all platforms, MS products only run on one. -- Galen Boyer --- news://freenews.netfront.net/ - complaints: news(a)netfront.net ---
From: Mladen Gogala on 20 Mar 2010 20:59 On Sat, 20 Mar 2010 05:56:06 -0700, Steve Howard wrote: > Python has been my language of choice recently. I know you are a big > perl guy based on your past posts, but I absolutely love the python > model. I am, actually, rather unhappy because the company I worked for decided to adopt Python as its scripting language. All those ad-hoc scripts that I used to write in Perl will from now on have to be written in Python. Having worked with Perl for a decade, I am quite familiar with it and I like it a lot. The developers, however, claim that Perl is hard to learn, something that I bitterly dispute, and that it has a lousy object model, which I must agree with. A have bought a book about Python and will have to start reading it one of these days. I will try keeping my Perl skills current, though. I am writing a schema comparison tool that will be completely open source, in Perl. It will include Postgres, too. -- http://mgogala.byethost5.com
From: Thomas Gagne on 21 Mar 2010 07:21 Galen Boyer wrote: > <snip> > > The fact that a procedure can also return a resultset doesn't really fit > the model of what a procedure is intended for. Can you elaborate on "... what a procedure is intended for?"
From: John Hurley on 21 Mar 2010 07:33
On Mar 21, 7:21 am, Thomas Gagne <TandGandGA...(a)gmail.com> wrote: snip > > The fact that a procedure can also return a resultset doesn't really fit > > the model of what a procedure is intended for. > > Can you elaborate on "... what a procedure is intended for?" Really please take the time to buy and read a couple of Tom Kyte's books. It would probably be a better use of your time and you would have something permanent. The first thing you need to understand is that you want to harness the power of Oracle not do things in some kind of database neutral type of interfacing. Start by tossing out all your preconceived ideas of how you would do it in some other environment. Good luck! |