در نسخه 23c دستور OERR اطلاعات بیشتری را برای بسیاری از خطاها(نسبت به نسخه های قبلی) ارائه می کند که این مسئله می تواند در شناسایی علت رخ دادن خطا و همچنین رفع سریع تر آن کمک کند.
بعنوان مثال، برای خطای ORA-00001: unique constraint (constraint_name) violated این دستور را در دو نسخه 21c و 23c اجرا می کنیم:
'Oracle 21c':
[oracle@OEL7 ~]$ oerr ora 00001
00001, 00000, "unique constraint (%s.%s) violated"
// *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
// For Trusted Oracle configured in DBMS MAC mode, you may see
// this message if a duplicate entry exists at a different level.
// *Action: Either remove the unique restriction or do not insert the key.
'oracle 23c'
[oracle@IranHost ~]$ oerr ora 00001
00001, 00000, "unique constraint (%s.%s) violated on table %s.%s columns (%s)"
// *Cause: An UPDATE, INSERT or MERGE statement attempted to update or
// create a record that duplicated values limited by a unique
// constraint. A unique constraint can be implemented as an
// explicit unique constraint, a unique index, or a primary key.
//
// Consider the case where a table has a unique constraint on columns
// FIRSTNAME and LASTNAME. Because of this constraint, it is not
// possible to insert a row containing values of FIRSTNAME and
// LASTNAME that are identical to the values of these columns in an
// existing table row.
// *Action: Determine what type of unique constraint was violated (explicit
// unique constraint, unique index, or primary key), and which table
// columns are affected by this unique constraint.
//
// Then choose whether to change the constraint to allow duplicate
// values; modify the SQL statement to no longer create a duplicate
// value; or drop the constraint, unique index, or primary key.
// *Params: 1) constraint_schema: The schema name where the constraint
// resides.
// 2) constraint_name: The name of the constraint.
// 3) table_schema: The schema name for the table affected by this
// constraint.
// 4) table_name: The name of the table affected by this
// constraint.
// 5) column_names: The column names affected by this
// constraint.
قابلیت دیگر اوراکل 23c در این زمینه، اضافه شدن دستور OERR به محیط sqlplus است:
[oracle@IranHost ~]$ sqlplus "/as sysdba"
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL> OERR
Usage: OERR <facility name> <error number>
SQL> OERR ora 01452
Message: "cannot CREATE UNIQUE INDEX; duplicate keys found"
Help: https://docs.oracle.com/error-help/db/ora-01452/
Cause: A CREATE UNIQUE INDEX statement specified one or more
columns that currently contained duplicate values. All values in
the indexed columns must be unique by row to create a UNIQUE
INDEX.
Action: If the entries need not be unique, remove the keyword
UNIQUE from the CREATE INDEX statement, then re-execute the
statement. If the entries must be unique, as in a primary key,
then remove duplicate values before creating the UNIQUE index.
