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