Re: SQLPLUS and PL/SQL

Actually, if all you had was:
BEGIN
END; the SQL*Plus client will give you an error. You need to have any valid statement within the block. Just:
NULL;
is a valid statement, so:
BEGIN
   NULL;
END;
/
will just do nothing, but still count as executing an anonymous block. You only need the DECLARE if you are going to declare anything: variables, cursors, etc.

Hope this helps a bit.

Chi

Jeffery Cann wrote:

> 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;
> end
>
> 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.
Received on Thu Mar 05 1998 - 00:00:00 CET