0

I have a table with some other columns and preexisting data in my development database. I need to add four more columns to store data for a new feature.

I've added four new columns to this table with the following commands:

alter table my_table add (pin_validacao_cadastro varchar2(6 char) default '000000' not null);
alter table my_table add (tentativas_validacao_pin number default 0 not null);
alter table my_table add (codigo_bloqueio number default 1 not null check (codigo_bloqueio in (0, 1, 2)));
alter table my_table add data_validacao_cadastro date;

Then, I've discovered that the first definition needed to change because the default value should be another value. Then, I've dropped the first column (pin_validacao_cadastro).

alter table my_table drop column pin_validacao_cadastro;

Suprisingly enough, before I try to recreate the first column with the correct default value, I've noticed the second column (tentativas_validacao_pin) now is altered and all the values are NULL, when it should to be 0.

Then, I've dropped the second column (tentativas_validacao_pin) to recreate it and fix the corruption.

alter table my_table drop column tentativas_validacao_pin;

But wait! Before I've had the chance to recreate it, I've noticed that all values of the third column (codigo_bloqueio) are equal to 0. Before the DROP command, all values of this column were equal to 1 (the default value for this column).

What am I missing here? Is this supposed to happen? It seems that the default value of the dropped column is being applyed to the next existing column.

Since the problem ocurrs using diferent database tools (sqldeveloper, sqlplus, PlSqldeveloper) I think that it is something related to oracle database.

Can anyone explain what is happening?

I'm using Oracle 11G.

Carlos Nantes
  • 1,197
  • 1
  • 12
  • 23
  • 1
    Are you seeing any errors? [Bug 17325413](https://support.oracle.com/epmos/faces/DocContentDisplay?id=17325413.8) sounds like it *could* be related, but [there are quite a few other bugs](https://support.oracle.com/epmos/faces/DocContentDisplay?id=1492674.1) related to add column optimisation. – Alex Poole Aug 09 '21 at 15:18
  • 1
    Well, you are right! It is a bug, though I can't confirm it's the one you mentioned. There is no explicit error when dropping the columns. I've found other threads related to this issue here: https://stackoverflow.com/a/33030522/7684613 https://stackoverflow.com/a/42261169/7684613 Splitting the `default` and `not null` declaration into 2 separated commands resolves the problem for me, as follows: ```` alter table my_table add (pin_validacao_cadastro varchar2(6 char) default '000000' ); alter table my_table modify pin_validacao_cadastro constraint nn_appusuario_pin not null; ```` – Carlos Nantes Aug 09 '21 at 17:29
  • OK, glad you found a workaround. I've closed a s duplicate of the first one as that is closer to your issue, but included the second as that has the workaround you are using. – Alex Poole Aug 09 '21 at 17:41

0 Answers0