Re: Can a procedure contain only a SELECT statement?

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

Oh boy. This is very wrong. First, if you want to do a simple select and just change the values of string, you can do it with bind variable. Second, a procedure is procedural. It has variables, scope, an entry point and an exit point. It is meant to do something. The verb "to do" is the key here. If you need just to return value, you need a function. If you need to return a query, the proper data type is cursor. This procedure of yours is also incorrectly formatted. Camel notation usually gets obliterated by the first formatter that gets hold of your code, be it SQL*Developer or that amphibian thingy that some people use. PL/SQL is not case sensitive like Java, so the camelNotationDoesNotLookGood. Everything will get blurred into an enormous unreadable string. Use "_" to separate words. Also, use meaningful variable names. Prefix variables with "v_".

-- 
http://mgogala.byethost5.com