(answer below)
> -----Original Message-----
> From: Gujral, Harpal S. [mailto:[EMAIL PROTECTED]]
>
> 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.
a) 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.
b) 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).
c) 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.)
