Can a procedure contain only a SELECT statement?
in [Oracle]
|
Prev: semvmx
Next: 10.2.0.5 Patchset
From: Serge Rielau on 25 Mar 2010 16:34 On 3/25/2010 11:38 AM, Jonathan Lewis wrote: > 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. Actually I think that is an outdated statement. AFAIK TSQL does at least have the concept of table parameters (which is roughly equivalent to an associative array of records). Another key difference of TSQL is that these select statements within a batch or procedure flow back to the client immediately. That is while the TSQL script is still executing. So when you invoke a procedure result are coming back long before the procedure completes. PRINT is a special case of that. This makes for a different approach to track progress compared to APPLICATION_INFO. Quite interesting, actually. Cheers Serge -- Serge Rielau SQL Architect DB2 for LUW IBM Toronto Lab
From: Jonathan Lewis on 25 Mar 2010 17:05 "Serge Rielau" <srielau(a)ca.ibm.com> wrote in message news:811vmcFklmU1(a)mid.individual.net... > On 3/25/2010 11:38 AM, Jonathan Lewis wrote: >> 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. > Actually I think that is an outdated statement. AFAIK TSQL does at least > have the concept of table parameters (which is roughly equivalent to an > associative array of records). > There are all sorts of parameters that a procedure can have, and lots of data types that a function can return - but the point I was making was that you can write a procedure that has NO formal parameter declaration that can still "return" data. In the absence of a formal declaration, how does the front end know what to do with the stream of data that gets generated. > Another key difference of TSQL is that these select statements within a > batch or procedure flow back to the client immediately. That, of course, is nearly the problem I was highlighting. The results of a select statement "flow back" to the client - but how is the client supposed to know how to process that stream of data. The procedure may not have any formal declaration that the client can use to decide how to handle that data. -- Regards Jonathan Lewis http://jonathanlewis.wordpress.com
From: joel garry on 25 Mar 2010 17:09 On Mar 25, 8:38 am, "Jonathan Lewis" <jonat...(a)jlcomp.demon.co.uk> wrote: > "Mladen Gogala" <n...(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 Lewishttp://jonathanlewis.wordpress.com I work in several languages that have implicit data conversions. This is generally considered "bad form," in that it leads to its own class of errors. I'd say the same applies to the current subject. Some things appear to become easier and more straightforward in the short term. It's a false economy. jg -- @home.com is bogus. http://www.sfgate.com/cgi-bin/article.cgi?file=/c/a/2007/09/30/MNDTSEMSJ.DTL&tsp=business
From: Serge Rielau on 25 Mar 2010 17:45 On 3/25/2010 5:05 PM, Jonathan Lewis wrote: > "Serge Rielau"<srielau(a)ca.ibm.com> wrote in message > news:811vmcFklmU1(a)mid.individual.net... >> On 3/25/2010 11:38 AM, Jonathan Lewis wrote: >>> 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. >> Actually I think that is an outdated statement. AFAIK TSQL does at least >> have the concept of table parameters (which is roughly equivalent to an >> associative array of records). >> > > There are all sorts of parameters that a procedure can have, and > lots of data types that a function can return - but the point I was > making was that you can write a procedure that has NO formal > parameter declaration that can still "return" data. In the absence > of a formal declaration, how does the front end know what to do > with the stream of data that gets generated. > > >> Another key difference of TSQL is that these select statements within a >> batch or procedure flow back to the client immediately. > > That, of course, is nearly the problem I was highlighting. The results of > a select statement "flow back" to the client - but how is the client > supposed > to know how to process that stream of data. The procedure may not have > any formal declaration that the client can use to decide how to handle that > data. How does Oracle handle an OUT parameter of ANYDATA? (As seen e.g. in DBMS_AQ) How is it different from a weakly typed refcursor? Clearly the resultset has to be self describing. I agree with you and Joel, that this is bad form btw. All I'm doing is pointing out that within themselves these features do have value and rarely ever is any vendor alone in allowing the user to shoot themselves in the foot. Cheers Serge -- Serge Rielau SQL Architect DB2 for LUW IBM Toronto Lab
From: Serge Rielau on 25 Mar 2010 17:47
... can't resist an NRA quote here: It's not features that kill applications, it's programmers. ;-) -- Serge Rielau SQL Architect DB2 for LUW IBM Toronto Lab |