Xref: alice comp.databases.oracle.server:77526
Date: 12 Dec 99 23:19:00 +0100
From: "Lothar Armbüster" <lothar.armbruester@rheingau.netsurf.de>
Subject: Re: Fail to copy a LONG datatype in Oracle to another table
References: <3851AD8B.AE32946@cbmi.upmc.edu>
Message-ID: <1737.15T29T13992648@rheingau.netsurf.de>
Newsgroups: comp.databases.oracle.server
Lines: 51
X-Newsreader: THOR 2.5a (Amiga;TCP/IP)
Path: alice!news-feed.fnsi.net!news.maxwell.syr.edu!newsfeed.ision.net!ision!juno.wiesbaden.netsurf.de!deck18gei.vistec.com

Rich Tsui wrote at 11-Dez-99  02:48:59
on the subject Fail to copy a LONG datatype in Oracle to another table:

>Hi Oracle gurus,

>  I have trouble in copying a LONG datatype column of data to another 
>table via 
>"INSERT INTO tableA (A_col_NUM, A_col_LONG) 
>   SELECT B_col_NUM, B_col_LONG FROM tableB", where the A_col_LONG and
>B_col_LONG represent the LONG datatype.

>The error message I got from the sqlplus is: 
>ORA-00997: illegal use of LONG datatype

>I suspect it's a bug in Oracle 8.0.4.  If you know how to solve this
>problem, please let me know.  Thank you in advance.  

Hello Rich,
it's not a bug, it's a feature. ;-)
Well the fact is the LONG datatype is very limited concerning the use in
SQL statements.
When I write it's a feature I mean, it's documented somewhere.
One solution of your problem is to use PL/SQL:

begin
   for b_rec in (select b_col_num, b_col_long from tableb) loop
      insert into tablea(a_con_num,a_col_long)
         values(b_rec.b_col_num,b_rec.b_col_long);
   end loop;
end;
/

But beware! There is some limit in the size of LONG fields to be processed
like this. AFAIK it is 32K in Oracle 7 but I don't know he limit in
Oracle 8.

>Additionally, I even tried to use DBI in PERL to read a LONG datatype,
>and it also ended up with the following error message, 
>DBD::Oracle::st fetchrow failed: ORA-24345: A Truncation or null fetch
>error occurred (DBD ERROR: ORA-01406 error on field 1 of 1, ora_type 8)
>at cp_table.pl line 25.  (Line 25 has the command $sth->fetchrow())

Here I don't have any experience.

Hope that helps,
Lothar

-- 
Lothar Armbrüster       | lothar.armbruester@rheingau.netsurf.de
Hauptstr. 26            | lothar.armbruester@t-online.de
D-65346 Eltville        |