9 Replies Latest reply: Feb 28, 2013 2:53 AM by Girish Sharma RSS

    Table name wrongly renamed

    11g.DBA
      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
          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
            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
              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
                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
                  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
                    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 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
                        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
                          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>