Xref: alice comp.databases.oracle.misc:22719
Path: alice!news-feed.fnsi.net!newsfeed.wli.net!newsfeed.berkeley.edu!agate!usenet
From: Jason Jay Weiland <archduke@uclink4.berkeley.edu>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Long/Varchar2 datatype question...Help!
Date: Tue, 17 Nov 1998 09:49:38 -0800
Organization: UC Berkeley, College of Chemistry
Lines: 49
Message-ID: <3651B732.A516E760@uclink4.berkeley.edu>
References: <36509ff7.423086273@news.newcastle.edu.au>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-Mailer: Mozilla 4.5b2 [en] (WinNT; I)
X-Accept-Language: en
To: Hope <cchln@alinga.newcastle.edu.au>

Hope,

     Thomas Kyte had a good solution some weeks back.
Since PL/SQL implicitly converts LONG data to VARCHAR.

If your table was three columns you could try this:

begin
    for x in ( select * from old_table ) loop
      insert into new_table values ( x.field1, x.field2,
      substr(x.field3,1,240));
    end loop;
end;


Hope that helps,

Jay!!!


Hope wrote:

> Hello-
>
> Excuse the newbie nature of this question, please.
>
> I can only export 'memo' type fields from MS Access to 'long' type
> fields in Oracle.
>
> I need to get the data from the 'long' fields into 'varchar2' fields
> so I can query it.
>
> Please help-  I cannot seem to make a query that will do this.
>
> What I've been doing?  I've tried creating two tables identical except
> that one has varchar2 fields where the other has long.  Then I've
> tried to do an 'insert' query, or an 'update' query in Oracle to get
> the varchar2 table populated with data from the 'long' table.  I can't
> get it to work.  Please, please, some kind person, show me an example
> of the SQL to do this?
>
> Thanks in advance, and sorry if this is a totally dumb question.
>
> Hope
>
> ¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø¤º°`°º¤ø
> http://www.geocities.com/PicketFence/7113/
> Writing about music is like dancing about architecture.