PLS-00801 internal error and ORA-06544 pl/sql internal error

After giving the finishing touches to a PL/SQL procedure that had been haunting me for the last days, consisting primarily of one BIG implicit cursor with several subqueries and a convoluted logic, i was finally ready to compile the program when i got this terrifying error message:

Line: 1 Column: 3  Error: PLS-00801: internal error [ph2csql_strdef_to_diana:bind]
Line: 7 Column: 12  Error: PL/SQL: ORA-06544: PL/SQL: internal error, arguments: [ph2csql_strdef_to_diana:bind], [], [], [], [], [], [], []
Line: 7 Column: 5  Error: PL/SQL: SQL Statement ignored

Once i recovered from the shock, i soon realized that there must be some junk i left in the SQL statement that i was converting from the sqlplus format (where i developed and tested it) into a programmatic format, that is replacing substitution variables with procedure parameters and variables.

From my past experience i know that when Oracle 9i complaints with a PL/SQL parser internal error, although the manual suggests to call Oracle Support and report a supposed bug, usually it's much ado about nothing, it's just a matter of a construct that the parser doesn't like.

Note that the parser doesn't point us to the precise spot where it found a problem, but it reports it as a generic problem regarding the whole SQL statement (line 7, where my UPDATE begins) , so it's up to us to find out where the offending bit is located.

...
begin
...
UPDATE                        -- at line 7
...
AND L1.TYPE_CODE = 'MK'       -- at line 25
AND L2.TYPE_CODE = 'RE'
AND AM.MD_NBR = &num_model_nbr
AND L1.LOC_CODE = L2.LOC_CODE
AND AM.MD_CODE = 'WK'
AND MOG.USER_ID = i_var_user
AND MOG.CONFIG_FLAG = 'X'
AND MOG.MKT_NBR = L2.LOC_NBR
AND MOG.PL_NBR = AM.PL_NBR
...
end;

In my case, it was just a sqlplus substitution variable that i forgot to replace with its corresponding parameter, however i remember i saw this happen more than once with certain SQL statements involving function THE (aka TABLE function) inside triggers.

If you want to artificially recreate this problem, try to execute the following anonymous PL/SQL block:

begin
update dual
   set k = k
 where k = &test;
end;

In conclusions there are situations where the PL/SQL parser seems to say: "hey look, i know there is a problem with your SQL but i can't make out exactly what it is, i give up, sorry!"

The good news is that at least Oracle doesn't freeze and doesn't force you to press CTRL-ALT-DEL to restart!

See message translations for ORA-06544 and search additional resources.