DBMS_JAVA could not be validated or authorized-CodePudding

I am facing the error while upgrading oracle database 11gR2 to 19c. Error Code: ORA-04023: Object SYS.DBMS_JAVA could not be validated or authorized.

For example:

  SQL> select dbms_java.longname('TEST') from dual;
  select dbms_java.longname('TEST') from dual
  *
  ERROR at line 1:
  ORA-04023: Object SYS.DBMS_JAVA could not be validated or authorized

CodePudding user response：

You need to alter the dbms_java package.

First connect to database as sysdba then alter the package

conn / as sysdba

SQL> alter package dbms_java compile;

Package altered.

SQL> select dbms_java.longname('TEST') from dual;

DBMS_JAVA.LONGNAME('TEST')
--------------------------
TEST

CodePudding user response：

Using dba_registry should help identify if there is something wrong with your Java version installed in the database.

Query

SELECT comp_id, comp_name, version, version_full, status, procedure
  FROM dba_registry
 WHERE comp_id = 'JAVAVM';

Example Result

   COMP_ID                       COMP_NAME       VERSION    VERSION_FULL    STATUS                     PROCEDURE
__________ _______________________________ _____________ _______________ _________ _____________________________
JAVAVM     JServer JAVA Virtual Machine    19.0.0.0.0    19.10.0.0.0     VALID     INITJVMAUX.VALIDATE_JAVAVM

When looking at dba_registry, any components that are not listed as VALID can be validated by connecting as SYS then running the procedure listed under the PROCEDURE column.