Oracle Error PLS-801: INTERNAL ERROR [pklsdlp:dependency verification] – Just thought I’d help anyone out there that was struggling with this error when calling a function on an Active Standby.
Oracle Database Enterprise edition 11G update 2 running Oracle’s Enterprise Linux 64bit 5.4. One Primary with multiple Active Standbys.
We found this nasty bug when we were using functions on an Active Standby. The PLS-801: INTERNAL ERROR [pklsdlp:dependency verification] would occur from time to time. Oracle’s response was flushing the cache. No help at all, it’s only a matter of time before the problem happens again. We found out that as soon as statistics is gathered for the table that is being used by the function, the problem will happen again. In our case every 5 days.
The root cause of the problem is the return type. We noticed that tablename.column%TYPE as return type will experience the problem, yet if you change the return type to VARCHAR2 for example, you WON’T experience the problem. This is a true workaround, rather than Oracle’s flushing of the cache. If you can change the return type, you shouldn’t have to worry about applying any patches to fix this. Which would usually result in a ton of testing and hassle for everyone involved.