تغییرات دستور OERR در اوراکل 23c

در نسخه 23c 1 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.

قابلیت دیگر 1 23c در این زمینه، اضافه شدن دستور OERR به محیط sqlplus است:

[oracle@IranHost ~]$ sqlplus "/as sysdba"
Coected 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: "caot 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.