asked on

Move CLOB from Oracle to Sybase: Direct Connection

I have an Oracle 10g database with large amounts of text (html markup if you care) stored as CLOB datatype. I want to be able to retrieve that text out of Oracle (on Solaris) and into a Sybase database (on Win). I'm being told by my developer that this object type is not supported by Sybase and that we need to create some kind of text export from Oracle to be read back into Sybase.

I don't care about performance (within reason) as these systems are not our on our production side and have many hours of inactivity in a day.

Can anyone verify this, and if you know of a way to directly connect the two, please elaborate?!

Joe Woodhouse

Assuming you mean Sybase ASE (there are three Sybase database products) then I'm not sure I understand why your developer says that. Sybase supports a "text" datatype (character LOB) and "image" (binary LOB). These have both been around, well, for over 12 years at least. If you don't need any HTML handling and just want to store and retrieve this as very large character data, "text" is all you need.

Note that dealing with chunks over 32Kb (64Kb in some versions, I forget which exactly) you have to start using text pointers and the "sp_readtext"/"sp_writetext" procedures which are a bit clunky. But certainly you have have gigabytes of plain text in a single text field.

With various paid extras you can have free text search capabilities, and/or XML support directly in ASE.

SOLUTION

Joe Woodhouse

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

ASKER CERTIFIED SOLUTION

grant300

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer