String functions on Long types in a data extract

[MartinF]

Hi,

I have a query that pulls 1000's of records from about six different joined tables to create an extract that i need. However, i need to filter out line feeds and carriage returns. Normally i would use the REPLACE(... function, but you cant use this on Long types.

I believe i need to use PL/SQL to do this, but how can i do this on 1000's of rows, rather than reading a single value into a variable etc.

Basically what i wish to end up with is an extract of data, but with the long field filtered of all certain characters.

Any help with this would be greatly appreciated.

 

[SantaMufasa]

Martin,

Beginning with Oracle 9i, Character Large OBjects (CLOBs) replaced LONGs as Oracle's method of storing data in cells larger than 2000 characters. The benefit of CLOBs is that they can be arguments in Oracle functions just as easily as any other VARCHAR expression. This means that you can apply functions such as REPLACE, TRANSLATE, SUBSTR, et cetera, against CLOB columns.

Is there a business-critical reason that you are using Oracle 8, a version that was obsoleted three versions and many years ago?

If you must manipulate the contents of LONG columns, then this factor, alone, may justify your upgrading to at least Oracle 10g (and possibly to Oracle 11).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
A fo ben, bid bont.