Some Oracle Errors & their simple solutions

Encountered some of oracle errors which were too trivial to solve though the error message does not elaborate. Please note that these are not the only solution, but I faced the problem and the solution was too trivial and not well understood by the error message

  • ORA-01747: invalid user.table.column, table.column, or column specification

Solution : The error is raised when by mistake an extra ‘,’ (comma) is left in update or insert query like

insert into table1 (f1, f2, f3 ,) values (val1, val2, val3);   — Note an extra ‘,’ at the end of f3

update table1 f1 = val1, f2 = val2, f3 = val3,;   — Note an extra ‘,’ at the end of f3

  •  ORA-00907: missing right parenthesis

When using distinct with more than one field if you put the fields in bracket the error may come

select distinct (field1) from table1;           — No Error

select distinct (field1, field2) from table1;   — Error as above

select distinct field1, field2 from table1;   — No Error

  • Expdp failing with error open failed

Check that permission are in place for the user who is exporting / importing

grant create session, create table, create procedure exp_full_database, imp_full_database to <user>

Advertisements
This entry was posted in Database, Oracle. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s