0

Its about ORACLE (PL/SQL) script. I am not very familiar with databse to be honest. I want to alter the length of a string in a column from 30 to 60. It is not null column. If the table is empty and I run following script then it works:

alter table [TABLE_NAME] add ( NEW_COLUMN NVARCHAR2(60)  DEFAULT 'null' NOT NULL );
/
alter table [TABLE_NAME] DROP CONSTRAINT PK_[TABLE_NAME];
/
begin
   for rec in ( select * from [TABLE_NAME] )
   loop
      update [TABLE_NAME] set NEW_COLUMN =rec.OLD_COLUMN where Name_ID=rec.Name_ID;
   end loop;
end;
/
alter table [TABLE_NAME] drop column OLD_COLUMN;
/
alter table [TABLE_NAME] rename column NEW_COLUMN to OLD_COLUMN;
/
alter table [TABLE_NAME] add CONSTRAINT PK_[TABLE_NAME] PRIMARY KEY(Name_ID);
/

But if the table has values then this script does not work. It gives error: Cannot drop constraint - nonexistent constraint

However, if I remove lines about constraints (second and second last) then it works. Now I don’t know if the table will be empty or it will have data so I need a script that can work in both the situations. Can anyone help please?

Following script for creating table:

CREATE TABLE TABLE_NAME
(
Name_ID NVARCHAR2(7) NOT NULL,
OLD_COLUMN NVARCHAR2(30) NOT NULL,
CONSTRAINT PK_TABLE_NAME PRIMARY KEY(Name_ID, OLD_COLUMN)
)
/

So while creating table it puts the primary key constraints but while updating table it drops this constraints somehow. I am simplyfying the sitation here. The tables are updates through java code. What I need to do is make a script that work in both situations - with data or just after creating table and modifying the column.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Charychap
  • 69
  • 1
  • 7
  • Please edit your question to include the create table (including constraints) script, so that we can at least have a go at replicating your particular scenario. Also, why are you doing the update in PL/SQL? You're complicating and slowing things down by doing so. Instead, why not just: `update table_name set new_column = old_column;`, followed by an explicit `commit;`? – Boneist Mar 19 '15 at 11:58
  • Also, what do you mean by "script does not work"? Does it error? If so, what error(s) do you get? – Boneist Mar 19 '15 at 12:00
  • I think the error is the space in `rec. OLD_COLUMN`; btw. you should do the update as Boneist shows, do not forget the `commit` – Pavel Gatnar Mar 19 '15 at 12:04
  • the lack of an explicit commit is not so much of an issue here, as the very next DDL statement will automatically do a commit, but it's good practice not to rely on the implicit DDL commit. – Boneist Mar 19 '15 at 12:11

2 Answers2

1

The following script works for me, regardless of whether the insert statement is present or not (ie. the table has or has not data):

CREATE TABLE TABLE_NAME
(
Name_ID NVARCHAR2(7) NOT NULL,
OLD_COLUMN NVARCHAR2(30) NOT NULL,
CONSTRAINT PK_TABLE_NAME PRIMARY KEY(Name_ID, OLD_COLUMN)
);

insert into table_name (name_id, old_column)
values ('test', 'test_old_col');

commit;

alter table table_name add (new_column nvarchar2(60) default 'null' not null);

update table_name set new_column = old_column;

commit;

alter table table_name drop constraint PK_TABLE_NAME;

alter table table_name drop column old_column;

alter table table_name rename column new_column to old_column;

alter table TABLE_NAME add CONSTRAINT PK_TABLE_NAME PRIMARY KEY(Name_ID, old_column);

drop table table_name;

I have assumed that you meant to recreate the primary key with the old_column in it, otherwise you would be unable to recreate it if there are any duplicate values present in the name_id column.

Boneist
  • 22,910
  • 1
  • 25
  • 40
  • Ok. Its working now. I am not sure if dropping constraint before copying the values was raising this problem. Also you suggested copying the values in diferent way. Thanks. – Charychap Mar 19 '15 at 13:55
  • It wouldn't have done, no; the constraint has no impact on the contents of new_column. I just moved the drop of the constraint to where it most logically seemed to go (at least, to me!) - just before the drop of the column. – Boneist Mar 19 '15 at 14:33
1

As an alternative, you can save the old data and create a new table with new parameters. Then insert the old values.

enter image description here

In SQL Server Management Studio:

"your database" => task => generatescripts => select specific database object => "your table" => advanced => types of data to script - schema and data => generate

Pingolin
  • 3,161
  • 6
  • 25
  • 40
vlad
  • 11
  • 2