how to change a column's attribute without affecting the values already present?

Question

Hi,

To put it in a nutshell - In an oracle db, I want to make a column varchar2(16) which is now varchar2(8), without affecting the values presnet. I have already tried this and it does weird things.

The query I tried was - alter table SOME_TABLE modify (SOME_COL varchar2(16)); But the values(some not all) already present in the table get '\0' appended to them when I run the above query.

So, what is the right way of doing what I want?

Answer 1

The command you are executing is correct.

Are you sure the additional characters you are seeing are not already present?

Answer 2

If nothing else works for you, you can always do the following:

1. Add a new column with a new name
2. Copy the values from the old column to the new one using UPDATE
3. Delete the old column
4. Rename the new column to the old one's name

It's long and cumbersome and brute force, but if you can't get it done any other way, it will work...

Answer 3

It's very doubtful that the raw data in the table is being changed. Since some of your comments imply you are using tools and applications other than SQLPlus to look at and process the data, I think you need to look at whether they are mishandling the data in some way.

Here's an example where I tried to reproduce what you did in straight SQLPlus. No null bytes are appended to the existing data:

SQL> create table foo (bar varchar2(8));

Table created.

SQL> insert into foo        
  2  select lpad(to_char(level),level)
  3    from dual 
  4    connect by level <=8;

8 rows created.

SQL> commit;

Commit complete.

SQL> select bar,dump(bar) from foo;

BAR
--------
DUMP(BAR)
--------------------------------------------------------------------------------
1
Typ=1 Len=1: 49

 2
Typ=1 Len=2: 32,50

  3
Typ=1 Len=3: 32,32,51

   4
Typ=1 Len=4: 32,32,32,52

    5
Typ=1 Len=5: 32,32,32,32,53

     6
Typ=1 Len=6: 32,32,32,32,32,54

      7
Typ=1 Len=7: 32,32,32,32,32,32,55

       8
Typ=1 Len=8: 32,32,32,32,32,32,32,56


8 rows selected.

SQL> alter table foo modify (bar varchar2(16));

Table altered.

SQL> select bar,dump(bar) from foo;

BAR
----------------
DUMP(BAR)
--------------------------------------------------------------------------------
1
Typ=1 Len=1: 49

 2
Typ=1 Len=2: 32,50

  3
Typ=1 Len=3: 32,32,51

   4
Typ=1 Len=4: 32,32,32,52

    5
Typ=1 Len=5: 32,32,32,32,53

     6
Typ=1 Len=6: 32,32,32,32,32,54

      7
Typ=1 Len=7: 32,32,32,32,32,32,55

       8
Typ=1 Len=8: 32,32,32,32,32,32,32,56

Answer 4

Mostly agree with Dave Costa. Might there be a cache that still thinks the data is the old '8' size. When you say "some not all" values get the extra \0, what is the consistent factor ? Are they all the same length (eg seven or eight characters) or might they have been inserted while the ALTER column was being done or before some srver restart ?

No solutions, but something similar got raised a couple of days ago here. Maybe try to compare notes.