how to return a value from a function in PL SQL

Question

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.

PrR3 · Accepted Answer · 2014-04-02 19:05:44Z

1

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.

answered Apr 2, 2014 at 19:05

PrR3

1,2561 gold badge19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

joe Over a year ago

that's correct, i just run the function body in an anonymous block, and this is exactly what was happening. thanks a lot

Collectives™ on Stack Overflow

how to return a value from a function in PL SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related