Can a procedure contain only a SELECT statement?
in [Oracle]
|
Prev: semvmx
Next: 10.2.0.5 Patchset
From: Robert Klemme on 21 Mar 2010 07:55 On 20.03.2010 23:20, Galen Boyer wrote: > 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. Why is that an argument pro Oracle? Kind regards robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/
From: Jonathan Lewis on 21 Mar 2010 08:21 "Thomas Gagne" <TandGandGAGNE(a)gmail.com> wrote in message news:tqadnRsfhsIjGjzWnZ2dnUVZ_qudnZ2d(a)wow.com... > I'm looking around for Oracle 10g CREATE PROCEDURE syntax and looking for > examples with simple SELECT statements inside. I see a lot of DML but > none with simple SELECT statements. > > I'm thinking something like > > create or replace procedure aSimpleSelect (aLikeValue char(4)) as > begin > select * > from aTableName > where aColumn like aLikeValue; > end; / > > But when I try creating it inside 10g it complains: > > PLS-00103: Encountered the symbol "(" when expecting one of the > following: :=), default varying character large the symbol ":=" was > substituted for "(" to continue. > I think the thing that looks odd to the Oracle professional is as follows: In SQL Server you can do something like this (apologies for incorrect table and column names, I don't have a copy of the software handy): create procedure jpl as set notcount on select name from sys.schemas; select name, physical_name from sys.master_files; set nocount off go From (say) sqlcmd you can now type: > jpl > go This effectively executes and displays the results of the two queries in the procedure - and this seems "viable" in something like a lightweight tool supplied by the people who produced the database software. BUT - The procedure seems to have taken on the responsibility of knowing how to output the data to the front-end. So, from the viewpoint of the Oracle developer, what do you have to do in the application code to know that when you call the procedure you're going to get two result sets which are different shapes. (Presumably you want to see two sets of data, rather than one set of data which is just a single column very wide string.) And how does the front-end code know that it might, or might not, get some "data" which is actually row counts depending on whether the procedure "set nocount on" or not ? Does your application call to the procedure have to know about all the result sets that could be produced in the procedure and call the procedure passing in references to some sort of cursor handle ? Please bear in mind that this question is being asked from a perspective of total ignorance of how you are expected to use procedures in application code written for SQL Server. -- Regards Jonathan Lewis http://jonathanlewis.wordpress.com
From: Jonathan Lewis on 21 Mar 2010 08:24 "Galen Boyer" <galen_boyer(a)yahoo.com> wrote in message news:u4okahbya.fsf(a)www.yahoo.com... > > One of the biggest winning arguments for Oracle, is that it run on > almost all platforms, MS products only run on one. > > On the other hand, many businesses only want to run on a single platform. -- Regards Jonathan Lewis http://jonathanlewis.wordpress.com
From: Bob Jones on 21 Mar 2010 11:49 "Robert Klemme" <shortcutter(a)googlemail.com> wrote in message news:80mfq8Fo7U1(a)mid.individual.net... > On 20.03.2010 23:20, Galen Boyer wrote: >> 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. > > Why is that an argument pro Oracle? > Very simple, choices. That is a major advantage.
From: Robert Klemme on 21 Mar 2010 14:10
On 03/21/2010 04:49 PM, Bob Jones wrote: > "Robert Klemme" <shortcutter(a)googlemail.com> wrote in message > news:80mfq8Fo7U1(a)mid.individual.net... >> On 20.03.2010 23:20, Galen Boyer wrote: >>> 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. >> Why is that an argument pro Oracle? >> > > Very simple, choices. That is a major advantage. I beg to differ. Some do not need the choice, some are only looking for MS based products, some don't care about the OS... On the other hand, if you need to support multiple platforms you either need to make compromises to be able to adjust your product to all of them - or you need significant more development resources. The sheer number of supported OS to choose from is not a value in itself. Actually I believe "choice" is often overrated. You certainly cannot look at it alone and make that a major selling point for one product or another. If you are building a product for a wide range of application cases from very small to very large then it's probably a good idea to look at the platforms a database product can be run on. But even then you should closely look at the details - it may turn out that supporting one DB product on multiple platforms turns out as expensive as having to support different products altogether. If you do not need to support such a variety of environments then the platform may be totally irrelevant and other aspects such as customer requirements, reusing existing code base etc. might be paramount. Many companies today use more than one OS so the aspect of additional cost for maintaining another platform often is non existent. Kind regards robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/ |