CODEWITHSUNDEEP
oracle-databaseplsqladdition
learning_spark
learning_spark

Reputation: 669

Programmatically adding columns to an Oracle table

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

Upvotes: 0

Views: 879

Answers (2)

wardies
wardies

Reputation: 1259

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.

Upvotes: 1

wolφi
wolφi

Reputation: 8361

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)));

Upvotes: 4

Related Questions