Prev: Extracting 2.5 million rows
Next: Minus Operator with Missing Column
From: Jeremy on 7 Jan 2010 10:10
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

From: Mark D Powell on 8 Jan 2010 09:29
On Jan 7, 10:10 am, Jeremy <jeremy0...(a)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 --
From: Mark D Powell on 9 Jan 2010 15:33
On Jan 8, 9:51 am, Jeremy <jeremy0...(a)gmail.com> wrote:
> In article <566ef7e9-c38c-43a2-8e12-29c006559181
> @j19g2000yqk.googlegroups.com>, Mark.Powe...(a)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 --



 | 
Pages: 1
Prev: Extracting 2.5 million rows
Next: Minus Operator with Missing Column