Re: SQLPLUS and PL/SQL

corby hudnall wrote:
>
> Hi all, I'm a student taking an intro database class and I have a professor
> that either doesn't know or doesn't care to know anything about what he's
> "teaching" us. I am trying to figure out PL/SQL with only the online help
> to guide me and I'm having some difficulty. Specifically, SQLPlus won't
> recognize BEGIN or DECLARE as valid SQL statements. When I try this:
>
> CREATE PROCEDURE simple ( value IN INTEGER ) AS
> BEGIN
> RETURN;
> END simple;
> /
>

First, you cannot use a 'RETURN' statement in a PROCEDURE. If you want to use the 'RETURN' statment, you must declare a function:

CREATE FUNCTION simple ( value IN number) as ...

Second, you can only declare 'anonymous' pl-sql blocks in sqlplus. To remedy this restriction, you have two options:

1. Don't name your procedure (here is an example):

declare

        my_index number; -- a local variable with procedural scope only
begin

	select my_data
	  from my_table
	 where my_index = 1;

2. Put your named procedure into a package. This is a better idea if you have the 'create' privilege on your Oracle server. This way, you can store the procedure in the database. It won't be recompiled each time you want to run it.

> I get an error at the opening paren stating it's not a valid SQL statement.
> I've tried anonymous blocks as simple as
>
> BEGIN
> END;
>
> but again, I'm told that BEGIN is not a valid SQL statement. Stabbing in
> the dark has gotten me no where so any direction you can offer is GREATLY
> appreciated!
>

You just need to have the declare statment come first.

> Corby S. Hudnall Senior, Spring 1998
> Colorado State University http://WWW.CS.ColoState.EDU/~hudnall
> Department of Computer Science hudnall_at_CS.ColoState.EDU

P.S. Even though you attend CSU and I attended University of Wyoming, I still gave you advice! :-p

Go WYO! Beat CSU!

Cheers.

-- 
Jeffery C. Cann
Senior Software Engineer
Fairway Systems, Inc.