Using `SELECT` as `RETURN` Argument in Function?

Question

Using Oracle, I attempted to write a function that demonstrates how to return the result of a SELECT ... query:

 CREATE OR REPLACE FUNCTION f(v1 number) RETURN number IS
 BEGIN
 IF TRUE THEN
 RETURN (select 42 from dual);
 ELSE
 RETURN v1;
 END IF;
 END;

But it does not compile.

How can I fix the above IF's RETURN to return 42, i.e. the result of select 42 from DUAL?

Answer 1

Save the result of your query into a variable and return the variable. For example:

CREATE OR REPLACE FUNCTION test_f(v1 number) RETURN number IS
v2 number;
 BEGIN
 IF TRUE THEN
 SELECT 42 into v2 from dual;
 RETURN v2;
 ELSE
 RETURN v1;
 END IF;
 END;

Be sure that your query always generates exactly one row. Otherwise you could get an error if no rows returned or if more than one row is returned.