Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

You should return something when nothing is found. Before exception comment in the statement.
The way your routine is written it may does find nothing and then hits the end of the function without returning anything, hence the error function returned without value.
Workaround :
Handle it with the exception

exception
   when TOO_MANY_ROWS then
        return 'Error bla bla '; -- do something meaningful here 
   when NO_DATA_FOUND then
        return null; -- if you want to return null on nothing found
share|improve this answer
    
how? the variable is already set to 'N', default value. edit: ok makes sense now i'll give it a try thanks –  joe Apr 2 at 18:37
    
the error only happens when no match is found, so if i remove the exception handling, it returns null as value!!! –  joe Apr 2 at 18:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.