Firstly I should state that we have solved this problem but we still don't know why it happened so we are still looking for answers. The problem occurred on Monday where a packaged function was failing with the following error:
ORA-06540: PL/SQL: compilation error
ORA-06553: PLS-907: cannot load library unit SAP_TABLE1@DBLINK
The function looked up a description column in SAP_TABLE2@DBLINK but did use column types to SAP_TABLE1@DBLINK in the specification. We were able to run the select statement contained in the function without an issue but a call to the function failed with the same error from SQLPlus.
The issue was resolved by recompiling the package containing the function.
After speaking to the SAP DBAs they said that SAP_TABLE1 was moved from one table space to another over the weekend however I have since tested this scenario, by dropping and recreating the table, and was not able to reproduce the error.
Can anyone suggest another reason for us getting this error?
Regards
Brummoi