Re: Does SELECT REPLACE work on Long fields?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 24 Jun 1998 16:29:34 GMT
Message-ID: <3597270d.12732448@192.86.155.100>

A copy of this was sent to dolans_at_stripe.Colorado.EDU (Sean Dolan) (if that email address didn't require changing) On 24 Jun 98 13:34:40 GMT, you wrote:

> I am trying to replace the carriage return (chr(10) with '<br>' in a long field in an Oracle 7.3.4 dbase. I have tried to do an update from SQL/Plus, but it gives me an error of 'inconsistent datatypes'... so instead, I decided to write a simple PL/SQL program. It almost works. The following line is where the code "chokes":
>
>SELECT REPLACE (lp, chr(10), '<br>') into lp from dual where id=something
>
>Remember, it it querying a long datatype first (the lp variable). Now it works on SOME of the records but not others - I think it has to do with the size of those fields. For example, I am successful on record 3 (which has 1300 characters) but not on record 7 (which has 2800 characters).
>
>Could anyone help with this and maybe suggest a means to get around this?
>
>Thanks,
>Sean Dolan

its because you are binding a character function (replace) using SQL in pl/sql and Pl/SQL believes the biggest string a character function will return is 2000 bytes in 7.x and 4000 in 8.x...

Instead of using sql to do this, just use pl/sql. pl/sql will limit you to 32k. So, code:

lp := replace( lp, chr(10), '<br>' );

and you will be OK. If you need to make it 'permanent', that is update the row, code something like:

1 begin

  2     for x in ( select rowid rid, data from tabe ) loop
  3          x.data := replace( x.data, chr(10), '<br>' );
  4          update table set data = x.data where rowid = x.rid;
  5     end loop;

6* end;
SQL> / Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jun 24 1998 - 11:29:34 CDT