Why can't I call my function in a SQL statement?

Question

I create a package that executes my function in a SELECT statement right inside that same page:

CREATE OR REPLACE PACKAGE p
   AUTHID DEFINER
IS
   FUNCTION f
      RETURN NUMBER;
END;
/

CREATE OR REPLACE PACKAGE BODY p
IS
   FUNCTION ff RETURN NUMBER
   IS
   BEGIN
      RETURN 73;
   END;

   FUNCTION f RETURN NUMBER
   IS
      l_number   NUMBER;
   BEGIN
      SELECT 42 INTO l_number
        FROM DUAL
       WHERE ff () = 73;

      RETURN l_number;
   END;
END;
/

BEGIN
   DBMS_OUTPUT.put_line (p.f);
END;
/

But when I try to execute that function, I get the PLS-00231 compile error:

BEGIN
   dbms_output.put_line (P.F);
END;
/

PLS-00231: function 'FF' may not be used in SQL

The function is declared right there. It doesn't execute any non-query DML. It doesn't have any PL/SQL-specific datatypes in the header.

Why can't SQL use/see it?

Answer 1

Here's the bottom line:

If you want to call a function from within a SQL statement, then it must declared at the schema level (CREATE FUNCTION) or be defined in the specification of a package. This is true even if your SQL statement is inside a subprogram in the same package as the function called.

So if I change the package specification as follows, exposing the the formerly-private function, all is well:

CREATE OR REPLACE PACKAGE p AUTHID DEFINER
IS
   FUNCTION f RETURN NUMBER;
   FUNCTION ff RETURN NUMBER;
END;
/

And notice, interestingly, that you do not have to qualify the name of the function with the package name, inside the static SQL statement. The PL/SQL compiler sorts that out before passing the statement over to the SQL engine.

If you execute the SQL dynamically, however, the package name will be needed, as in:

FUNCTION f RETURN NUMBER
IS
   l_number NUMBER;
BEGIN
   EXECUTE IMMEDIATE 
      'SELECT 42 FROM DUAL WHERE p.ff () = 73'
      INTO l_number;

   RETURN l_number;
END;