Re: Can a procedure contain only a SELECT statement?
Date: Sun, 21 Mar 2010 12:21:47 -0000
"Thomas Gagne" <TandGandGAGNE_at_gmail.com> wrote in message
> 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
> 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
set notcount on
select name from sys.schemas;
select name, physical_name
set nocount off
From (say) sqlcmd you can now type:
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.
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.
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
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
ignorance of how you are expected to use procedures in application code written
for SQL Server.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.comReceived on Sun Mar 21 2010 - 07:21:47 CDT