PL/SQL – How to Correctly Return Values from a Function

functionsplsqlselect

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.

Best Answer

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

Related Solutions

How to create a function(table_name) that return a collection in Oracle

I haven't syntax checked this but it should put you on the right path.

This should be put in a package but for demonstration purposes...

PROCEDURE GET_COLUMNS(table_name_in IN VARCHAR2, v_query_out OUT sys_refcursor)
IS
v_count NUMBER(9);
CURSOR the_columns IS 
Select COLUMN_NAME 
from all_tab_columns 
where table_name='table_name_in';
v_query VARCHAR2(4000):= 'Select  ';
BEGIN
IF table_name IS NOT NULL THEN
--requires permission to access dba_tables, if not available use ALL_TABLES
--or USER_TABLES
   SELECT count(*)
   INTO v_count  
   FROM dba_tables
   where table_name = table_name_in;
--might need the schema name too in a multi schema environment
   IF v_count <> 1 THEN
      RAISE bad_table_name;
   END IF;
ELSE
     RAISE bad_table_name;
END IF;

for all_columns in the_columns LOOP
v_query := vquery || all_columns.COLUMN_NAME ||',';
END LOOP;
--trim off the last comma
v_query := SUBSTR(v_query, 1, LENGTH(v_query) - 1);
v_query := v_query ||' from ' || table_name_in;

OPEN v_query_out for v_query;

EXCEPTION
when bad_table_name THEN
--log an error message
DBMS_OUTPUT.PUT_line('Table name is not accessible or does not exist');
RAISE; --could raise an custom exception if more detailed logging required
END;

This should work on Oracle 9,10 and 11 and could be simplified for 12 by using RETURN_RESULT.

How to raise_application_error beyond a when others clause

There are a variety of approaches you could take. You should pick the one you feel is most suitable for your application.

Include no WHEN OTHERS exception handlers and let the raw Oracle errors propagate to your users.
Include WHEN OTHERS exception handlers in the top-level blocks.
Include WHEN OTHERS exception handlers in all/most blocks

The first option requires no effort on your part, but can expose users to very unfriendly error messages. They can also expose details about the database which could be of use to hackers, etc.

The second option require some effort. You can use the built-in packages, such as DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to identify where an exception occurred, which can be included in any logging processes you may have. You can convert the raw Oracle errors into application specific ones.

The third option requires most effort. However, as well as the above, you can also log items such as variable values to help you identify why an error occurred, not just where it occurred. Using some common exception handling routines -as you seem to be doing - will reduce the effort required for this approach.

WHEN OTHERS THEN
   exception_pkg.handler('<variable_values_of_interest>')
   RAISE;

where you pass the variables values to be logged via a standard process before re-raising the exception.

You should always re-raise an exception in a WHEN OTHERS block, either the original exception or one with an application specific message - via RAISE_APPLICATION_ERROR.

Best Answer

Related Solutions

How to create a function(table_name) that return a collection in Oracle

How to raise_application_error beyond a when others clause

Related Question