I need help for specific question.
I need to write some Oracle Database procedure, which will do next:
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;