CODEWITHSUNDEEP
sqlfunctionselectoracle11g
joe
joe

Reputation: 556

how to return a value from a function in PL SQL

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.

Upvotes: 1

Views: 266

Answers (1)

PrR3
PrR3

Reputation: 1246

I think your problem is located in the exception block.

If an exception occours, you return just nothing.

Insert for example RETURN returnVar; at the end of the exception block to return your default value in case of an exception.

Upvotes: 1

Related Questions