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

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.