Importing Oracle CLOB types - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Importing Oracle CLOB types

Author

Topic

leeWcarl
Starting Member

3 Posts

Posted - 2009-03-03 : 07:45:34

I'm doing some work that involves pulling data from an oracle server into an SQL Server database. Sample query is as follows - selecting the first three fields from "TABLENAME".

SELECT field1, field2, field3
from OPENQUERY(LINKEDSERVERNAME,'SELECT field1, field2, field3 from sa.TABLENAME') AS ROWSET_1

This all works fine and returns the three fields. However some of the other fields I need are CLOB types, and SQL Server throws an error if I try and include them. I dimly remember a way of converting those to VARCHARS on the fly as part of the SELECT clause, but I'm damned if I can find the code snippet again!

I'm sure it was something like this (where "field4" is a CLOB / BLOB)

SELECT field1, field2, field3, field4
from OPENQUERY(LINKEDSERVERNAME,'SELECT field1, field2, field3, CONVERT(field4, varchar(2000)) from sa.TABLENAME') AS ROWSET_1

Any help is greatly appreciated

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 08:11:57

CONVERT(varchar(2000), field4)

E 12°55'05.63"
N 56°04'39.26"

leeWcarl
Starting Member

3 Posts

Posted - 2009-03-03 : 08:20:14

It doesn't seem to like that. Top query works, bottom query (with the CLOB field4 included) throws an error

SELECT
field1, field2 from
OPENQUERY(LINKEDSERVERNAME,'SELECT field1, field2
from sa.TABLENAME') AS ROWSET_1

SELECT
field4, field1, field2 from
OPENQUERY(LINKEDSERVERNAME,'SELECT CONVERT(varchar(2000),field4), field1, field2
from sa.TABLENAME') AS ROWSET_1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 08:55:30

Have a look at the CONVERT syntax for the oracle linked server.

E 12°55'05.63"
N 56°04'39.26"

leeWcarl
Starting Member

3 Posts

Posted - 2009-03-03 : 10:21:53

SELECT
field1, field2 field4 from
OPENQUERY(LINKEDSERVERNAME,'SELECT field1, field2, CAST(field4 as VARCHAR(4000)) as field4
from sa.TABLENAME') AS ROWSET_1

ARGH! Its as simple as that. I'm sure I tried that syntax and it didn't work. Now it does. Oh well

Thanks for the time on this Peso. I'll make sure this is saved somewhere safe for the next time it raises its head.

:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 10:23:28

You can even CAST AS VARHAR(8000) if you want to.
SQL Server has a limit of 8000 characters for a VARCHAR variable or table column.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources