Re: Can a procedure contain only a SELECT statement?

From: Jonathan Lewis <>
Date: Thu, 25 Mar 2010 22:42:39 -0000
Message-ID: <>

"Serge Rielau" <> wrote in message
> On 3/25/2010 5:05 PM, Jonathan Lewis wrote:
>> "Serge Rielau"<> wrote in message
>>> 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'm not being judgemental (yet) - I just want to know how it works.

From what you're saying it seems the programmer who calls the procedure has to write the code to unpack the data stream correctly, potentially writing some very generic code to handle a stream that says things like:

    The next 10 records are column names

        name1, name2, ...
    The next 10 records are column types

        date, varchar2(10) ...
    The next data stream is an array of rows terminated by end-of-data


Is this vaguely correct ? I assume there are some library routines (like the Oracle pl/sql obind, odesc, etc.) to eliminate the drudge work of connecting the stream to the local arrays, of course.


Jonathan Lewis
Received on Thu Mar 25 2010 - 17:42:39 CDT

Original text of this message