ORA-04044: procedure, function, package, or type is not allowed here

In maintaining an existing system, we recently came across an interesting Oracle error in a PL/SQL module "ORA-04044: procedure, function, package, or type is not allowed here." This error raised itself when opening a cursor using dynamic SQL. As usual we all swore black and blue that we hadn't made any changes to the underlying schema, yet the dynamic SQL was now failing. It was the day before Christmas and we weren't happy! The problem had to be solved. I'll document it here for future reference and others to make use of.

The following SQL*Plus spool demonstrates the scenario where the error occurred:

SQL> CONN alpha/alphapwd@somedb
Connected.
SQL>
SQL> CREATE TABLE some_object
  2 (somefield NUMBER);

Table created.

SQL> GRANT SELECT ON some_object TO bravo;

Grant succeeded.

SQL> CREATE PUBLIC SYNONYM some_object FOR alpha.some_object;

Synonym created.

SQL> CONN bravo/bravopwd@somedb
Connected.
SQL>
SQL> CREATE OR REPLACE PROCEDURE test
  2 IS
  3   TYPE ref_cursor_type IS REF CURSOR;
  4   c_ref_cursor ref_cursor_type;
  5   v_sql VARCHAR2(1000) := 'SELECT 1 FROM some_object';
  6   v_dummy NUMBER;
  7 BEGIN
  8   OPEN c_ref_cursor FOR v_sql;
  9   FETCH c_ref_cursor INTO v_dummy;
 10   CLOSE c_ref_cursor;
 11 END;
 12 /

Procedure created.

SQL> EXEC test;

PL/SQL procedure successfully completed.

SQL> CREATE OR REPLACE PROCEDURE some_object
  2 IS
  3 BEGIN
  4   NULL;
  5 END;
  6 /

Procedure created.

SQL> EXEC test;
BEGIN test; END;

*
ERROR at line 1:
ORA-04044: procedure, function, package, or type is not allowed here
ORA-06512: at "BRAVO.TEST", line 8
ORA-06512: at line 1

SQL> DROP PUBLIC SYNONYM some_object;

Synonym dropped.

SQL> EXEC test;
BEGIN test; END;

*
ERROR at line 1:
ORA-04044: procedure, function, package, or type is not allowed here
ORA-06512: at "BRAVO.TEST", line 8
ORA-06512: at line 1

As you can see, the first time under which the method bravo.test containing our dynamic SQL is executed, the code works fine. However at a later date another coder introduced a procedure bravo.some_object which conflicts with alpha.some_object.

Normally the database would complain that you can't create same named objects and this protects developers against silly mistakes and having to be explicit about the object type they're selecting against. However as the objects here exist in different schemas, the database is happy with this and lets the developer proceed.

Once the some_object procedure is created and the bravo.test is run, it's dynamic SQL attempts to resolve SELECT 1 FROM some_object, and bravo.test discovers some_object is a procedure in the local schema (remembering the database's preference is to use local schema objects first), not the table from the original alpha schema. The database thus throws ORA-04044 at runtime, basically saying it wants a table or view, not a procedure, function, package or object type.

You can see from the developer's point of view the confusion when this error occurs, as nothing has changed in the bravo schema besides introducing a new procedure. Why would adding a new object cause this problem? And potentially this is even more confusing as the creation of the procedure could have been weeks ago, entirely forgotten, but only now the dynamic SQL is falling over.

On encountering this scenario we had a slightly more complicated problem to debug. In our case the alpha schema had been granted execute privileges on bravo.test. When the alpha schema executed bravo.test ORA-04044 is still thrown. However if you extract the SQL statement from the dynamic SQL call and run it within the alpha schema, the SQL statement will run fine! Talk about a red herring.

Our initial mistake was to forget about that PL/SQL modules are executed with definer-rights by default, meaning they are executed in the schema of the PL/SQL module owner, in test's case bravo, rather than alpha. As soon as we ran the SQL statement in the bravo schema, the same ORA-04044 occurred.

The obvious hack to fix this problem is to change the dynamic SQL statement to prefix the table name some_object with the schema dot notation (eg. SELECT 1 FROM alpha.some_object). In this way regardless if the dynamic SQL is run in the bravo schema, it's guaranteed to retrieve results from the alpha schema.

However some developers and DBAs don't like prefixing queries with schema dot notation for flexibility. On thinking about this I guess an alternative solution that will reduce the chance of the problem occurring is to ensure your object names have some sort of prefix or postfix notation. For example p_ for procedures, f_ for functions, _tbl for tables etc. This sort of notation I note is falling out of favour at a number of sites, but in this situation could have saved us much time debugging.

I'd also be interested in other methods anybody thinks would have avoided this issue in the first place.