I have a requirement to update a LONG column in a table; I know that I should not be using LONGs but the directive I have bee given does not allow for reconstruction of the affected table.
The table definition is:
CREATE TABLE s1_debit_credit_memo
(
debit_credit_nbr number not null,
plc_id varchar2(10) not null,
serial_nbr number not null,
memo_comment long null,
CONSTRAINT pk_debit_credit_memo PRIMARY KEY (debit_credit_nbr)
using index
tablespace smartsoft_index
)
tablespace smartsoft_data
/
I am trying to remove line feed and carriage return characters from the memo_comment column.
Something like this: REPLACE(CAST(dcm.memo_comment AS varchar2(2500)), '~r~n', '')
To further complicate things, I need to do this in a view.
What approach would you take to accomplish this?
Thanks
Murray