Upgraded Spotlight on Oracle from SoO4.0.4 to SoO4.5.1 and are prompted to upgrade objects when connecting to database. However, when trying to upgrade database objects, we get "An error has occurred in table MakeXView" message
An error has occurred in table MakeXView
Ora-06550 line12 column11
PLS-00905 object sys.build_xview is invalid
Ora-06550 line 12 column 11
Migrated production trading database from 8.1.7 to 10.2.0.3 The problem was that the "plsql_code_type" parameter in the database was set to "native" which means that it will try to compile all plsql code to native binaries.
Resetting the parameter to "interpreted" resolved issue
We run a report that shows all invalid objects and found execute_immediate and build_xview objects invalid
We tried recompiling both objects, then tried to run the Oracle user wizard to create a new user, but after trying to create the user in soo4.5 we found the objects invalid again and got the same error "An error has occurred in table MakeXView"
We also tried recompiling the objects and then ran the manual script on the server and got the same messages of invalid objects and found invalid quest_soo_user_manager object.
When recompiling the object we encountered PLS00920 error:
SQL>alter package quest.quest_soo_user_manager compile; PLS-00920: parameter plsql_native_library_dir is not set
