Re: Sql Server NTEXT fields

  • From: "Grant Allen" <gxallen@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 3 May 2006 10:17:43 +1000

On 5/3/06, Goulet, Dick <DGoulet@xxxxxxxx> wrote: 

Folks,

        I've got a copy of Oracle's Gateway to Sql Server so that we can
integrate data from a third party, mom & pop created application with
other data stored in Oracle.  This application has a number of NTEXT
fields where comments and the like are being stored, but in Rich Text
Format.  Now from the manual:

        You cannot use SQL*Plus to select data from a column defined as
Microsoft SQL
        Server data type IMAGE, TEXT or NTEXT when the data is greater
than 80
        characters in length.

Anybody know how to strip the rtf stuff out especially from the
Microsoft side??


Two techniques combined will help - I've done this before, though
performance wasn't one of the design criteria.

On the SQL Server side, you can create a view based on cast/convert of
the NTEXT field, to varchar. The SQL Server limit on varchar is 8000,
so watch your equivalent Oracle declarations for Oracle's 4000 limit. On top of that, SQL Server's "stuff" function (I kid you not, that's
its name) is equivalent to Oracle's Replace function, and you can use
this to strip out any characters you don't want (like all the RTF
formatting crap).

Roll all this together, and you get all the hard work done on the SQL
Server side, and can just get on with life on the Oracle side of
things.

Happy hacking.

Ciao
Fuzzy
:-)
--
//www.freelists.org/webpage/oracle-l


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 05-2006