Validating Long RAW column in oracle with SQL Server after migration using SSMA

2
votes

I have an oracle 11g source which I hae migrated to SQL Server 2008 using SQL Server Migration assistant tool from microsoft. The oracle source had a table that contained a long raw column. The type mapping that was used for migration was long raw -> varbinary(max). When I view the oracle data using SQL developer, the column is only showing RAW in every rows. However in SQL Server, I am able to view the migrated data in string format.

What can I do to validate or crosscheck the data in oracle to that with the data in SQL server, or atleast is validation of long raw column possible?

1
sqloraclesql-server-2008oracle11gmigration
Since raw is a binary data type, it seems odd that it was mapped to a character data type; I would have expected varbinary(max) instead. I know nothing about SSMA, but this may be a case where you have to migrate the binary data yourself. As for validating it, it depends what the data actually is but I assume you can write a short script to query a few rows and then try opening the binary data in whatever application knows how to read it. - Pondlife
Sorry..my bad. It was not varchar(max) but varbinary(max). I have edited the question. - Dibin

1 Answers

1
votes

If you still have access to your Oracle 11g database server: then, on the Oracle database, create a remote database link to the SQL Server database. See http://www.dba-oracle.com/t_database_link_sql_server_oracle.htm for info.

Then, still on Oracle, write a PLSQL routine which reads and compares data from the local Oracle table LONG RAW column with data from the remote SQL Server table (the migrated table).

You may need to use the built in PLSQL UTL_RAW package to read and compare the data. For info on how to use it see http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/u_raw.htm#BABFGDDG

For info on how LONG RAW columns work in 11g in general see: http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/datatypes.htm#CJAEGDEB http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm#SQLRF00201

Note that when working with older Oracle databases such as Oracle 8i, if your Oracle LONG RAW columns are greater than 32K you will not be able to use PLSQL to read them, and you must use another programming language to do it. See http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:201012348073