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

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Fri, 8 Jan 2010 06:29:17 -0800 (PST)
Message-ID: <566ef7e9-c38c-43a2-8e12-29c006559181_at_j19g2000yqk.googlegroups.com>

On Jan 7, 10:10 am, Jeremy <jeremy0..._at_gmail.com> wrote:
> Hi
>
> -------------------------
> Oracle 10g R2 10.2.0.4.0
> 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