ORA-20999: PL/SQL Function Body Did Not Return a Value - SOLVED

Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

April 26, 2023
OrclApex
1 min read

In Oracle Apex, you are trying to create a PL/SQL function body to return an SQL query, but you are getting the error ORA-20999: PL/SQL function body did not return a value.

This is because you are definitely creating and returning an SQL query within an IF condition.

To resolve this error, you must create and return the SQL query in the ELSE part of the IF condition. Below is a correct example:

Declare
   v_sql varchar2(500);
Begin
   If :p2_status = 'A' then
      v_sql := 'Select col1 d, col2 r from your_table where status = ''A''';
   Else
     v_sql := 'Select null d, null r from dual';
   End If;

Return v_sql;
End;

Now, this method will work and will not give an error because we have written the SQL query for the ELSE part too.