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

Today I got this error.

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

There could be many reasons why you can get this error. But the basic reason is the query which is finally getting executed to your database is wrong in some way. In my case, I had a column with column name as DESC. I got rid of this problem by simply dropping the table and creating a new one with column name DESCRIPTION instead of DESC. You may not be able to do that if the table is already existing with many records in it. 🙂

Other reason could be that, if you are using named query your named query is not syntactically right.

No matter how the you get this error or for that matter any DB error in Hibernate, the best approach is to enable the show_sql variable in hibernate configuration and get the final query which hibernate is firing against the database.

Get this query and try running it directly in your database client. You can easily find out the culprit part of the query from there. Once you find out root cause of the problem you can think of workarounds or solution for this problem.

And yes, don’t forget to turn off the show_sql parameter after you are done with it. It will unnecessarily clutter your logs.

11 thoughts on “ORA-01747: invalid user.table.column, table.column, or column specification

  1. joshua

    hi i got this error while using my java program with jdbc

    i tried copy paste the generated query in oracle,it works fine…but when it is executed in java program..it shows exception

    Reply
  2. Paras

    hi Joshua,
    have you found what was the root cause of your problem. Can you post the query you are running directly in the Oracle.

    Reply
  3. ashish

    “INSERT INTO ICCUDISCHARGES ” +
    “(ICCUID,ICCUREGNO,RESULT,REMARK,SPT,DATE,TRANSFERTOWARD)” +
    “VALUES(SWS_ICCU_ID.NEXTVAL,:ICCUREGNO,:RESULT,:REMARK,:SPT,:DATE,:TRANSFERTOWARD)”;

    i fire this query..in all forms this type qury is executed..but in this particular form i find error like this so what is solution..???or is there any problm in my database or in table..???

    Reply
  4. Paras

    Ashish,
    If you are successfully able to run this query in other forms I think the query is alright. Have you tried executing it against the database directly? I am not sure of the ‘DATE’ column, because I don’t know which database you are using specifically.
    Other point, may be one of the values you are passing are culprit. For the particular case in which it is failing please find the value of SWS_ICCU_ID.NEXTVAL,:ICCUREGNO,:RESULT,:REMARK,:SPT,:DATE etc by putting debug or Sysout statement. The take those values and run the entire query against the database directly. You might get the offending part. Probably the date format is not proper or something.
    Let me know.
    Paras

    Reply
  5. desilva

    I got this error when i had a comma after the last set variable.

    update foo set bar=?, foo=?, baz=?, where foo.id = ? ^- here

    Reply
  6. mijaved

    I got this error in the following statement:
    insert into T_User ( USERID,PASS,USERNAME,ROLEID,OID,) values(‘1′,’1′,’1′,’1′,’1’,)

    and fixed it by:
    insert into T_User (USERID,PASS,USERNAME,ROLEID,OID) values(‘1′,’1′,’1′,’1′,’1’)

    just by removing the extra commas after the last set variable

    Reply
  7. Mohamed Ennahdi El Idrissi

    I think it’s a question of column type: if you try to answer a string value into a Number SQL column, that could trigger such an error…

    Reply
  8. Pingback: 2010 in review « Techspace

Leave a Reply

Your email address will not be published. Required fields are marked *