Skip to Main Content
  • Questions

  • SQL*Plus copy command errorring on CLOB

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, A.J..

Asked: February 19, 2016 - 2:19 pm UTC

Last updated: July 13, 2018 - 8:43 am UTC

Version: 12x

Viewed 10K+ times! This question is

You Asked

I am trying to move select records from one database to another using the copy command and I am getting this error below. Is there a way around this using the copy command?

Thank you,

A.J.

SQL> copy from saturn/******@test insert SFRAREG using select * from SFRAREG where SFRAREG_pidm=*****;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

CPY-0012: Datatype cannot be copied

select * from v$version
2 ;

BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0

SQL>

desc sfrareg
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
SFRAREG_PIDM NOT NULL NUMBER(8)
SFRAREG_TERM_CODE NOT NULL VARCHAR2(6 CHAR)
SFRAREG_CRN NOT NULL VARCHAR2(5 CHAR)
SFRAREG_EXTENSION_NUMBER NOT NULL NUMBER(3)
SFRAREG_RSTS_CODE NOT NULL VARCHAR2(2 CHAR)
SFRAREG_START_DATE NOT NULL DATE
SFRAREG_COMPLETION_DATE NOT NULL DATE
SFRAREG_FEE_WAIVER_IND NOT NULL VARCHAR2(1 CHAR)
SFRAREG_SAPR_OVERRIDE_IND NOT NULL VARCHAR2(1 CHAR)
SFRAREG_ACTIVITY_DATE NOT NULL DATE
SFRAREG_USER_ID NOT NULL VARCHAR2(30 CHAR)
SFRAREG_NUMBER_OF_UNITS NUMBER(7,2)
SFRAREG_DUNT_CODE VARCHAR2(4 CHAR)
SFRAREG_DETL_CODE VARCHAR2(4 CHAR)
SFRAREG_AMOUNT NUMBER(7,2)
SFRAREG_INSTRUCTOR_PIDM NUMBER(8)
SFRAREG_TRAN_NUMBER NUMBER(8)
SFRAREG_EFFECTIVE_DATE DATE
SFRAREG_COMMENTS CLOB
SFRAREG_DATA_ORIGIN VARCHAR2(30 CHAR)
SFRAREG_RSTS_DATE NOT NULL DATE
SFRAREG_SURROGATE_ID NUMBER(19)
SFRAREG_VERSION NUMBER(19)
SFRAREG_VPDI_CODE VARCHAR2(6 CHAR)

SQL>

and Chris said...

Copy only supports the following data types:

CHAR
DATE
LONG
NUMBER
VARCHAR2

http://docs.oracle.com/database/121/SQPUG/apb.htm#SQPUG611

So you can't use it to copy clobs. If the clobs hold strings shorter than 4,000 characters you could cast them to varchar2s:

SQL> create table t (c clob);

Table created.

SQL> insert into t values ('x');

1 row created.

SQL> commit;

Commit complete.

SQL> copy from chris@db  create tcopy using select cast(c as varchar2(10)) c from t;

Array fetch/bind size is 100. (arraysize is 100)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table TCOPY created.

   1 rows selected from chris@db
   1 rows inserted into TCOPY.
   1 rows committed into TCOPY at DEFAULT HOST connection.

SQL> desc tcopy
 Name Null?    Type
 ---- -------- ------------------------------------

 C             VARCHAR2(10)


If they hold strings longer than this then you could trim the extended chars and cast it to a varchar2. This will lose data!

Assuming you don't want this then you'll need to find another approach.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Using Cast on a table inside a copy command.

A.J. Stepanick, February 19, 2016 - 6:29 pm UTC

Chris,

How you apply this to the whole table I supplied? I'm not sure how I would do it.

Thank you,

A.J.

Connor McDonald
February 20, 2016 - 4:18 am UTC

You just need a longer query, ie,

copy from saturn/******@test insert SFRAREG using select * from SFRAREG

becomes

copy from saturn/******@test insert SFRAREG using select col1, col2, col3, cast(col4 as varchar2(4000) col4, col5, col6 from SFRAREG where SFRAREG_pidm=*****;

etc etc

Still getting the error

A.J., February 22, 2016 - 4:31 pm UTC

Unfortunately I'm still getting the error. Please see below.

Thank you.

SQL> copy from saturn/*****@test insert SFRAREG using select SFRAREG_PIDM,SFRAREG_TERM_CODE,SFRAREG_CRN,...,cast(sfrareg_comments as varchar2(4000)),SFRAREG_DATA_ORIGIN,SFRAREG_RSTS_DATE,SFRAREG_SURROGATE_ID,SFRAREG_VERSION,SFRAREG_VPDI_CODE from SFRAREG where SFRAREG_pidm=*****;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

CPY-0012: Datatype cannot be copied
Chris Saxon
February 22, 2016 - 5:54 pm UTC

What happens if you substr the clob before casting it?

copy command

A.J., February 22, 2016 - 9:04 pm UTC

Sorry, but that did not work.

cast(SUBSTR(sfrareg_comments,1,4000) as varchar2(4000))


Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

CPY-0012: Datatype cannot be copied
Connor McDonald
February 23, 2016 - 1:19 am UTC

How about not using COPY at all ? Use a global temporary table

Database 1
==========

SQL> create table T (  pk int, c clob );

Table created.

SQL> insert into T values (1,'small test');

1 row created.

SQL> commit;

Commit complete.

SQL> declare
  2    c clob;
  3  begin
  4    dbms_lob.createtemporary(c,true);
  5    for i in 1 .. 100 loop
  6      dbms_lob.writeappend(c,32000,rpad('large test',32000,'x'));
  7    end loop;
  8    insert into t values (2,c);
  9    commit;
 10    dbms_lob.freetemporary(c);
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL>
SQL> select pk, dbms_lob.getlength(c) from t;

        PK DBMS_LOB.GETLENGTH(C)
---------- ---------------------
         1                    10
         2               3200000

Database 2
==========
SQL> create global temporary table gtt as select pk, c from t@db11;

Table created.

SQL> insert into gtt select pk, c from t@db11;

2 rows created.

SQL> select pk, dbms_lob.getlength(c) from gtt;

        PK DBMS_LOB.GETLENGTH(C)
---------- ---------------------
         1                    10
         2               3200000


Cheers,
Connor

Destination table can only have "allowed" data types

Babak Tourani, July 13, 2018 - 8:30 am UTC

Hi,

I believe the issue is caused by the fact that the destination table still contains a "disallowed" data type.

You have dealt with the SELECT problem using CAST, but because we're dealing with an INSERT operation (as opposed to "CREATE" which Chris suggested), the destination table (i.e. SFRAREG) should have its CLOB column modified to VARCHAR2.

That forces you to introduce a new step in the whole operation: First you have to create a new table similar to the destination which only includes "allowed" data types (even existence of virtual column based on "disallowed" data types will break the operation), copy the data into the intermediary table and then copy from the main table into the destination.

Please let me know if there's a way to avoid the middle step.

Regards,
Chris Saxon
July 13, 2018 - 8:43 am UTC

You mean using copy? I'm not aware of a way around the issue with this command. You could look into using a gtt as Connor suggested. Or some other way of transferring the data.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here