PLS-00801: internal error [hshuid:LU invalid] when compiling a package body

This was a strange one.  Attempts at recompiling the following package body resulted in this PLS-00801 error.

COL owner FOR A20 
COL object_name FOR A40 
BREAK ON object_name 

SELECT owner, 
       object_name, 
       object_type, 
       status, 
       created, 
       last_ddl_time 
FROM   dba_objects 
WHERE  object_name = 'PKG_FINANCIALS_WORK'
ORDER  BY status, object_type 
/

OWNER                OBJECT_NAME                              OBJECT_TYPE         STATUS  CREATED              LAST_DDL_TIME
-------------------- ---------------------------------------- ------------------- ------- -------------------- --------------------
FINANCE              PKG_FINANCIALS_WORK                      PACKAGE BODY        INVALID 16-SEP-2010 13:19:10 19-OCT-2016 10:28:37
FINANCE                                                       PACKAGE             VALID   16-SEP-2010 13:18:54 19-OCT-2016 09:56:14

ALTER PACKAGE PKG_FINANCIALS_WORK COMPILE BODY;

Warning: Package Body altered with compilation errors.

SHOW ERRORS
Errors for PACKAGE BODY PKG_FINANCIALS_WORK:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
0/0      PLS-00801: internal error [hshuid:LU invalid]

I stumbled across this MOS note (1194683.1) which suggests several possible causes, but the 4th point relating to views was the underlying cause in this particular case…

BREAK ON OWNER SKIP 1
COL OBJECT_NAME FOR A30

SELECT o.owner,
       o.object_name,
       o.status,
       o.last_ddl_time
FROM   dba_objects o,
       dba_dependencies d
WHERE  o.owner = d.referenced_owner
       AND o.object_name = d.referenced_name
       AND d.name = 'PKG_FINANCIALS_WORK'
       AND d.owner = 'FINANCE'
       AND d.referenced_type = 'VIEW'
ORDER  BY 2 DESC
/ 

OWNER                          OBJECT_NAME                    STATUS  LAST_DDL_TIME
------------------------------ ------------------------------ ------- --------------------
FINANCE                        VW_DEBT                        VALID   22-JUL-2014 22:30:37
                               VW_OVERDUE_INVOICE             VALID   22-JUL-2014 22:30:37
                               VW_CREDIT                      VALID   30-AUG-2016 16:04:07
                               VW_ACCOUNTS                    VALID   30-AUG-2016 16:04:07

As you can see, all the views were valid, but I opted to force a recompilation of them anyway…

SELECT 'ALTER VIEW '
       ||referenced_owner
       ||'.'
       ||referenced_name
       ||' COMPILE;' COMPILE_CMDS
FROM   dba_dependencies
WHERE  name = 'PKG_FINANCIALS_WORK'
       AND owner = 'FINANCE'
       AND referenced_type = 'VIEW'
       AND referenced_link_name IS NULL
ORDER  BY 1 DESC
/

COMPILE_CMDS
---------------------------------------------------------
ALTER VIEW VW_DEBT COMPILE;
ALTER VIEW VW_OVERDUE_INVOICE COMPILE;
ALTER VIEW VW_CREDIT COMPILE;
ALTER VIEW VW_ACCOUNTS COMPILE;

The views compiled successfully, and when I attempted to recompile the package body again this time, it compiled without any issue!

ALTER PACKAGE PKG_FINANCIALS_WORK COMPILE BODY;

Package body altered.

Happy days 🙂

Leave a comment

Your e-mail address will not be published. Required fields are marked *