Re: LONG to CLOB within a trigger - alternative to synching data values?

From: Mark D Powell <>
Date: Fri, 8 Jan 2010 06:29:17 -0800 (PST)
Message-ID: <>

On Jan 7, 10:10 am, Jeremy <> wrote:
> Hi
> -------------------------
> Oracle 10g R2
> Windows Server 2003
> -------------------------
> I realise it is not possible to reference the :new.longcolumn when the
> longcolumn is of datatype LONG.
> We have a legacy table with a LONG column. We wanted to transition this
> to a CLOB but actually modify the application code over time (there are
> many potential dependencies on this column (and by the way the app was
> originally designed before CLOB was a supported datatype!).
> We had thought we could simply:
> alter table mytab
> add
> (clob_col       clob);
> and then in a trigger put e.g.
> if inserting or updating then
>   :new.clob_col := :new.oldlongcolumn;
> end if;
> Now, we cannot do this owing the lack of support fro this in Oracle.
> Is there an alternative cunning method by which we could easily
> synchronise the new CLOB column with the LONG column's data content?
> --
> jeremy

I have never tried to perform Long to CLOB conversion in a trigger but it should be possible.

Use "DBMS_LOB.Copy() function can copy all or part of a source internal CLOB to a
CLOB or BLOB to a BLOB."

Quote from Oracle support document: How to convert LONG to a CLOB #ID 282464.1

HTH -- Mark D Powell -- Received on Fri Jan 08 2010 - 08:29:17 CST

Original text of this message