Re: Can a procedure contain only a SELECT statement?
From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Thu, 25 Mar 2010 17:45:29 -0400
Message-ID: <8123rpFe2uU1_at_mid.individual.net>
On 3/25/2010 5:05 PM, Jonathan Lewis wrote:
> "Serge Rielau"<srielau_at_ca.ibm.com> wrote in message
> news:811vmcFklmU1_at_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.
Date: Thu, 25 Mar 2010 17:45:29 -0400
Message-ID: <8123rpFe2uU1_at_mid.individual.net>
On 3/25/2010 5:05 PM, Jonathan Lewis wrote:
> "Serge Rielau"<srielau_at_ca.ibm.com> wrote in message
> news:811vmcFklmU1_at_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 LabReceived on Thu Mar 25 2010 - 16:45:29 CDT