Re: Can a procedure contain only a SELECT statement?

"Thomas Gagne" <TandGandGAGNE_at_gmail.com> wrote in message news:tqadnRsfhsIjGjzWnZ2dnUVZ_qudnZ2d_at_wow.com...
> I'm looking around for Oracle 10g CREATE PROCEDURE syntax and looking for
> examples with simple SELECT statements inside. I see a lot of DML but
> none with simple SELECT statements.
>
> I'm thinking something like
>
> create or replace procedure aSimpleSelect (aLikeValue char(4)) as
> begin
> select *
> from aTableName
> where aColumn like aLikeValue;
> end; /
>
> But when I try creating it inside 10g it complains:
>
> PLS-00103: Encountered the symbol "(" when expecting one of the
> following: :=), default varying character large the symbol ":=" was
> substituted for "(" to continue.
>

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 notcount 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.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com