Re: LONG Column Question

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..

--
[Quoted] 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.