SSMA for Oracle - Error: No columns were returned from the database for table 'Foo'

Error:   SQL Server Migration Assistant for Oracle fails to load columns with error "Loading columns for table or view 'Table_Name_1' failed."

Solution:   Connect to the Oracle instance using the OLE DB provider instead of the default .NET Oracle Client provider.

On a recent client project, we were tasked with upgrading a very old Oracle 8.0.5 database to SQL Server 2008 R2.  Using SQL Server Migration Assistant for Oracle (found here), we were able to connect to the Oracle instance and view the existing schema.  At this point, everything looked like it was running as expected.  However, we hit an issue when expanding any table to view the columns and also hit the same set of errors when trying to convert the schema.

The errors appeared in the following format:

Oracle Server Object Collector error: table : Table_Name_1
ORA-01405: fetched column value is NULL
Loading columns for table or view 'Table_Name_1' failed.
Oracle Server Object Collector error: table : Table_Name_1
No columns were returned from the database for table 'Table_Name_1'.

Searching online, we noticed a few people were asking for assistance with the same set of errors, but no one had seemed to solve the problem.  After contacting one of the original developers of the SSMA tool set, the simple solution is to change the default connection (data access provider) for the connection to the Oracle database.  The default connection uses the .NET Oracle Client Provider.  Disconnecting from the Oracle instance and reconnecting using the OLE DB provider solved the problem for us.  For more information on the connection and provider, take a look at the documentation here.

Hope it helps,
Sam Lester (MSFT)