The script generated when altering a MV attempts to catch the "No PK" error, but it appears Oracle 19 is not throwing -12014 error but instead a "ORA-12016: materialized view does not include all primary key columns" error. The script should be updated to catch either assuming 12014 is still valid.
Attached is output from running the generated script.
MV Update PK Error.txt (1.7 KB)
I also have scripts set to record start and end times, but this does not seem to work correctly in scripts run from the edit object wizards. You will see the following lines at the end of the attached file:
Time End: 11/2/2021 9:19:28 AM
Elapsed Time for Script Execution: 44502 days 09:19:28 hours
I believe the issue caused because the generated scripts do not set the start time.
So, you get this ORA-12016, when the MV was not created using the "WITH PRIMARY KEY"? on 19c?
The text makes it look like there is a PK mismatch somewhere.
It is possible I brought this odd error on myself. The MV is based on a bunch of other views, not tables and they contain calculated columns. As views really do not have PKs I created the MV without the PK clause. Once the MV and underlying table were created, I added a 2 Column PK to the table definition to make views based on the MV as efficient as possible. The Script for the MV now contains "WITH PRIMARY KEY" and when looking at the MV in the Schema Browser the PKs exist in the header info as shown below:
I am not sure if you want to ignore this as I am not sure how frequently others add PKs to an MV manually post creation, then attempt to use the wizard to make a modification to the MV. On the other hand I do not think it hurts to treat 12016 just like 12014 as all I did when I got the error was copy the code in the execute immediate from the Exception block and manually executed it in an editor window as though no PKs were present and the update correctly applies with no change to the PKs.
I was thinking the same after my initial reply. Sounds good. I'll go ahead and add it then.