This discussion is archived
9 Replies Latest reply: Feb 28, 2013 12:53 AM by Girish Sharma RSS

Table name wrongly renamed

11g.DBA Newbie
Currently Being Moderated
Hi,

I migrated an existing MySQL database to oracle database.
MySQL database was having some tables and Columns with length >30,As oracle doesnt support length >30 so it automatically renamed the length to 30 characters by removing the last characters which are after 30 characters but today i found some column names which are wrongly renamed as
All following columns are in single table only

surgey_pa_premedication_detail_dosa renamed to SURGEY_PA_PREMEDICATION_DETAIL (which is quiet right) 
But
surgey_pa_premedication_detail_id renamed to SURGEY_PA_PREMEDICATION_DE_730
surgey_pa_premedication_detail_route renamed to SURGEY_PA_PREMEDICATION_DE_731
surgey_pa_premedication_detail_type renamed to SURGEY_PA_PREMEDICATION_DE_732
Thanx
  • 1. Re: Table name wrongly renamed
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,

    Oracle does not support column name lenght more than 30 char.
    What is maximum column name length?

    so you wiill have to rename the name accordingly.

    http://docs.oracle.com/javadb/10.8.2.2/ref/rrefsqljrenamecolumnstatement.html
  • 2. Re: Table name wrongly renamed
    11g.DBA Newbie
    Currently Being Moderated
    Yeah I know that oracle doesn't support >30.I migrated database using sqldeveloper and it automatically truncate the length of column which are >30.
    But all other columns(length>30) in other tables are truncated well but the problem is in a single table only.
  • 3. Re: Table name wrongly renamed
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    11g.DBA wrote:
    in other tables are truncated well but the problem is in a single table only.
    What you mean by truncated well can you post some example.
  • 4. Re: Table name wrongly renamed
    11g.DBA Newbie
    Currently Being Moderated
    Table Name : OT_SURGEY_PA_PROCEDURE_DETAIL
    
    ot_post_anaesthesia_procedure_id renamed to OT_POST_ANAESTHESIA_PROCEDURE_     
    surgey_pa_procedure_detail_anesthesia renamed to SURGEY_PA_PROCEDURE_DETAIL_ANE
    surgey_pa_procedure_detail_dosa renamed to SURGEY_PA_PROCEDURE_DETAIL_DOS
  • 5. Re: Table name wrongly renamed
    Girish Sharma Guru
    Currently Being Moderated
    Since 30 is limit it means, we can not have more than 30. Now, remaining option is you can enter the longer identifier as a column comment in

    o <USER|ALL|DBA>TABCOMMENTS for tables.
    o <USER|ALL|DBA>COLCOMMENTS for columns.

    Further reading :

    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:233014204543

    Regards
    Girish Sharma
  • 6. Re: Table name wrongly renamed
    11g.DBA Newbie
    Currently Being Moderated
    You have not understood my problem well.My issue is not with the length.My issue is that it is wrongly renaming the column name just in a single table.In all other tables,the column names are correctly renamed i.e. by omitting the characters falling after 30 characters.
  • 7. Re: Table name wrongly renamed
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Oracle was smarter than you think.

    It couldn't truncate all the columns to 30characters because the first 30characters for every column are exactly the same. Truncating to 30characters would result in the same column-name being repeated another three times.

    So, it executed the truncate only for the first coumn. For the subsequent columns, it truncated at 26characters and added 4-character strings that are distinct.

    You will have to rename the columns yourself to more meaningful names.


    Hemant K Chitale
  • 8. Re: Table name wrongly renamed
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    11g.DBA wrote:

    surgey_pa_premedication_detail_dosa renamed to SURGEY_PA_PREMEDICATION_DETAIL (which is quiet right)
    But
    surgey_pa_premedication_detail_id renamed to SURGEY_PA_PREMEDICATION_DE_730
    surgey_pa_premedication_detail_route renamed to SURGEY_PA_PREMEDICATION_DE_731
    surgey_pa_premedication_detail_type renamed to SURGEY_PA_PREMEDICATION_DE_732
    So let's truncate those 3 columns to exactly 30 characters:
    surgey_pa_premedication_detail_id    -> SURGEY_PA_PREMEDICATION_DETAIL
    surgey_pa_premedication_detail_route -> SURGEY_PA_PREMEDICATION_DETAIL
    surgey_pa_premedication_detail_type  -> SURGEY_PA_PREMEDICATION_DETAIL
    Spot the problem ?

    I guess Oracle replaces the last 4 columns with a numeric tag because you might have 1000 columns in a single table which all start with the same 30 characters. (That's a guess - but I would be interested to hear how many columns this table has).

    Regards
    Jonathan Lewis
  • 9. Re: Table name wrongly renamed
    Girish Sharma Guru
    Currently Being Moderated
    I understood your problem well that is why I have given you the remaining option which you can have. Since column names have been changed, so now you can put comments for the changed column names something like this :

    SQL> comment on column OT_SURGEY_PA_PROCEDURE_DETAIL.SURGEY_PA_PREMEDICATION_DE_730
    is 'This is column surgey_pa_premedication_detail_id of table OT_SURGEY_PA_PROCEDURE_DETAIL';

    and then you can query something like this :

    select comments
    from user_col_comments
    where table_name = 'OT_SURGEY_PA_PROCEDURE_DETAIL'
    and column_name = 'SURGEY_PA_PREMEDICATION_DE_730';

    This will give you :

    This is column surgey_pa_premedication_detail_id of table OT_SURGEY_PA_PROCEDURE_DETAIL

    Regards
    Girish Sharma

    Edited by: Girish Sharma on Feb 28, 2013 2:22 PM

    Something like this :
    SQL> create table OT_SURGEY_PA_PROCEDURE_DETAIL
      2  (
      3  OT_POST_ANAESTHESIA_PROCEDURE_ number,
      4  SURGEY_PA_PROCEDURE_DETAIL_ANE number,
      5  SURGEY_PA_PROCEDURE_DETAIL_DOS number
      6  )
      7  /
    
    Table created.
    
    SQL> comment on column OT_SURGEY_PA_PROCEDURE_DETAIL.OT_POST_ANAESTHESIA_PROCEDURE_
      2  is 'This is column ot_post_anaesthesia_procedure_id of Table OT_SURGEY_PA_PROCEDURE_DETAIL';
    
    Comment created.
    
    SQL> comment on column OT_SURGEY_PA_PROCEDURE_DETAIL.SURGEY_PA_PROCEDURE_DETAIL_ANE
      2  is 'This is column surgey_pa_procedure_detail_anesthesia of Table OT_SURGEY_PA_PROCEDURE_DETAIL';
    
    Comment created.
    
    SQL> comment on column OT_SURGEY_PA_PROCEDURE_DETAIL.SURGEY_PA_PROCEDURE_DETAIL_DOS
      2  is 'This is column surgey_pa_procedure_detail_dosa of Table OT_SURGEY_PA_PROCEDURE_DETAIL';
    
    Comment created.
    
    SQL> ---Now Query
    SQL> column comments for a50;
    SQL> set line 200;
    SQL> select comments
      2  from user_col_comments
      3  where table_name = 'OT_SURGEY_PA_PROCEDURE_DETAIL'
      4  and column_name = 'OT_POST_ANAESTHESIA_PROCEDURE_';
    
    COMMENTS
    --------------------------------------------------
    This is column ot_post_anaesthesia_procedure_id of
     Table OT_SURGEY_PA_PROCEDURE_DETAIL
    
    
    SQL> select column_name,
      2  comments
      3  from user_col_comments
      4  where table_name = 'OT_SURGEY_PA_PROCEDURE_DETAIL';
    
    COLUMN_NAME                    COMMENTS
    ------------------------------ --------------------------------------------------
    OT_POST_ANAESTHESIA_PROCEDURE_ This is column ot_post_anaesthesia_procedure_id of
                                    Table OT_SURGEY_PA_PROCEDURE_DETAIL
    
    SURGEY_PA_PROCEDURE_DETAIL_ANE This is column surgey_pa_procedure_detail_anesthes
                                   ia of Table OT_SURGEY_PA_PROCEDURE_DETAIL
    
    SURGEY_PA_PROCEDURE_DETAIL_DOS This is column surgey_pa_procedure_detail_dosa of
                                   Table OT_SURGEY_PA_PROCEDURE_DETAIL
    
    
    SQL>

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points