The issue has been resolved but I would like to know if I had been able to do it better.
For the past few months we've been plagued with the task of converting an ERP related database from Oracle to SQL Server, I say plagued cause we received a lot of errors when we tried to online the backup we had received. In the end it turned out we were being sabotaged by the company that used to support the Oracle version. (Because of corporate secrets stored in the database I can't mention exact table or column names)
After finally getting it up and running we looked at what the best way was to migrate, we found a Microsoft tool called SSMA and began migrating. Currently the structure of the database has been completely migrated only the data remains to make the same journey. Here we hit a problem, the SSMA tool error'ed out a 'year month and day parameters describe an un-representable datetime' on literally several thousands of tables in the database.
We singled out on table giving the problem and checked it, this table (call it table A) has 6 date fields, 4 of these 6 seem to indicate start and stop dates, while the other 2 seem to be setup and completed dates.
Table A
...
Start_Time_User1 date
Finish_Time_User1 date
...
ect...
I made a distinct select on all the date colums to see the values in them and maybe see what was going on, 4 of the columns turned out to contain no values while the other 2 had the following: 01-01-00 or 00-00-00.
A to_char on these results with the pattern dd-mm-yyyy gave me that these values were 01-01-1900 and 00-00-0000.
I quickly searched the web for a way to recognise invalid dates and tested them on Table A, when this found 00-00-00, I made it an update statement turning 00-00-00 into 02-01-(19)00.
The next check with the SSMA tool revealed the 'year month and day parameters describe an un-representable datetime' was gone, however the momentous task of finding all these invalid dates across the database was still waiting.
The first thing I did was to find out how many date columns were in the database:
select dtc.owner || '.' || dtc.table_name, dtc.column_name
from dba_tab_columns dtc
inner join dba_tables dt on dtc.owner = dt.owner and dtc.table_name = dt.table_name
where data_type = 'DATE';
Using this sql I came to the result that there were 23458 date columns in the database, my boss suggested that I would manually make the update statements but I came to the conclusion that this could take weeks if not months to prepare all.
So I created a script to dynamically update them all.
I created a table on the server first to hold all the column/table names, so that I could loop through them.
The idea was to execute the dynamically created statements, however this failed.
When I tried to execute I got the error ORA-01861: literal does not match format string, I looked for this error but didn't find an answer that really explained what I was doing wrong and since I was pressed for time I decided to take a detour.
I saved the statements into a new table, I then exported the table to a csv file, to my horror I discovered that the export, although successfully, had capped the length of the line causing the sql statement to be spread over several lines and thus unusable.
I exported the statements then to sql server, and then exported them to an sql script, however this wasn't helpful either cause I stopped each line after a certain amount of characters.
In the end I just copy pasted them from the sql server output to sql plus, and so I was able to solve the final errors that were blocking the migration.
Off course it could have been better if the update statement ran directly like in the following script.
If anyone has an idea what I did wrong please divulge, and for those who say I'm again using the forum to abuse and let other people solve my issues I have resolved the issue on my own this time I just want to know if there was a way that I could have done it better.
SET ECHO ON
SET SERVEROUTPUT ON
--variable dsql1a varchar2(4000);
--variable dsql2a varchar2(4000);
DECLARE
--8
dsql1 varchar2(4000);
dsql2 varchar2(4000);
cursor c1 is
select tablename,columnname
from system.ClairvuTable;
BEGIN
dsql1 := '';
dsql2 := '';
--22
FOR table_rec in c1
LOOP
--dbms_output.put_line(table_rec.tablename || '__' || table_rec.columnname);
--26
dsql1 := dsql1 || q'[UPDATE ]' || table_rec.tablename || q'[ set ]' || table_rec.columnname || q'[='02-01-00' WHERE ]';
dsql1 := dsql1 || q'[CASE WHEN LENGTH(to_char(]'|| table_rec.columnname ||q'[,'yyyy-mm-dd')) = 10 THEN ]';
dsql1 := dsql1 || q'[CASE WHEN TRANSLATE(SUBSTR(to_char(]'|| table_rec.columnname ||q'[,'yyyy-mm-dd'),1,4),'0123456789','NNNNNNNNNN') = 'NNNN' THEN ]';
dsql1 := dsql1 || q'[CASE WHEN TO_NUMBER(SUBSTR(to_char(]'|| table_rec.columnname ||q'[,'yyyy-mm-dd'),1,4)) BETWEEN 1900 AND 2100 THEN ]';
dsql1 := dsql1 || q'[CASE WHEN SUBSTR(to_char(]'|| table_rec.columnname ||q'[,'yyyy-mm-dd'),5,1) = '-' THEN ]';
dsql1 := dsql1 || q'[CASE WHEN TRANSLATE(SUBSTR(to_char(]'|| table_rec.columnname ||q'[,'yyyy-mm-dd'),6,2),'0123456789','NNNNNNNNNN') = 'NN' THEN ]';
dsql1 := dsql1 || q'[CASE WHEN TO_NUMBER(SUBSTR(to_char(]'|| table_rec.columnname ||q'[,'yyyy-mm-dd'),6,2)) BETWEEN 1 AND 12 THEN ]';
dsql1 := dsql1 || q'[CASE WHEN SUBSTR(to_char(]'|| table_rec.columnname ||q'[,'yyyy-mm-dd'),8,1) = '-' THEN ]';
dsql1 := dsql1 || q'[CASE WHEN TRANSLATE(SUBSTR(to_char(]'|| table_rec.columnname ||q'[,'yyyy-mm-dd'),9,2),'0123456789','NNNNNNNNNN') = 'NN' THEN ]';
dsql1 := dsql1 || q'[CASE WHEN TO_NUMBER(SUBSTR(to_char(]'|| table_rec.columnname ||q'[,'yyyy-mm-dd'),9,2)) BETWEEN 1 AND 31 THEN ]';
dsql1 := dsql1 || q'[CASE WHEN SUBSTR(to_char(]'|| table_rec.columnname ||q'[,'yyyy-mm-dd'),9,2) <> '31' ]';
dsql1 := dsql1 || q'[OR SUBSTR(to_char(]'|| table_rec.columnname ||q'[,'yyyy-mm-dd'),6,2) IN ('01','03','05','07','08','10','12') THEN ]';
dsql1 := dsql1 || q'[CASE WHEN SUBSTR(to_char(]'|| table_rec.columnname ||q'[,'yyyy-mm-dd'),9,2) <> '30' ]';
dsql1 := dsql1 || q'[OR SUBSTR(to_char(]'|| table_rec.columnname ||q'[,'yyyy-mm-dd'),6,2) NOT IN ('02') THEN ]';
dsql1 := dsql1 || q'[CASE WHEN SUBSTR(to_char(]'|| table_rec.columnname ||q'[,'yyyy-mm-dd'),6,2) <> '02' ]';
dsql1 := dsql1 || q'[OR SUBSTR(to_char(]'|| table_rec.columnname ||q'[,'yyyy-mm-dd'),9,2) NOT IN ('29') ]';
dsql1 := dsql1 || q'[OR (MOD(TO_NUMBER(SUBSTR (to_char(]'|| table_rec.columnname ||q'[,'yyyy-mm-dd'),1,4)),4) = 0 ]';
dsql1 := dsql1 || q'[AND ]';
dsql1 := dsql1 || q'[(MOD(TO_NUMBER(SUBSTR (to_char(]'|| table_rec.columnname ||q'[,'yyyy-mm-dd'),1,4)),100) <> 0 ]';
dsql1 := dsql1 || q'[OR MOD(TO_NUMBER(SUBSTR (to_char(]'|| table_rec.columnname ||q'[,'yyyy-mm-dd'),1,4)),1000) = 0)) ]';
dsql1 := dsql1 || q'[THEN ]';
dsql1 := dsql1 || q'['Y' ]';
dsql1 := dsql1 || q'[END END END END END END END END END END END END IS NULL]';
--dsql1 := dsql1 || q'[IS NULL ]';
dsql2:= 'update ' || table_rec.tablename || ' set ' || table_rec.columnname || ' = to_date(to_char(' || table_rec.columnname || q'[,'DD-Mon-yyyy HH24:MI:SS'),'DD-Mon-yyyy HH24:MI:SS')]';
--:dsql1a := dsql1;
insert into system.ClairvuTable2(ZeroUpdate,ColumUpdate) values (dsql1,dsql2);
--EXECUTE IMMEDIATE dsql1;
--EXECUTE IMMEDIATE :dsql2;
--dbms_output.put_line(dsql1);
dsql1 := '';
dsql2 := '';
END LOOP;
END;
/
Edited by: BluShadow on 19-Aug-2011 13:30
added {noformat}
{noformat} tags. Please read {message:id=9360002} to learn to do this yourself.