Can a procedure contain only a SELECT statement?
in [Oracle]
|
Prev: semvmx
Next: 10.2.0.5 Patchset
From: Jonathan Lewis on 25 Mar 2010 18:42 "Serge Rielau" <srielau(a)ca.ibm.com> wrote in message news:8123rpFe2uU1(a)mid.individual.net... > On 3/25/2010 5:05 PM, Jonathan Lewis wrote: >> "Serge Rielau"<srielau(a)ca.ibm.com> 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 etc. 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. -- Regards Jonathan Lewis http://jonathanlewis.wordpress.com
From: phil_herring on 25 Mar 2010 19:54 I'm not a SQL Server user, but some Googling turned up this page that discusses a generic handler for SQL Server result sets: http://stackoverflow.com/questions/1807899/c-function-to-return-generic-objects-entities Clearly, there are some library calls involved, but it looks possible, at least with C#. -- Phil
From: Galen Boyer on 26 Mar 2010 00:02 "Jonathan Lewis" <jonathan(a)jlcomp.demon.co.uk> writes: > 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. From the java perspective, a procedure executes, and because you called it, you know whether it returns a result set or not, so, in java you ask the result set object for the result set, so, to the java coder, the API works as expected. But, the issue seems to be exposed by the error checking that is forced upon the java codebase. Upon execution of a procedure within Sybase (and I guess in SQL Server) you have to iterate a stack of things. Each thing is tested as to whether it is a return code or not, and if it is a return code, then a boolean of yes or no tells you whether the procedure has errored or was successful. Within the procedure, an error does not actually bubble to the top as it does in Oracle, but, instead gets put on this stack of things. So, on this return stack is, exactly as you are questioning, multiple types of things. How is the client supposed to interpret these different things. My guess is that the implementation of the jdbc drivers iterate the same stack I just described looking for result sets, and then, return those things. That is what I know and am surmising from the java perspective. I would guess its the same in other client codebases -- Galen Boyer --- news://freenews.netfront.net/ - complaints: news(a)netfront.net ---
From: Jonathan Lewis on 26 Mar 2010 17:07 <phil_herring(a)yahoo.com.au> wrote in message news:f7f782cf-52db-4a34-9661-a9f0d52404f7(a)n20g2000prh.googlegroups.com... > I'm not a SQL Server user, but some Googling turned up this page that > discusses a generic handler for SQL Server result sets: > > http://stackoverflow.com/questions/1807899/c-function-to-return-generic-objects-entities > > Clearly, there are some library calls involved, but it looks possible, > at least with C#. > > > -- Phil Phil, Thanks for passing on an interesting link. -- Regards Jonathan Lewis http://jonathanlewis.wordpress.com
From: Jonathan Lewis on 26 Mar 2010 17:10
"Galen Boyer" <galen_boyer(a)yahoo.com> wrote in message news:uwrwzd92d.fsf(a)www.yahoo.com... > "Jonathan Lewis" <jonathan(a)jlcomp.demon.co.uk> writes: > >> 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. > > From the java perspective, a procedure executes, and because you called > it, you know whether it returns a result set or not, so, in java you ask > the result set object for the result set, so, to the java coder, the API > works as expected. But, the issue seems to be exposed by the error > checking that is forced upon the java codebase. Upon execution of a > procedure within Sybase (and I guess in SQL Server) you have to iterate > a stack of things. Each thing is tested as to whether it is a return > code or not, and if it is a return code, then a boolean of yes or no > tells you whether the procedure has errored or was successful. Within > the procedure, an error does not actually bubble to the top as it does > in Oracle, but, instead gets put on this stack of things. > > So, on this return stack is, exactly as you are questioning, multiple > types of things. How is the client supposed to interpret these > different things. My guess is that the implementation of the jdbc > drivers iterate the same stack I just described looking for result sets, > and then, return those things. > > That is what I know and am surmising from the java perspective. I would > guess its the same in other client codebases > > -- > Galen Boyer > > --- news://freenews.netfront.net/ - complaints: news(a)netfront.net --- Galen Thanks for that. I think your comments match the degree of complexity that I had thought might be necessary. I wonder if this means that people tend to write "simple" procedures that only output one type of record set, rather than the multiple types that the OP was asking about. -- Regards Jonathan Lewis http://jonathanlewis.wordpress.com |