0

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?

0

1 Answer 1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.