Environment: Banner: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Patch Version 19.30
this Statement throws the exception ORA-00979
SELECT INITCAP(g.risiko_gruppe),
g.gor_risiko_typ,
g.zustand,
--
MAX(g.sto_gueltig_ab) AS sto_gueltig_ab,
SUM(g.gor_bruttopraemie) AS gor_bruttopraemie
FROM ( --
SELECT DISTINCT gor.gor_risiko_typ AS risiko_gruppe,
gor.gor_risiko_typ AS gor_risiko_typ,
--
gor.gor_zustand AS zustand,
NVL(gor.gor_bruttopraemie, 0) AS gor_bruttopraemie,
TRUNC(SYSDATE - 10) AS sto_gueltig_ab
FROM sev.gefahr_objekt_risiko gor
WHERE gor.pol_policen_nr = 13036148--
) g
GROUP BY zustand, risiko_gruppe
With the Patch Patch Version 19.26 the statements works fine.
The reason is that, in version 19.26, the Oracle optimizer handles situations where the same column is referenced with different aliases during execution more leniently.
The bigger problem is that in PL/SQL the compiler don't throws an error during compiling. So the error is always at runtime.
Questions:
-
Is there a possibility to set a flag in the database configuration so that the optimizer works like Patch Version 19.26 ?
-
Is there a possibility in the database configuration so that the compiler throws an error during compiling the pl/sql source ?
-
Do you have a good idea to find all source code in pl/sql that throws the error during runtime ?
Thank you very much for your help
best regards
Michael