Oracle 10g claims package body has errors when it doesn’t

Question

I have several packages on my oracle instance. When I recreate my database from scratch, I get zero invalid objects and no warnings from Liquibase.

Then, when I call the package method, for example:

new SimpleJdbcCall(jdbcTemplate).withCatalogName(packageName).withProcedureName(storedProcedureName).execute(parameterMap); 

This gives me the following error:

 org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call CALENDAR_MANAGEMENT.CREATE_SUMMARIES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; SQL state [72000]; error code [4063]; ORA-04063: package body "ETPDEV.CALENDAR_MANAGEMENT" has errors ORA-06508: PL/SQL: could not find program unit being called: "ETPDEV.CALENDAR_MANAGEMENT" ORA-06512: at line 1; nested exception is java.sql.SQLException: ORA-04063: package body "ETPDEV.CALENDAR_MANAGEMENT" has errors ORA-06508: PL/SQL: could not find program unit being called: "ETPDEV.CALENDAR_MANAGEMENT" ORA-06512: at line 1 Caused by: java.sql.SQLException: ORA-04063: package body "ETPDEV.CALENDAR_MANAGEMENT" has errors ORA-06508: PL/SQL: could not find program unit being called: "ETPDEV.CALENDAR_MANAGEMENT" ORA-06512: at line 1 

Checking for invalid objects again causes nothing. Compilation CALENDAR_MANAGEMENT again completes normally without errors.

I tried recompiling the full circuit, but that didn't work.

When I call it from SQLDeveloper, it works fine.

Answer 1

I encountered this error in my integration tests, and I did not think to check if the package was correct when it worked.

Removing a simple bit of code before calling the package:

 select name || ' - ' || line || ' - ' || text from user_errors order by name, sequence; 

I found that the other test did not clear after myself, but this one was, so when I looked at the database before and after, it looked great.