RE: Populating VARCHAR2 with LONG data...

Yes, Perl can do it also. And without 32k limits.

Jared

Jacques Kilchoer <Jacques.Kilchoer_at_quest.com> Sent by: root_at_fatcity.com
 03/31/2003 11:14 AM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        RE: Populating VARCHAR2 with LONG data...

(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.

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.)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Jared.Still_at_radisys.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).