Re: find/replace on long field

From: Tim Arnold <timkarnold_at_comcast.net>
Date: Tue, 31 Oct 2006 08:55:53 -0500
Message-ID: <b9idnTirVcZ4yNrYnZ2dnUVZ_sOdnZ2d@comcast.com>

"Hugh" <hughmclaughlin_at_gmail.com> wrote in message news:1161892429.832892.284930_at_m73g2000cwd.googlegroups.com...
> I have a table where I am searching for text in a long field to be
> replaced in Oracle 8.
>
>
>
> I have created something like
>
> update table_tbl
> set FIELD =
> replace(FIELD,'text_to_be_Replaced','new_text_to_be_inserted')
> where FIELD like '%'||'text_to_be_Replaced'||'%'
>
> but that does not work since you can not run an oracle function on a
> Long field. Since this database works with 3rd party software,
> changing the field to a CLOB is not a viable option.
>
>
> any recommendations?
>

Might try PL/SQL. Use replace on varchar2. See MetaLink note 1005147.6

Solution Description:

---

The easiest way to "copy" a LONG data into a VARCHAR2 column is via PL/SQL. For instance, the following anonymous PL/SQL block copies a LONG column from table LONGTABLE into a VARCHAR2 column from table VARCHARTABLE, with the assumption that LONGTABLE.LONGCOLUMN does not exceed 2000 bytes in length:

declare
  string varchar2(2000);
  character char(1);
begin
  select longcolumn, pk into string, character from longtable;   insert into varchartable values (string, character); end;
/ Received on Tue Oct 31 2006 - 07:55:53 CST