Stored Procedure in Oracle 8i
-
Hello friends, I want to create 1 stored procedure in Oracle. I have written it & when trying to execute, its giving some error message...i am using Ora 8i. While creating SP, Oracle gave me error msg :- "Warning: Procedure created with compilation errors." :doh::doh::doh: Will anybody please help me in solving the issue??? Here I am giving that SP... Looking forword to see your reply.. ;) Thanks in advance... Regards, Rohan CREATE OR REPLACE PROCEDURE usp_Security_IsAuthorisedUser (strUserName IN VARCHAR2(40), strPassword IN VARCHAR2(16), blnIsValidUser OUT BOOLEAN, strInfo OUT VARCHAR(10)) AS BEGIN DECLARE intCount NUMBER; 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; 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; 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;
-
Hello friends, I want to create 1 stored procedure in Oracle. I have written it & when trying to execute, its giving some error message...i am using Ora 8i. While creating SP, Oracle gave me error msg :- "Warning: Procedure created with compilation errors." :doh::doh::doh: Will anybody please help me in solving the issue??? Here I am giving that SP... Looking forword to see your reply.. ;) Thanks in advance... Regards, Rohan CREATE OR REPLACE PROCEDURE usp_Security_IsAuthorisedUser (strUserName IN VARCHAR2(40), strPassword IN VARCHAR2(16), blnIsValidUser OUT BOOLEAN, strInfo OUT VARCHAR(10)) AS BEGIN DECLARE intCount NUMBER; 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; 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; 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;
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