Accessing Oracle through SQL Server

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 2000 Forums

SQL Server Development (2000)

Accessing Oracle through SQL Server

Author

Topic

alph2004
Starting Member

7 Posts

Posted - 2004-11-29 : 05:02:20

I have an Oracle linked server to SQl Server 2000 which works fine for the most part. However, I can't access a particular Oracle table as it uses the CLOB datatype which is unsupported in SQL Server. Is there a way around this?

Thanks

Alph

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-11-30 : 00:51:43

have not tried this...but..
Can you create a view that converts the CLOB in to multiple varchars.

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

alph2004
Starting Member

7 Posts

Posted - 2004-11-30 : 04:49:58

hmmmmm..... I'd not thought about that.

How would you do that though?

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-11-30 : 05:34:31

use the SUBSTR function..

DBMS_LOB.SUBSTR (
lob_loc IN CLOB CHARACTER SET ANY_CS,
amount IN INTEGER := 32767,
offset IN INTEGER := 1)
RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET;

something like this... (change the syntax as appropriate to Oracle..)


Create or Replace view My_Test_view as
    SELECT <col1>, <col2>..., DBMS_LOB.SUBSTR (<CLOB>, 8000, 1) as CLOB1, DBMS_LOB.SUBSTR (<CLOB>, 8000, 8001) as CLOB2, DBMS_LOB.SUBSTR (<CLOB>, 8000, 16001) as CLOB3.....<depends on how many bytes your CLOB data might be...>
    FROM <table_name>

hope it helps....

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources