Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: LONG Column Question

Re: LONG Column Question

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/03/07
Message-ID: <8a1ica$k9n$1@nnrp1.deja.com>#1/1

In article <8a1edl$see$2_at_ebaynews1.EBay.Sun.COM>,   gosar_at_bhavesh.ebay.sun.com wrote:
> Hi,
>
> We have a Field which was a datatype with Varchar2(2000)...we used to
> Load data thru SQL*Loader into the Field applying the function
 "replace(:<field>,'^M','chr(10)||chr(13)')"
> This was due to the fact that Data came in thru the Web-form/email,
 etc and had a ^M....which we translated
> into the a New Line so that Data appears in proper format on the GUI
 Screen.
>
> WE had a Business Requirement and Have to Change this Field to a LONG
 Field...
> How do we take care of the ^M replacement...since we cannot apply the
 replace function during or after
> the load of data....The Data without the above comes in one contiuous
 line...
>
> Any help/pointer is appreciated.
>
> TIA,
> Bg
>
>

as long as the LONG is 32k or less, we can do it in a trigger (an AFTER trigger).

You can:

  select LONG_COLUMN into plsql-variable FROM T where ....;   plsql-variable := f(plsql-variable);
  update T set LONG_COLUMN = plsql-variable where....;

as long as the long column is 32k or less.

If you read http://osi.oracle.com/~tkyte/Mutate/index.html, you'll find a general purpose way to avoid mutating tables. We can use the same logic to capture the ROWIDS of newly inserted rows into a plsql table (array) in an AFTER INSERT, FOR EACH ROW trigger and then process the newly inserted rows in an AFTER INSERT TRIGGER -- fetching out the long, doing the replace, and then an update to put it back..

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Mar 07 2000 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US