A requirement
came up to migrate some MS SQL data from SharePoint into Oracle and most of
this data has some html formatting tags and more than 4000 characters requiring
CLOB columns on Oracle end.
First option is
to try SQL Developer and as the source was given as a 'access' database file,
tried to connect to access and noticed there's NO access connection tab in SQL
Developer 4.1.1 as it uses jdk8 and there's no jdbc-odbc bridge in jdk8 as per this post https://community.oracle.com/thread/3642078 so
had to launch SQL Developer 4.0.2 which has "Access" tab.
Now the other
issue is connecting to Access gives "Data source name not found and no
default driver specified" error which is due to missing 64bit MS Access
driver as I was using 64 bit OS. So uninstalled my office suite and installed
the Access driver using http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255 and
now I can connect to Access. However the source data is
supposed to be extracted from a query from some of the access base tables and
for some reason the access connection shows the queries as 'views'.
So decided to
switch to MS SQL connection (which was the original source for this access)
and connected SQL
Developer to MS SQL using jTDS driver and now I
can connect to MS SQL.
And from there
on, it's easier than expected to load the data using 'copy to oracle' option
for the MS SQL tables and it nicely converted the nvarchar data into CLOB
columns.
However one small
glitch is it failed to load the data from a MS SQL table that has ":"
in it's name, but strangely it didn't throw any errors simply says
load completed with neither table nor data present in Oracle. Also for the
tables it worked ok, Oracle table name got trimmed to 30 characters limit on
Oracle end.
So renamed the
tables on MS SQL end without ":" character and now the load succeeded
and table record count and max(length(column_name)) returned the same value on
both ends...a pleasing result at the end!
