I would like to create a function that queries a user table by passing a username and a password, the function is supposed to return 'Y' if the username / password are correct and 'N' if they're not.
the return values is initialized to 'N' and i check if the function found a match using the SQL%FOUND to assign a 'Y' to teh return variable.
i get the following error: 06503. 00000 - "PL/SQL: Function returned without value"
CREATE OR REPLACE FUNCTION VERIFY ( userName IN VARCHAR2, userPassword VARCHAR2 ) RETURN VARCHAR2 AS returnVar VARCHAR2(1):='N'; userID number; zip number; BEGIN SELECT userId,SUBSTR(ZIPCODE, 1,3) INTO userID,zip FROM users WHERE username = userName AND PASSWORD = userPassword; if SQL%FOUND then returnVar:='Y'; end if; return returnVar; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('NO DATA FOUND'); END VERIFY;
NOTE: also in case you are wondering why i select the userID and the zip: when the function works fine and tested, i will include it into a package inorder to make the userID and Zip Code available globally, this is a requirement of the exercise i am working on.