Re: Can a procedure contain only a SELECT statement?

From: Jonathan Lewis <>
Date: Thu, 25 Mar 2010 13:01:58 -0000
Message-ID: <>

"Jonathan Lewis" <> wrote in message
> 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.


Jonathan Lewis
Received on Thu Mar 25 2010 - 08:01:58 CDT

Original text of this message