Copy legacy Oracle tables with SQL*Plus

by Bob Watkins | Apr 24, 2007 7:00:00 AM

Tags: Bob Watkins, Oracle Corp., LONG RAW, Oracle Tips Newsletter

Takeaway: Tables with a LONG datatype cannot be copied via the CREATE TABLE AS SELECT syntax. They can, however, be copied with the SQL*Plus COPY command. Bob Watkins shows you how in this Oracle tip.

You may be running Oracle 10g Release 2, but the applications you're supporting may have been written much earlier. Tables developed in versions of Oracle prior to 8i often used the LONG datatype to store large amounts of text.

With the introduction of Large Object (LOB) datatypes, the LONG and LONG RAW datatypes were deprecated. If you need to make a copy of a table that uses LONG, the CREATE TABLE AS SELECT syntax won't work; you'll get this error: ORA-00997: illegal use of LONG datatype.

You could export the table and import it, but that's a lot of work. The COPY command in SQL*Plus can still copy such a table with a single command. Here is the format of COPY (note the use of the continuation character, -, to break up the line):

COPY FROM user/pw@dblink TO user/pw@dblink CREATE tablename -

USING select-statement;

Be sure to use SET LONG first to cover the length of the data in the LONG column and avoid truncated data.

The downside is that the COPY command was frozen in functionality at version 8.0 of Oracle. It can only copy tables consisting of the following datatypes: CHAR, DATE, LONG, NUMBER, and VARCHAR2. New datatypes added in versions 8i and later are not supported. This is purely a legacy solution.

Listing A shows the CREATE TABLE error and the successful COPY command.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.

Print/View all Posts Comments on this article

Another reason COPY is good to know. swstephe | 04/25/07

Export file size sgecom | 04/29/07

Beware of COPY rbahadur@... | 05/03/07

Did Not Know COPY was Frozen psinger1 | 04/25/07

RE: Copy legacy Oracle tables with SQL*Plus yhogan@... | 10/09/07