Re: Fail to copy a LONG datatype in Oracle to another table

From: Lothar Armbüster <lothar.armbruester_at_rheingau.netsurf.de>
Date: 12 Dec 99 23:19:00 +0100
Message-ID: <1737.15T29T13992648@rheingau.netsurf.de>

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_at_rheingau.netsurf.de
Hauptstr. 26            | lothar.armbruester_at_t-online.de
D-65346 Eltville        |

Received on Sun Dec 12 1999 - 16:19:00 CST