2

How to change CHAR (17 byte) to CHAR (16 byte) when there is already a data?

Values are something like this:

012345-000-00001

  • There is a space at the end.

I tried to trim it first before altering the column size but still gets the error due to column is already bigger than 16 byte (due the space at the end).

Thanks !

1

Modifying an Existing Column Definition

You must set the initialization parameter BLANK_TRIMMING=TRUE to decrease the length of a non-empty CHAR column.

SQL> show parameter BLANK_TRIMMING;

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
blank_trimming               boolean     FALSE


SQL> alter system set BLANK_TRIMMING=true scope=spfile;

System altered.

Then bounce the database.

SQL> startup force;

SQL> update tblchar set col1=TRIM(col1);

SQL> alter table tblchar modify col1 char(16 byte);

Table altered.

Documentation: Altering Tables

  • +1 for the BLANK_TRIMMING parameter. Would have saved me a bit of work a couple of weeks ago. – BriteSponge Feb 23 '17 at 14:33
1

Let me add another method.

add another column to the table, copy data to new column, drop old column, rename new to old column.

ALTER TABLE t
   ADD (col2 CHAR (16));

UPDATE t
   SET col2 = TRIM (col1);

ALTER TABLE t
   DROP (col1);

ALTER TABLE t
   RENAME COLUMN col2 TO col1;

Edit: Do not forget to create new indexes that may be affected.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.