Converting and altering Oracle table columns

Question

I need help for specific question.

I need to write some Oracle Database procedure, which will do next:

• Receiving a table name as a parameter;
• Taking all fields with numeric value (number/bignumber/numeric/etc.) excepting primary key;
• Convert these columns to to DATE format;
• Add these new formatted columns to this table (I mean an ALTER TABLE ADD COLUMN).

How can I do this?

Answer 1

As all comments below post said it is not possible to convert any number to date. You need to know what is format of number. Below is example procedure to add columns and convert all numbers but it is based on condition that numbers store dates in format ddmmyyyy for example 12102014 will give date 12-Oct-2014 but if number have date in other format it will throw an exception.

create or replace procedure columns_change(t_name in varchar2) as
col varchar2(30);
begin
for x in (select column_name from all_tab_columns where table_name = t_name and data_type in ('NUMBER'/*add whatever you need*/)) loop
col := x.column_name;
if (length(col)> 29) then --that's to avoid extending name to more than 30 characters
col := substr(col,1,29);
end if;
execute immediate 'alter table ' || t_name || ' add d' || col || ' date';
execute immediate 'update ' || t_name || ' set ' || col || ' = to_date(' || x.column_name || ',''ddmmyyyy'')';
end loop;
end;