-1

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?

b00blik
  • 31
  • 4
  • 2
    Huh? Are all those columns empty? If not, how do you convert a number like -200 to a date? Or are there more details you need to share with us? –  Oct 16 '16 at 14:23
  • 1
    There is no `bignumber` in Oracle. And I agree with mathguy, how would you convert numbers like -42, 1e123 to a date? –  Oct 16 '16 at 14:30
  • Actually, I don't know. A have this amazing task in my university. I think these columns could not be empty, also I thought about converting not null values to DATE format. May be smth like this http://stackoverflow.com/questions/11883923/converting-number-to-date-in-oracle – b00blik Oct 16 '16 at 14:52
  • Is this an assignment someone gave you? Ask them - WHAT KIND of numbers are stored? Are the numbers that already exist in the tables in the format 930216 (for example), and are you asked to interpret this to mean RRMMDD in Oracle-speak? You need more information about what's in those columns, and share that with us or else I don't see how we will be able to help. Good luck! –  Oct 16 '16 at 15:28
  • Another question is if PL/SQL supports the invocation of ALTER TABLE. Not sure about that. – FDavidov Oct 16 '16 at 15:39
  • Ok, how we can convert numbers like 90301 (for example) to DATE format? – b00blik Oct 16 '16 at 15:55

1 Answers1

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;
Kacper
  • 4,798
  • 2
  • 19
  • 34