Friday, May 04, 2007

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.

12 comments:

Anonymous said...

By the way, this can be a transient error as well. It seems to be an "catch-all" type of error. We've encountered it on Oracle 10g.

Stew said...

Thanks for this! I was getting this strange error, had no clue, spent an hour trying to simplify the huge cursor that an analyst had given me. It turned out that, like you, there was an '&' left from the analyst's tests!

Thanks very much for explaining this simple problem that looks SO ugly!

Anonymous said...

Thanks... You advice on this error was right on target.

Anonymous said...

Thanks - you saved me a lot of time with this advice!

Anonymous said...

Thanks - you saved me a lot of time too!!!

Miguel Fornari said...

Very helpful. In my case was a simple space in a trigger.

: new.column

that space between : and new.

Tks a lot

Anonymous said...

thanks~! as soon as i saw "&" in your post, i knew the problem. i just searched for & and took care of it. : )

thanks again!

Anonymous said...

Thanks for this!

Korepetycje said...

Good advice, time saving. Thank You.

Nidhi said...

thanku u much in my case it was a simple space in a trigger.

gioalhaz said...

Comment was very helpful :) Thanks

Anonymous said...

Here's a similar/worse situation with an internal error where the compiler gives no clue at all as to where the problem is:


create type t as table of number;

declare
type r is record( n t );
type w is table of r;
v w;
begin
select null bulk collect into v from dual;
end;
/

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PLS-00801: internal error [ph2csql_string_type:charset]

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles