Adsense Ad

Thursday 25 May 2017

Oracle: ORA-00957 Error Message

ORA-00957 Error Message

Learn the cause and how to resolve the ORA-00957 error message in Oracle.

Description

When you encounter an ORA-00957 error, the following error message will appear:
  • ORA-00957: duplicate column name

Cause

You tried to execute either a CREATE TABLE or INSERT statement where the same column name was listed more than once.
Or you tried to execute a UPDATE statement where the same column name was listed more than once in the SET clause.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

If this error occurred during a CREATE TABLE, correct the statement so that each column listed in the CREATE TABLE statement is unique.
For example, if you tried to create the following table:
CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  supplier_id numeric(8)
);
You would receive the following error message:
Oracle PLSQL
Since the supplier_id field appears twice, the CREATE TABLE statement will fail. You need to ensure that each column name is unique.
You could correct the statement as follows:
CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  supplier_id2 numeric(8)
);

Option #2

If this error occurred during an INSERT statement, correct the statement so that each column listed is unique.
For example, if you tried to execute the following INSERT statement:
INSERT INTO supplier
(supplier_id, supplier_name, supplier_id)
VALUES
(1000, 'IBM', '1000');
You would receive the following error message:
Oracle PLSQL
Since the supplier_id field appears twice, the INSERT statement will fail. You can correct this by removing the duplicate column name as follows:
INSERT INTO supplier
(supplier_id, supplier_name)
VALUES
(1000, 'IBM');

Option #3

If this error occurred during an UPDATE statement, correct the statement so that each column listed in the SET clause is unique.
For example, if you tried to execute the following UPDATE statement:
UPDATE suppliers
SET supplier_id = 1000,
    supplier_id = 1000
WHERE supplier_id = 1;
You would receive the following error message:
Oracle PLSQL
Since the supplier_id field appears twice in the SET clause, the UPDATE statement will fail. You can correct this by removing the duplicate column name as follows:
UPDATE suppliers
SET supplier_id = 1000
WHERE supplier_id = 1;

No comments: