Press "Enter" to skip to content

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

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.

Related Tutorial:

Vinish Kapoor

I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me, it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub and get notifications for new posts.

You may also like:

Comments are closed.