This content has been marked as final. Show 9 replies
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.
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.
11g.DBA wrote:What you mean by truncated well can you post some example.
in other tables are truncated well but the problem is in a single table only.
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
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 :
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.
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
11g.DBA wrote:So let's truncate those 3 columns to exactly 30 characters:
surgey_pa_premedication_detail_dosa renamed to SURGEY_PA_PREMEDICATION_DETAIL (which is quiet right)
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
Spot the problem ?
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
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).
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 :
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
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>