PL/SQL Exception Handling - Function Returned without value

Question

In Oracle 12.1, I have a reasonably simple PL/SQL routine. The output is a single value, either a comma separated list of names, or the string 'NO_DATA'. There are no other outputs allowed. For input, there is a single value, a company name. If I hard code a company name, and run the SQL statement (not the function) from a SQL window, it runs fine, so I know the SQL is valid. The issue has to do with the exception handling. If I have NO EXCEPTION HANDLING AT ALL, and pass a valid name to the function, it gives me a valid output. I need to be able to handle the situation when no data is found, so I add simple exception handling. This is where I get my issue. With the Exception Handling code in place, if I pass in a bad value (aka company name that is not found), I get 'NO_DATA', just like I should. If I pass in a good value, I get a PL/SQL error ORA-06503: PL/SQL: Function returned without a value. Here is my code.

   create or replace FUNCTION authorized_email(nn1 in varchar2)
   RETURN varchar2
   IS  
   thisName varchar2(4000);
   Output varchar2(4000);

-- this routine build the list of comma seperated authorized users   

BEGIN

 SELECT NN_Name,      
        nvl(replace(Upper(LISTAGG( Name, ',' ) WITHIN GROUP ( ORDER BY Name )), '@XYZ.COM', NULL), 'NO_DATA') AS Names
       into thisName, Output
FROM   (
  SELECT DISTINCT(NN_NAME),
         Name
  FROM   LINE_ITEMS
  UNPIVOT( name FOR typ IN ( 
        FMW_MGR_L3_EMAIL,
        FMW_MGR_L4_EMAIL,
        FMW_MGR_L5_EMAIL,
        FMW_MGR_L6_EMAIL,
        FMW_MGR_L7_EMAIL,
        FMW_EMAIL,
        HYBRID_MGR_L3_EMAIL,
        HYBRID_MGR_L4_EMAIL,
        HYBRID_MGR_L5_EMAIL,
        HYBRID_MGR_L6_EMAIL,
        HYBRID_MGR_L7_EMAIL,
        HYBRID_REP,
        TECH_MGR_L3_EMAIL,
        TECH_MGR_L4_EMAIL,
        TECH_MGR_L5_EMAIL,
        TECH_MGR_L6_EMAIL,
        TECH_MGR_L7_EMAIL,
        TECH_EMAIL) 
    ) )
where NN_NAME = nn1
GROUP BY NN_NAME;

EXCEPTION
  WHEN no_data_found then    
    Output := 'NO_DATA';

return Output;


END;

There is something wrong with my EXCEPTION HANDLING code, but I cannot determine what it is. Any help appreciated!

Dave Costa · Accepted Answer · 2016-08-20 14:57:47Z

4

Your confusion is here:

EXCEPTION
  WHEN no_data_found then    
    Output := 'NO_DATA';

return Output;

The WHEN clause doesn't terminate until it hits another WHEN or and END. So your return Output; is part of the exception handler, not part of the main body of the code. (The way you've indented the code may create the impression that the return statement is outside the exception handler, but the compiler doesn't care about that, just about the defined syntax.)

I would suggest a slightly different fix than in the other answer, to avoid having multiple return statements. You can nest BEGIN/END blocks to accomplish the flow you want:

BEGIN
  BEGIN
    ... SQL statement here...
  EXCEPTION
    WHEN no_data_found then    
      Output := 'NO_DATA';
  END;

  return Output;
END;

answered Aug 20, 2016 at 14:57

Dave Costa

48.3k8 gold badges61 silver badges73 bronze badges

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

Comments

Luc M · Accepted Answer · 2016-08-20 14:52:01Z

2

Before the exception, you don't return any value.

Then you should add

return Output;

The statements below the EXCEPTION

Output := 'NO_DATA';
return Output;

are only executed when a NO_DATA_FOUND is triggered.

So, your code should be like

BEGIN
        SELECT NN_Name,      
                nvl(replace(Upper(LISTAGG( Name, ',' ) WITHIN GROUP ( ORDER BY Name )), '@XYZ.COM', NULL), 'NO_DATA') AS Names
               into thisName, Output
        FROM   (
          --skipped) 
            ) )
        where NN_NAME = nn1
        GROUP BY NN_NAME;
       return Output;   --     <---   code added
    EXCEPTION
      WHEN no_data_found then    
        Output := 'NO_DATA';
        return Output;
    END;

edited Aug 20, 2016 at 14:52

answered Aug 20, 2016 at 14:47

Luc M

17.5k26 gold badges78 silver badges91 bronze badges

2 Comments

user5683823 Over a year ago

So, you also need the return Output; before the EXCEPTION block.

user1009073 Over a year ago

That was my culprit. Thanks!

Collectives™ on Stack Overflow

PL/SQL Exception Handling - Function Returned without value

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related