Can a procedure contain only a SELECT statement?
in [Oracle]
|
Prev: semvmx
Next: 10.2.0.5 Patchset
From: Mladen Gogala on 19 Mar 2010 15:23 On Fri, 19 Mar 2010 11:51:59 -0700, Thomas Gagne wrote: > So is there a way to use packaged procedures to select data without > using cursors? It would be great if I could use the same syntax inside > SQLDeveloper as I might inside a program, and process the results > similarly to how normal selects are handled. No, it is not possible. Oracle does not support local temporary tables which are alternative to cursors in some other RDBMS systems. -- http://mgogala.byethost5.com
From: Sybrand Bakker on 19 Mar 2010 15:39 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. -- Sybrand Bakker Senior Oracle DBA
From: Thomas Gagne on 19 Mar 2010 16:56 Sybrand, no need to be insecure. I'm just trying to figure out how Oracle programmers do things I've been doing since the early 90s. At present my task is to figure out how to do write procedures for Oracle and am trying to discover what it's capabilities are. Packages and cursors add complexity that doesn't exist in other products. Don't feel threatened, I'm sure there are features Oracle provides that are difficult to simulate without complexity in other DBs, like recursive SELECTs. But for the purposes of this thread, the topic is stored procedures and returns data sets either to an interactive user using SQLDeveloper or an application.
From: Mladen Gogala on 19 Mar 2010 17:16 On Fri, 19 Mar 2010 13:56:20 -0700, Thomas Gagne wrote: > Sybrand, no need to be insecure. Sybrand may be anything, but insecure he's not. Pop psychology on oracle group is a sign of bad taste. I do dislike Sybrand intensely and I would use a word starting with "a" and ending in "le" to describe him, but insecure he's not. -- http://mgogala.byethost5.com
From: John Hurley on 19 Mar 2010 17:58
On Mar 19, 4:56 pm, Thomas Gagne <tgga...(a)gmail.com> wrote: snip > Sybrand, no need to be insecure. I'm just trying to figure out how > Oracle programmers do things I've been doing since the early 90s. At > present my task is to figure out how to do write procedures for Oracle > and am trying to discover what it's capabilities are. Packages and > cursors add complexity that doesn't exist in other products. Don't > feel threatened, I'm sure there are features Oracle provides that are > difficult to simulate without complexity in other DBs, like recursive > SELECTs. > > But for the purposes of this thread, the topic is stored procedures > and returns data sets either to an interactive user using SQLDeveloper > or an application. I would recommend that you buy and read ( probably multiple times ) Tom Kyte's book Expert Oracle Architecture. Read the first chapter a couple of times. Do the shampoo song ( rinse and repeat ). You need to understand and harness the power of Oracle ... not keep repeating I would do this if it were SQL server. That's the road to purgatory that you are currently traveling on. |