CODEWITHSUNDEEP
sqloracle-databaseplsql
KarmaOfJesus
KarmaOfJesus

Reputation: 101

PLSQL procedure for automatic column type changing

Let's assume following table:

drop table test1;
create table test1(
    A number(10)
);

insert into test1 values (1);
insert into test1 values (10);

So as you can see table TEST1 is already populated. What i need to do is to change types of column A to varchar2. Since this column has values we can't just use following code:

alter table test1 modify A varchar2(10);

So i have wrote stored procedure which:

  1. Renames column A to A1 ->
  2. Then adds new column called A of type varchar2 ->
  3. Then updates column A with values from column A1 ->
  4. And ultimately drops old column A1.

Code which runs this process is following:

create or replace procedure change_col_type_to_varchar2(p_tab in varchar2, p_col in varchar2)
is 
v_string clob;
cursor cur is
select column_name
from all_tab_columns 
where table_name = upper(p_tab) and
column_name in (select regexp_substr(p_col,'[^,]+', 1, level) from dual
                 connect by regexp_substr(p_col, '[^,]+', 1, level) is not null);
begin
    for i in cur loop
        v_string := 'alter table ' || p_tab || ' rename column ' || i.column_name || ' to ' || i.column_name || '1' || ';';
        dbms_lob.append(v_string,''||chr(10)||'');
        dbms_lob.append(v_string, 'alter table ' || p_tab || ' add ' || i.column_name || ' varchar2(10);');
        dbms_lob.append(v_string,''||chr(10)||'');
        dbms_lob.append(v_string, 'update ' || p_tab || ' set ' || i.column_name || ' = ' || i.column_name || '1' || ';');
        dbms_lob.append(v_string,''||chr(10)||'');
        dbms_lob.append(v_string, 'alter table ' || p_tab || ' drop column ' || i.column_name || '1' || ';');
        EXECUTE IMMEDIATE v_string;
        DBMS_OUTPUT.PUT_LINE(v_string);
        v_string := NULL;
    end loop;
end;

I'am trying to apply this procedure to TEST1:

begin
    change_col_type_to_varchar2('TEST1', 'A');
end;

And get error:

Error report -
ORA-23290: This operation may not be combined with any other operation
ORA-06512: at "YAVORSKYIY_DM.CHANGE_COL_TYPE_TO_VARCHAR2", line 19
ORA-06512: at "YAVORSKYIY_DM.CHANGE_COL_TYPE_TO_VARCHAR2", line 19
ORA-06512: at line 2
23290. 00000 -  "This operation may not be combined with any other operation"
*Cause:    ALTER TABLE RENAME COLUMN/CONSTRAINT operation was given in
           conjunction with another ALTER TBALE Operation. This is not
           allowed.
*Action:   Ensure that RENAME COLUMN/CONSTRAINT is the only operation
           specified in the ALTER TABLE.

But just typing :

alter table test1 rename column A to A1;
alter table test1 add A varchar2(100);
update test1 set A = A1;
alter table test1 drop column A1;

Works perfect. Does anybody have any ideas about how to overcome this problem?

Appreciate your help.

Upvotes: 0

Views: 286

Answers (3)

Aliaksandr Iuchyn
Aliaksandr Iuchyn

Reputation: 9

I propose other algorithm.

  1. create new table 'table2' with varchar column;
  2. select all values from table1.A and insert to table2 with to_char() conversion;
  3. drop table1;
  4. rename table2 to table1;
  5. profit!

Upvotes: 1

Ranagal
Ranagal

Reputation: 317

the below will do what you asked for.

declare
    procedure change_col_type_to_varchar2(p_tab in varchar2, p_col in varchar2)
    is 
        v_string clob;
        cursor cur is
        select  column_name
        from    all_tab_columns 
        where   table_name = upper(p_tab) and
                column_name in (select  regexp_substr(p_col,'[^,]+', 1, level) 
                                from    dual
                                connect by regexp_substr(p_col, '[^,]+', 1, level) is not null);
    begin
        for i in cur loop
            v_string := 'alter table ' || p_tab || ' rename column ' || i.column_name || ' to ' || i.column_name || '1';
            execute immediate v_string;
            
            v_string := 'alter table ' || p_tab || ' add ' || i.column_name || ' varchar2(10)';
            execute immediate v_string;
            
            v_string := 'update ' || p_tab || ' set ' || i.column_name || ' = ' || i.column_name || '1' ;
            execute immediate v_string;
            
            v_string := 'alter table ' || p_tab || ' drop column ' || i.column_name || '1' ;
            execute immediate v_string;
            
            v_string := NULL;
        end loop;
       
    end;
begin
        DBMS_OUTPUT.PUT_LINE('Before calling');
        change_col_type_to_varchar2('TEST1','A');
        DBMS_OUTPUT.PUT_LINE('After calling');
end;

Upvotes: 2

gsalem
gsalem

Reputation: 2028

Well, execute each statement alone, instead of concatenating them. And you don't need LOBs, varchar2 for each one should be enough.

Upvotes: 2

Related Questions