1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Invailid identifier in SQL Developer after renaming column

Discussion in 'SQL PL/SQL' started by Chet, Mar 4, 2010.

  1. Chet

    Chet Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    I am reengineering a database and wnated to change POC to PocID as this is a number field that is the PK for the table. I di this by generating the sql in SQL Developer and then running it in the code window. Everything workied fien the field was renamed and in the column, data, etc views the PocID column is there with data. However when I try to reference the column in a SELECT or ORDER BY I get ORA-00904 invalid identifier. In the Auto complete feature PocID shows up with quotes around it but none of the other fields are displayed that way.

    Any ideas thsi is very frustating and if I return to the origonal name I still have the issue.

    Thanks
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The column name returns as case-sensitive, I'll presume, and as such you need the "" around the name to get Oracle to recognize it. This is, unfortunately, the way most 'tools' function. You COULD do this the 'old fashiioned' way through a SQL*Plus prompt and rename this column to the same name:

    alter table ... rename column "PocID" to PocID;

    You should then be able to execute:

    select pocid from ...

    and it should work.
     
  3. Chet

    Chet Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Thanks, but that did not appear to solve the problem. It is confusing in one case I renamed the column to the old name, created a new column, copied the data and then dropped the initial column. That worked. In anotehr situation I tried this and when I tried to copy I got the Invailid ID again. Based on you suggestion I tried different combinations of single and double quotes all to no avail.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You failed to provide the Oracle release you're using; such information goes a long way in allowing us to know if this is a release where such operations are plagued with bugs.

    Please post the Oracle version, in dot-separated numbers (10.2.0.4, 9.2.0.6, etc.).
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Also try to do the same activity in SQLPLUS .. and show us the result by pasting the session .
     
  6. Chet

    Chet Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Here are the results fro SQL Plus. Oracle version in the header



    Code (SQL):
    SQL*Plus: Release 10.2.0.1.0 - Production ON Fri Mar 5 13:07:55 2010

    Copyright (c) 1982, 2005, Oracle.  ALL rights reserved.


    Connected TO:
    Oracle DATABASE 10g Enterprise Edition Release 10.2.0.1.0 - Production
    WITH the Partitioning, OLAP AND DATA Mining options

    SQL> CREATE TABLE TEST
      2  (
      3    POC NUMBER
      4  , NAME VARCHAR2(20)
      5  , LAST_UPDT DATE
      6  );

    TABLE created.

    SQL> INSERT INTO Test VALUES(1,'name1', '3-mar-2010');

    1 ROW created.

    SQL> INSERT INTO Test VALUES(2, 'name2', '3-mar-2010');

    1 ROW created.

    SQL> SELECT POC, Name, Last_Updt FROM Test;

           POC NAME                 LAST_UPDT
    ---------- -------------------- ---------
             1 name1                03-MAR-10
             2 name2                03-MAR-10

    SQL> ALTER TABLE "CMDDB"."TEST" RENAME COLUMN "POC" TO "PocID";

    TABLE altered.

    SQL> SELECT PocID, Name, Last_Updt FROM Test;
    SELECT PocID, Name, Last_Updt FROM Test
           *
    ERROR at line 1:
    ORA-00904: "POCID": invalid identifier


    SQL>
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I explained this earlier, the query MUST be written as:

    Code (SQL):
    SELECT "PocID", name, last_updt FROM test;
    You created the column name with a quoted string so you MUST retain the quotes when referencing that column. Had you written this:

    Code (SQL):
    ALTER TABLE CMDDB.TEST RENAME COLUMN POC TO PocID;
    your original query:

    Code (SQL):
    SELECT PocID, Name, Last_Updt FROM Test;
    would work.
     
  8. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    use "desc test" command what exactly the columns are present in table.

    desc <table name> may avoids the above stated confusion
     
  9. Chet

    Chet Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    After alot of trial and error it appears for this to work quotes cannot be used in the rename statement. I tried renaming w/o quotes and all works fine. When I renamed with quotes I could never access the renamed field by name even using single or double quotes.

    Thanks for the help
     
  10. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    And to add a special NOTE.. .

    Making the table names or column names case-sensitive (by using double quotes) IS NOT a good practice.