Wednesday, March 05 2014
Keywords: Oracle Database Server, SQL Developer, Error, Merge, Merging, ORA-00957, duplicate column name?
Oracle Error While Merging : ORA-00957 duplicate column name?
This error simply states that a column name was specified twice in a CREATE, INSERT, UPDATE or MERGE.
As you could guess...column names must be unique within a table or query.
You could get this error message in various situations...and the aim of this blog is not to explain every single cause of this error. There are many web pages out there that explain this already. I must also add that with a little bit of detective work, the duplicate-culprits are usually spotted quickly within a query...although sometimes well camouflaged.
However...recently I had to solve this issue from a completely different angle and point of view. When a colleague approached me the other day with this very error...I took a look at the Merge statement where this was occurring and could not come up with any duplicate columns...there certainly wasn't any...this was quite strange and I'm a firm believer of science and logic...knowing that there is always a logical answer to every single error out there...as strange as they might look.
Then I started to investigate a bit further...and there came in a big clue: "this very same query was working on another database with identical table structure and data.
That was a big clue for me actually...and lead into the discovery of the answer. It basically told me that it wasn't the query itself per se...but the environment or settings.
The first thing I started investigating was the security and permission settings...I saw that there were differences between permissions applied to the two database users in question. In the database where the duplicate column name error was raised, the user was given dba permission.
In order to make the life of developers easier...it is common practice by some folks to give every single power-user grade permission to a user so that he/she can continue programming in peace and doesn't hit stumbling blocks and waste time.
But in this particular case of mine...too much permission was actually the cause of the problem. I removed the 'dba' permission and left the user with a few basic rights...and voila...the error was gone. Again that is because now the user could no longer see the same table belonging to all other schemas...this problem is quite very specific to Oracle actually.
In this particular application...the bare minimum roles I needed to provide the user were:
And the bare minimum System Privileges provided to this user needed to be:
Create any view
Create public synonym
Select any dictionary
Unlimited table space
Written by Montreal DBA Team