Can a procedure contain only a SELECT statement?
in [Oracle]
|
Prev: semvmx
Next: 10.2.0.5 Patchset
From: Galen Boyer on 24 Mar 2010 21:55 Robert Klemme <shortcutter(a)googlemail.com> writes: >> 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? Hm... So, on any OS you can run Oracle? How is that not an argument that is pro Oracle? -- Galen Boyer --- news://freenews.netfront.net/ - complaints: news(a)netfront.net ---
From: gazzag on 25 Mar 2010 05:39 On 25 Mar, 01:55, Galen Boyer <galen_bo...(a)yahoo.com> wrote: > Robert Klemme <shortcut...(a)googlemail.com> writes: > >> 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? > > Hm... So, on any OS you can run Oracle? How is that not an argument > that is pro Oracle? > > -- > Galen Boyer At the very least we can agree that it's a limit of SQL Server :) -g
From: Jonathan Lewis on 25 Mar 2010 09:01 "Jonathan Lewis" <jonathan(a)jlcomp.demon.co.uk> wrote in message news:RM6dnTmUA8fLkjvWnZ2dnUVZ8mqdnZ2d(a)bt.com... > > 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 nocount 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. > > Would anyone care to make a technical comment on my earlier comment I'm still interested to hear how the front-end code can handle the output from a procedure when it doesn't have any information about what that output might look like. -- Regards Jonathan Lewis http://jonathanlewis.wordpress.com
From: Mladen Gogala on 25 Mar 2010 11:10 On Thu, 25 Mar 2010 13:01:58 +0000, Jonathan Lewis wrote: > Would anyone care to make a technical comment on my earlier comment > > I'm still interested to hear how the front-end code can handle the > output from a procedure when it doesn't have any information about what > that output might look like. Well, there must be some information. If the output type is a cursor, one can describe the cursor. In Perl, which is my front end of choice, a statement handle has the following properties which describe the output rather well: NUM_OF_FIELDS,NAME,TYPE,PRECISION and SCALE. I can always construct my output of choice, based on the handle description, even if I don't know what the original statement looks like, which is frequently the case with the dynamic SQL. However, I have to know that the output type is a cursor, not a scalar variable. So, if my output is a statement handle called $sth, I would do the following: my $nf = $sth->{NUM_OF_FIELDS}; for ( my $i = 0; $i < $nf; $i++ ) { my $name = $sth->{NAME}[$i]; my $type = $sth->{TYPE}[$i]; my $prec = $sth->{PRECISION}[$i]; my $scle = $sth->{SCALE}[$i]; my $tn=$db->type_info($type)->{TYPE_NAME}; "Field number $i: name $name of type $tn". "with precision $prec,$scle\n"; } That would give me the exact description of the output. One cannot know nothing of the output, that would make any programming impossible. Executing a procedure which you know nothing about is like the Russian roulette: you never know when the chamber will be loaded and what will come out of the barrel. -- http://mgogala.byethost5.com
From: Jonathan Lewis on 25 Mar 2010 11:38
"Mladen Gogala" <no(a)email.here.invalid> wrote in message news:pan.2010.03.25.15.10.07(a)email.here.invalid... > On Thu, 25 Mar 2010 13:01:58 +0000, Jonathan Lewis wrote: > >> Would anyone care to make a technical comment on my earlier comment >> >> I'm still interested to hear how the front-end code can handle the >> output from a procedure when it doesn't have any information about what >> that output might look like. > > Well, there must be some information. One would hope so - but in the Oracle world your front-end code has a formal link with the datbaase through the input types for a procedure and the output type for a function, and it's a little bit difficult to change the (database) code in a way that changes the input and output types without realising that the front-end code might need to be modified. On the other hand, in the SQL Server world you can have a procedure that has no apparent feature for a formal definition of what it's going to output - and therefore no formal mechanism for the front-end to associate what it's expecting with what the database code might deliver. -- Regards Jonathan Lewis http://jonathanlewis.wordpress.com |