ORA-00997: illegal use of LONG datatype

I don't really expect to see a large audience for this kind of error as the LONG datatype has been long deprecated by Oracle and it has been superseded by CLOBs (and BLOBs in case of LONG RAW), however a friend of mine contacted me this morning because while trying to execute a simple query like:

INSERT INTO long_table_b (col_k, long_col)
SELECT 2, long_col
FROM long_table_a
WHERE col_k = 1

he was getting the following error message:

ORA-00997: illegal use of LONG datatype

The reason is quite simple, open up the oracle document where the restrictions that apply to LONG datatypes are described and you will realize that you cannot just perform a simple query like the above when LONGs are involved (see bullet #8).

You can easily recreate the problem in this fashion:

create table test_long_a (col_k number, long_col long)
/
create table test_long_b (col_k number, long_col long)
/
insert into test_long_a values (1, lpad(' ', 4000))
/
insert into test_long_b
select 2, long_col
from test_long_a
where col_k = 1
/

ORA-00997: illegal use of LONG datatype

Now you might want to ask how can you can work around this limitation.
Well, the fact is that LONG datatypes have so many limitations that it is really a problem to work with them. According to Tom Kyte there are a few possibilities that you might want to explore, but basically you'd better to migrate the programs soon from LONG to CLOBs (or BLOBs) because they have been deprecated for almost 10 years now.
In the end CLOBs and BLOBs are much better to deal with, so do not *waste* your time with LONGs any more.

See message translations for ORA-00997 and search additional resources.

---

ORA-00997: Uso non consentito del tipo dati LONG
ORA-00997: uso no válido del tipo de dato LONG
ORA-00997: el tipus de dades LONG s'utilitza de forma il legal
ORA-00997: utilisation interdite du type de données LONG
ORA-00997: Unzulässige Verwendung des Datentyps LONG
ORA-00997: μη αποδεκτή χρήση του τύπου δεδομένων LONG
ORA-00997: ugyldig brug af LONG-datatype
ORA-00997: otillåten användning av datatypen LONG
ORA-00997: ugyldig bruk av datatypen LONG
ORA-00997: LONG-tietotyyppiä on käytetty väärin
ORA-00997: itt nem megengedett a LONG adattípus használata
ORA-00997: folosire ilegală a tipului de dată LONG
ORA-00997: Ongeldig gebruik van LONG-gegevenstype.
ORA-00997: uso inválido do tipo de dados LONG
ORA-00997: utilização ilegal do tipo de dados LONG
ORA-00997: неверное использование типа данных LONG
ORA-00997: nepřípustně použitý datový typ LONG
ORA-00997: neprípustné použitie dátového typu LONG
ORA-00997: niedozwolone użycie typu danych LONG
ORA-00997: geçersiz LONG veri türü kullanımı
ORA-00997: illegal use of LONG datatype