Here is the corrected code. Obviously, I cannot check whether it is doing what it is supposed to do. I just corrected obvious syntax errors. One more thing, when you get "Warning: Procedure created with compilation errors." , it means there are some errors in the stored procedure and you need to correct it before start using it. CREATE OR REPLACE PROCEDURE usp_Security_IsAuthorisedUser ( strUserName IN VARCHAR2, strPassword IN VARCHAR2, blnIsValidUser OUT BOOLEAN, strInfo OUT VARCHAR2) AS intCount NUMBER; BEGIN intCount := 0; SELECT COUNT(*) INTO intCount FROM USERS WHERE UserID = strUserName AND Password = strPassword; --If count = 1 then only user information is correct IF intCount = 1 THEN BEGIN blnIsValidUser := TRUE; intCount := 0; strInfo := 'SP'; SELECT COUNT(*) INTO intCount FROM UnitRights WHERE HoU = strUserName; IF intCount = 1 THEN strInfo := 'UH'; --EXIT; Invalid. Should be used on in the loop RETURN; ELSE SELECT COUNT(*) INTO intCount FROM GENCODES WHERE FLD_NAME = 'MARKET_REG' AND UDF_ST3 = strUserName; --If count = 1 then this is Region Manager IF intCount = 1 THEN strInfo := 'RM'; --EXIT; Invalid. Should be used on in the loop RETURN; END IF; END IF; END; ELSE BEGIN blnIsValidUser := FALSE; SELECT COUNT(*) INTO intCount FROM USERS WHERE CODE = strUserName; IF intCount = 1 THEN strInfo := 'PI'; ELSE strInfo := 'UU'; END IF; END; END IF; END; regards
