Re: PL/SQL HELP NEEDED!!!!

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 14 Jun 1999 12:22:55 GMT
Message-ID: <3765f392.752211@newshost.us.oracle.com>

A copy of this was sent to Barney Gumble <duff.drinker_at_cornerpub.FUDD-SUX> (if that email address didn't require changing) On Mon, 14 Jun 1999 14:07:31 +0200, you wrote:

>I'm tying to make a stored procedure that will create new user:
>
>CREATE OR REPLACE
>Procedure pMakeCust(fname IN VarChar2, lname IN VarChar2) AS
>
>DECLARE
> -- CustSeq is my sequence for key-generation.
> nCustNO Number := CustSeq.NextVal;
>
>BEGIN
>
> INSERT INTO
> Customers
> (CustNO, Firstname, LastName)
> VALUES
> (nCustNO, fname, lname);
>
>--Here I'll have to have several another INSERT INTO
>-- that will use same nCustNO
>
>END; -- procedure
>
>Why do I get error: "PLS-00103: Encountered the symbol "DECLARE" ?
>What am I doing wrong? Thanx in advance.
>
> BG :o)

because the declare doesn't belong there. the syntax is:

create or replace procedure <procedure-name><inputs>

AS | IS                                             -- as or is, doesn't matter

   <local variables/types/procedures>
BEGIN
   ....
   DECLARE
       <local variables/types/procedures>    begin

       ...
   end;
end;
/

The declare is only needed in:

• anonymous blocks that need variables, eg: declare x int; begin x := 5; end;
• when you want a new sub-block, a lower level of scope in a procedure, as the procedure template above shows. any variables defined in the declare block inside the procedure only exist inside that block.

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/

Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jun 14 1999 - 07:22:55 CDT