Re: LONG to CLOB within a trigger - alternative to synching data values?
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Sat, 9 Jan 2010 12:33:34 -0800 (PST)
Message-ID: <925740be-b4ed-45b7-b6ea-fabc72a5f5dd_at_c34g2000yqn.googlegroups.com>
On Jan 8, 9:51 am, Jeremy <jeremy0..._at_gmail.com> wrote:
> In article <566ef7e9-c38c-43a2-8e12-29c006559181
> _at_j19g2000yqk.googlegroups.com>, Mark.Powe..._at_hp.com says...
>
> > 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
>
> Will this not run into the same issue when referring to
> :new.oldlongcolumn?
>
> --
> jeremy
Date: Sat, 9 Jan 2010 12:33:34 -0800 (PST)
Message-ID: <925740be-b4ed-45b7-b6ea-fabc72a5f5dd_at_c34g2000yqn.googlegroups.com>
On Jan 8, 9:51 am, Jeremy <jeremy0..._at_gmail.com> wrote:
> In article <566ef7e9-c38c-43a2-8e12-29c006559181
> _at_j19g2000yqk.googlegroups.com>, Mark.Powe..._at_hp.com says...
>
> > 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
>
> Will this not run into the same issue when referring to
> :new.oldlongcolumn?
>
> --
> jeremy
I had to look up the conversion example and in the time it took to go find it then come back and post I had forgotten the mention of not being able to reference a long column in a trigger. We only have a few long columns in our system and I have never written a trigger that attempted to reference a long column so I had forgotten about that apparent restriction. Not having access to Oracle from home I cannot test to verify the restriction exits on 10.2
I will gather that you have so all I can suggest is adding the CLOB column then performing an update process to populate the CLOBs. Once populated you could remove the long column or at least null it out.
HTH -- Mark D Powell -- Received on Sat Jan 09 2010 - 14:33:34 CST