Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to access data in an Oracle 11g database from SQL-Server 2008

I set up a linked server and when I execute

select * from [Link_server_name]..Oracle_schema.Oracle_table

and Oracle_table contains Number and varchar2 columns, all works as excepted.

But when the Oracle_table contains a CLOB column, I get the following error:

Der OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' hat die Meldung 'Unspecified error' zurückgeben.

Der OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' hat die Meldung 'Oracle error occurred, but error message could not be retrieved from Oracle.' zurückgeben.

Der OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' hat die Meldung 'Data type is not supported.' zurückgeben.

Msg 7306, Level 16, State 2, Line 1

Die '"MCCAPP"."DOGGRUPPEN"'-Tabelle vom OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' kann nicht geöffnet werden.

Using OPENQUERY

SELECT * FROM OPENQUERY([L_V407SR8T], 'Select CLOB_COLUMN from Oracle_table'  )

I get

Der OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' hat die Meldung 'Oracle error occurred, but error message could not be retrieved from Oracle.' zurückgeben. Der OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' hat die Meldung 'Data type is not supported.' zurückgeben.

Please excuse German error messages.

My question: Is there any way to read CLOB columns via linked Servers?

EDIT:

  • SQL Server Import and Export Wizard seems to depend on OLE DB too and sucks on the same tables
  • I'm asking myself, if the problem depends on the character sets uses, but I can't change them
  • As practical work around, I use some PowerShell scripts to get at the data, but
share|improve this question
    
You should be able to read the CLOB if you at least can convert it first ... msdn.microsoft.com/en-us/library/ms378813.aspx –  jcolebrand Jan 6 '11 at 5:06

2 Answers 2

Download and install an Oracle 11 database client on your SQL Server 2008 machine.

Set up a linked server using an Oracle OLE DB provider (OraOLEDB.Oracle).

Make sure "Allow InProcess" is enabled in Provider options.

The Oracle 11 OLE DB client supports CLOBs.

share|improve this answer

A comment to an answer SO question How to read a CLOB column in Oracle using OleDb ?

contains a link to old Microsoft Support post with the following statement

Oracle 8.x-specific data types, such as CLOB, BLOB, BFILE, NCHAR, NCLOB, and NVARCHAR2, are not supported.

It would be nice to find some more current references.

I did some Google research and found only workarounds. I fear the data type is still not supported.

Edit 02/05/2011

I really do not understand why Microsoft isn't able to fix this issue. The following PowerShell Script uses OLE-DB to read a table containing a CLOB column from an Oracle database and insert the data into an existing similar table on Sql-Server using bulkCopy which is a very efficient way.

Adjust the following connection strings, query and table name to your installation:

# Here I use OLE-DB to read the Oracle database
$ConnectionString ="Server=ignore;User Id=myUser;Password=myPassword;Data Source=myTns;Provider=OraOLEDB.Oracle;"

# Here I use .NET SqlClient
$SqlClientConnString = "Data Source=localhost;Initial Catalog=myDestinationDB;Integrated Security=True"

$sql = "select int_col, clob_col from TABLE_WITH_CLOB"
$tableName = "COPY_OF_CLOB_TABLE"

The following code copies the oracle table into a sql-server table

$OLEDBConn = New-Object System.Data.OleDb.OleDbConnection($ConnectionString)
$OLEDBConn.open()
$readcmd = New-Object system.Data.OleDb.OleDbCommand($sql,$OLEDBConn)
$readcmd.CommandTimeout = '300'
$da = New-Object system.Data.OleDb.OleDbDataAdapter($readcmd)
$dt = New-Object system.Data.datatable
[void]$da.fill($dt)
$OLEDBConn.close()
#Write-Output $dt


if ($dt)
{
    try
    {
        $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $SqlClientConnString
        $bulkCopy.DestinationTableName = $tableName
        $bulkCopy.BatchSize = 50000
        $bulkCopy.BulkCopyTimeout = 0
        $bulkCopy.WriteToServer($dt)
    }
    catch
    {
        $ex = $_.Exception
        #Write-LogMessage -Source 'Write-DataTable' -Message "$($connectionName):$ex.Message"
        Write-Error "Write-DataTable$($connectionName):$ex.Message"
        continue
    }
}

BTW: the code is based on Chad Millers Really Simple Data Dictionary codeplex project

Currently using PowerShell is the only way I know to copy Data with CLOBs from Oracle to Sql-Server not using C# or any third party tools.

I didn't succeed using neither Import/Export Wizard nor linked servers.

share|improve this answer
    
grrrr that sucks considering it's been three point releases for ORA and at least one point release for MS since then ... AHA No new updates are planned for future releases of these components including their support against versions later than Oracle 8i. –  jcolebrand Jan 14 '11 at 16:56
    
I really do not understand why Microsoft isn't able to fix this issue? For the same reason Microsoft stopped supporting the dotnet framework Oracle client library - "other people do it better", if I ruthlessly paraphrase their responses in the past. No love lost between those two, I tell you. –  ScottCher Mar 2 '11 at 19:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.