Better error messages in Oracle 23ai !!
2 min readHey Folks !!
With the enhancement in Oracle 23ai features one of the beautiful thing which we can see is to get a descriptive error messages which was not present in earlier Oracle release version.
Use ERROR_MESSAGE_DETAILS to control whether the database raises an additional stacked error message, with further details about the data values encountering the error, when displaying error messages.
You can set this parameter for the database instance or PDB with the ALTER SYSTEM statement, or for the current session with the ALTER SESSION statement, using these values:
ON: The database will provide additional explanatory details when displaying error messages. This is the default.
OFF: The database will not provide additional explanatory details. This is the behavior prior to Oracle Database 23ai.
Additionally, you can set the following value only at the database instance or PDB level with the ALTER SYSTEM statement:
DISALLOWED: The database will not provide additional explanatory details, and users cannot override this behavior for the current session with the ALTER SESSION statement.
In Previous Oracle release , we can see error messages were not in descriptive way:
SQL> conn hr/hr
Connected.
SQL> def
DEFINE _DATE = "16-AUG-24" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "test1" (CHAR)
DEFINE _USER = "HR" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1903000000" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0" (CHAR)
DEFINE _O_RELEASE = "1903000000" (CHAR)
SQL> SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_ID,COUNT(*) FROM EMPLOYEES GROUP BY DEPARTMENT_ID;
SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_ID,COUNT(*) FROM EMPLOYEES GROUP BY DEPARTMENT_ID
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
In 23ai , Error show is more in descriptive way :
[root@localhost ~]# systemctl start oracle-free-23ai
[root@localhost ~]# su - oracle
[oracle@localhost ~]$
[oracle@localhost ~]$ . oraenv
ORACLE_SID = [oracle] ? FREE
The Oracle base has been set to /opt/oracle
[oracle@localhost ~]$
[oracle@localhost ~]$ sqlplus hr/hr@//localhost:1521/freepdb1
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Fri Aug 16 04:39:19 2024
Version 23.5.0.24.07
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Last Successful login time: Fri Aug 16 2024 04:12:02 +05:30
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07
SQL> SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_ID,COUNT(*) FROM EMPLOYEES GROUP BY DEPARTMENT_ID;
SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_ID,COUNT(*) FROM EMPLOYEES GROUP BY DEPARTMENT_ID
*
ERROR at line 1:
ORA-00979: "EMPLOYEE_ID": must appear in the GROUP BY clause or be used in an
aggregate function
Help: https://docs.oracle.com/error-help/db/ora-00979/
Hope it helped !! 🙂
