Clarity: Datamart Extraction Job Fails due to error PLS-00905: object NBI_EXTRACT_SP Is Invalid

Document ID : KB000049372
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

Datamart extraction fails, and the bg-niku log indicates that the stored procedure NBI_EXTRACT_SP is invalid:

ERROR 2011-08-29 10:50:54,187 [Dispatch Thread-77 : bg@server1234] niku.njs
(none:none:none) Error executing job: 5000024
com.niku.union.persistence.PersistenceException:
SQL error code: 6550
Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-06550: line 1,
column 7:
PLS-00905: object NIKU.NBI_EXTRACT_SP is invalid
ORA-06550: line 1, column 7: PL/SQL: Statement ignored

On the application server, in a command prompt, we ran "nikuadmin db compile" and received the output indicating more invalid objects:

8/29/11 11:56 AM (admin) Command: compile
8/29/11 11:56 AM (admin) Compiling the database...
8/29/11 11:56 AM (admin) Following object(s) are invalid:
8/29/11 11:56 AM (admin) PTI_PKG 8/29/11 11:56 AM (admin) RPT_STANDARDPKG
8/29/11 11:56 AM (admin) NBI_EXTRACT_SP 8/29/11 11:56 AM (admin) NBI_EXTR_PCF_SP
8/29/11 11:56 AM (admin) NBI_EXTR_PRTF_SP 8/29/11 11:56 AM (admin) NBI_EXTR_RF_SP
8/29/11 11:56 AM (admin) NBI_WAIT_FOR_PARALLEL_JOBS_SP
8/29/11 11:56 AM (admin) Total time: 0H:0M:6S

Solution:

On the Clarity database schema grant "execute" rights on "dbms_job" and "dbms_random".

Recompile the stored procedures in the database.

Re-run the command "nikuadmin db compile" on the command prompt in the application server.

After that the Datamart Extraction job should run successfully

Keywords: CLARITYKB, bg, refresh schema, restore.