(answer below)
> -----Original Message-----
> From: Gujral, Harpal S. [mailto:HGujral_at_oxhp.com]
>
> We have a requirement to add a VARCHAR2 column to an existing
> table which is
> a part of an application and also has a LONG column with data
> in it. Without
> using PL/SQL, how can we create the new VARCHAR2 column and
> "default" it to
> the LONG column data?
>
> The idea is all future inserts and updates made to the LONG
> column by the
> application, should get reflected in the VARCHAR2 column. We
> need to use
> this new VARCHAR2 column for reporting and use a WHERE clause
> on the column
> to filter data.
- If the length of the data in the LONG column is more than 2000 characters
(Oracle 7.3) or 4000 characters (Oracle 8.0 or higher) the data in the LONG
column will not fit into a varchar2 column. You will need to use a LONG or a
CLOB.
- If the length of the data in the LONG column is less than the above
numbers, then you can have any new values (insert/update to the LONG column)
be replicated to the varchar2 column by using a trigger (which is of course
written in PL/SQL).
- To move the existing values in the LONG column to the varchar2 column you
will need to write either PL/SQL (anonymous block or procedure) or else use
Pro*C or your programming language or choice. (I assume PERL would be able
to do this also.)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
INET: Jacques.Kilchoer_at_quest.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Mon Mar 31 2003 - 13:14:20 CST
