Over the summer our database was upgraded from Oracle 11g to 19c
I am trying to run the below query
CREATE MATERIALIZED VIEW rodney_is_going_crazy
(PROGRAM_KEY)
AS
(
SELECT PROGRAM_KEY FROM (
SELECT
B.PROGRAM_KEY
FROM I_FACT_CNF_SESS_INTAKE_SS B
INNER JOIN DM_CNF_PROGRAM B1
ON B1.PROGRAM_KEY = B.PROGRAM_KEY
NATURAL JOIN V_EPG_TERM B3
) );
And I am hitting the below errors when I try running it in SQL developer
Error report -
ORA-12018: following error encountered during code generation for "EDW"."ROD_IS_GOING_NUTS"
ORA-00904: "from$_subquery$_006"."PROGRAM_KEY": invalid identifier
12018. 0000 - "following error encountered during code generation for \"%s\".\"%s\""
*Cause: The refresh operations for the indicated materialized view could
not be regenerated due to errors.
*Action: Correct the problem indicated in the following error messages and
repeat the operation.
Prior to the upgrade this code was working perfectly fine. No issues. I tried running this code in both TOAD and SQL developer and both are giving me the error above
The original query is actually much larger, but I narrowed it down to the above.
My DBA suggested that theres a syntax error somewhere that is being caused by copying and pasting code from Notepad++ to TOAD initially.
But I literally typed the above query by hand from scratch in SQL developer so I dont see how it could be some hidden character.
The SQL itself runs just fine. Just when trying to create this materialized view its throwing me these errors.
Any insight would be greatly appreciated.