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.

Steven Feuerstein "down under"

For those "down under" in Australia who appreciate Steven Feuerstein PL/SQL offerings, Steve will be presenting in both Sydney and Melbourne for Quest Software on the 17th and 21st of January respectively.

It's a shame Steve can't make it to other Australia cities after coming all this way, but Oracle doesn't sell any licenses outside of Sydney and Melbourne anyhow, so it would be a pointless exercise I guess.

Feel free to wear *all* your Toad gear for the event too. I know I have 3 Toad hats, 7 Toad t-shirts, 4 Toad boxer shorts, 2 pairs of Toad socks.......

Check out the event and registration details here.

Disclaimer: this has nothing to do with AUSOUG. I just thought some local readers would like to know.

What features would you like to see in PL/SQL?

Every day I read posts that start "I was going to blog about X but Y beat me to it". It looks like it's my turn. I've been sitting on a post for improvements I'd like to see to PL/SQL, and it turns out Steven Feuerstein has set up a site to capture PL/SQL improvements we'd all like to see. Dag-nab-it!

Anyway, the gist of my original post follows, slightly rewritten to make it look like I didn't think of the idea first ;)

With the arrival of Oracle 11g we see a new set of features for PL/SQL. PL/SQL from my point of view has always been the ideal procedural language for the Oracle database, as many years ago I programmed with Ada which was the basis for PL/SQL syntax so I didn't have to learn a new language from scratch, I find the syntax very easy and clear to work with, and a breeze to teach (compared to Java for instance).

From experience, my positive outlook on PL/SQL is shared by a number of Oracle experts out there, with the main comment being "PL/SQL does what it needs to do." In other words it's a very mature language.

This doesn't mean that we shouldn't drive for further innovation in the PL/SQL language though, as there are many features in other languages that could make our PL/SQL programming experience more rewarding. If you look at the Java camp, committees upon committees, and blog entries upon blog entries are dedicated to improvements in the language for the next version. I can't say I've seen the same for PL/SQL, so Steve's initiative here is a good one.

So what improvements would you like to see in PL/SQL?

To start ball rolling, I'll suggest a couple and will post onto Steve's site:

1) I miss the ternary operator from other languages such as Java. The ternary operator takes the form:

(<boolean>) ? <return true expr> : <return false expr>;

So for a trivial example from Java:

boolean b = (1 < 2) ? true : false;

The ternary operator is really a short hand CASE or IF-ELSE-END IF statement. The power of the ternary operator is it supports expressions within each component, and expressions within expressions and so on. However the ternary operator is also responsible for some evil looking code because of this ability, so definitely it has its pros and cons.

2) To retrieve a sequence number in PL/SQL, you need to wrap this in an implicit SELECT-INTO statement or similar. What I really want to do is the following short-hand:

v_number := my_seq.nextval;

(and thanks to Justin Cave's response to my question on OTN, turns out this is available in 11g, as published in this AskTom article under the section "It's the little things". Neat!)

3) Assertions are a common feature in many languages beyond simple exception handling. They allow you to test a condition, and if it fails, stop the program. They are ideal for debugging programs because you can (for example) place the assertions at the beginning of each PL/SQL module to ensure incoming parameters are not null.

So what features would you like to see in PL/SQL? Why not head over to Steve Feuerstein's site and record your thoughts.