Programmatically adding columns to an Oracle table

Question

I am trying to do something really simple... Let me cite an example. Suppose I have a table with several columns and let’s call one of the columns “u”. This column has only 3 distinct values viz. 0, 1 and 2. I want to create three additional columns u_0, u_1 and u_2 as follows:

If u = 0, then u_0 = 1, u_1 = 0 and u_2 = 0
If u = 1, then u_0 = 0, u_1 = 1 and u_2 = 0
If u = 2, then u_0 = 0, u_1 = 0 and u_2 = 1

This is just an example. There are several columns like u, with more than three distinct values and I need to do similar addition of columns for all such variables. Is it possible to write a program in Oracle PL/SQL, which can efficiently do this? Thanks and regards, Dibyendu

I don't think there is an easy way to do this, and for good reasons. Oracle has the "Execute immediate" statement, but it does not allow DDL. DDL requires an implicit commit. Why not write a sqlplus script instead? — OldProgrammer
– OldProgrammer, Commented Jan 12, 2013 at 3:16
@LeorA - Execute immediate certainly does work with DDL. For example, begin execute immediate 'create table test1(a number)'; end;. Dibyendu - How often do these columns need to change? Do you only need to create the columns once? Do the values in U_0, U_1, and U_2 need to stay consistent with changes to U? — Jon Heller
– Jon Heller, Commented Jan 12, 2013 at 5:10
Let us assume that the values are never changed... I need to create the additional columns once only. Best regards, Dibyendu — learning_spark
– learning_spark, Commented Jan 12, 2013 at 5:48
Programmatically adding columns to an Oracle table - this is very bad idea. — Art
– Art, Commented Jan 14, 2013 at 14:39

wolφi · Accepted Answer · 2013-01-12 08:09:25Z

4

How about virtual columns? You'll have to add them manually, but the values are computed (and updated) programmatically:

ALTER TABLE mytable ADD (u_0 NUMBER GENERATED ALWAYS
    AS (CASE u WHEN 0 THEN 1 ELSE 0 END) CHECK (u_0 IN (0,1)));

answered Jan 12, 2013 at 8:09

wolφi

8,3814 gold badges42 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wardies · Accepted Answer · 2013-01-12 13:48:26Z

I think the following stored procedure does what you need:

create or replace procedure expandcolumn 
(
  colname in varchar2  
) as 
  v_max integer;
  v_col_index integer := 0;
  v_sql_ddl varchar2(2000) := 'alter table demo add (';
  v_sql_update varchar2(2000) := 'update demo set ';
  v_sep varchar2(3) := ', ';
begin
  -- Retrieve the maximum value of the column so we know how many columns to add
  execute immediate 'select max(' || colname || ') from demo' into v_max;

  -- Starting from zero, prepare the DDL and UPDATE statements for each new column
  for v_col_index in 0..v_max loop
    if v_col_index = v_max then
      v_sep := null; -- We don't need a comma separator after the last column
    end if;

    v_sql_ddl := v_sql_ddl || colname || '_' || v_col_index || ' number(1)' || v_sep;
    v_sql_update := v_sql_update || colname || '_' || v_col_index ||
      '=decode(' || colname || ',' || v_col_index || ', 1, 0)' || v_sep;
  end loop;

  v_sql_ddl := v_sql_ddl || ')';

  execute immediate v_sql_ddl; -- Add the new columns to the demo table
  execute immediate v_sql_update; -- Set the new column values (implicit commit)
end expandcolumn;

Call it with the original column name you wish to expand to multi-columns, e.g.

create table demo (u number(1));
insert into demo values (0);
insert into demo values (2);
insert into demo values (1);
commit;
exec expandcolumn('U');
select * from demo;

         U        U_0        U_1        U_2
---------- ---------- ---------- ----------
         0          1          0          0
         2          0          0          1
         1          0          1          0

Of course, you will likely need to parametrize more things (such as table name and column width) but I've left those out for simplicity.

Collectives™ on Stack Overflow

Programmatically adding columns to an Oracle table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related