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?

Joe Obbish · Accepted Answer · 2016-10-14 16:58:01Z

4

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.

edited Oct 14, 2016 at 16:58

answered Oct 14, 2016 at 16:30

Joe Obbish

33.3k4 gold badges77 silver badges156 bronze badges

Add a comment |

Stack Exchange Network

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

1 Answer 1

Your Answer

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

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related