Encountered the symbol declare when creating a stored procedure

Question

The following procedure is not being created in Oracle SQL Developer

 CREATE OR REPLACE PROCEDURE CheckUser(UserName IN VARCHAR2,Password IN VARCHAR2)
  AS
 DECLARE Counts int;
 BEGIN 
    SELECT COUNT(UserNames) INTO Counts FROM tblUsers
    WHERE UserNames = UserName and Passwords = Password;
  IF Counts = 1 THEN
       SELECT 1 AS Code;
  ELSE
      SELECT -1 AS Code;
 END;

When I run above procedure the following error message is returned in SQL Developer:

PROCEDURE CHECKUSER compiled
Errors: check compiler log

Error(3,1): PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior external language

score 1 · Answer 1 · edited May 23 '17 at 12:20

To actually go over your errors:

The procedure declaration CREATE OR REPLACE... is the DECLARE block; you can remove the DECLARE; see the documentation for more information.
You need to select from something, this is normally the table DUAL, which has been designed for this purpose, i.e.
```
select 1 as code from dual
```
If you're selecting data in a procedure you need to SELECT INTO a variable. You do this the first time but not the second, i.e.
```
select 1 into <some variable> from dual
```
INT is not a datatype; it's INTEGER, which is a synonym for NUMBER(38,0
As far as I can tell you're not actually using the return code at all... I assume you're authenticating users here, which means you need to tell the calling program whether it was successful or not.
If you want to return a value the you probably want a function, as opposed to a procedure.
To take this to it's logical conclusion, your IF statement is unnecessary; the COUNT(*) will return 1 or 0 depending on whether the username and password exist... use this as a Boolean True/False instead.
I hope this is a password hash and not the actual password...
It's often better to be explicit about naming conventions and separate out parameters from column names etc to make it easier to read and less likely to cause Oracle to choke on the scope.

Putting all this together you end up with something like this:

create or replace function check_user (
    PUsername in varchar2, PPassword_Hash in varchar2
     ) return number is

   l_exists number;

begin

   select count(*) into l_exists
     from tblUsers
    where username = PUsername
      and password = PPassword_Hash
          ;

   return l_exists;

end;
/

It's worth noting that your method of authentication is only safe if you ensure that people can only have one username, i.e. if TBLUSERS has a unique constraint on the column USERNAME. If it doesn't you do need some other method of uniquely identifying each user in your database, otherwise you could end up logging in people as a different user than they actually are.

Encountered the symbol declare when creating a stored procedure

1 Answers1