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

در نسخه 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"
Coected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Lea, 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.

تالار های تخصصی...