Take a COPY out of SQLcl’s book

As the world continues to get smaller, it makes sense for all databases to be defined to handle more than just US7ASCII, which is why the default characterset for all recent versions of the Oracle database is AL32UTF8. In that way, we can handle the various challenges when it comes to languages other than english.


SQL> create table t ( x varchar2(30 char));

Table created.

SQL> insert into t values ('안녕미미 99~12900원 시즌오프 원피');

1 row created.

But lets see what happens when we copy that data using some legacy facilities in SQL Plus.


SQL> COPY TO scott/tiger@db18_pdb1 CREATE t1 USING SELECT * FROM t;

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

   1 rows selected from DEFAULT HOST connection.
   1 rows inserted into T1.
   1 rows committed into T1 at scott@db18_pdb1.

SQL> desc scott.t1
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 X                                      VARCHAR2(120)

Notice that the definition of table T1 does not match that of the source. The COPY command in SQL Plus dates back to a time before CHAR and BYTE semantics existed, and we even stress that in the documentation.

“12.16 COPY.
The COPY command is not being enhanced to handle datatypes or features introduced with, or after Oracle8i. The COPY command is likely to be deprecated in a future release.”

Luckily, we of course have a newer version of SQL Plus, namely SQLcl. Running the same command under SQLcl is the easy solution here.


C:\>sqlcl

SQLcl: Release 19.2.1 Production on Mon Aug 05 10:46:43 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Mon Aug 05 2019 10:46:43 +08:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.6.0.0.0

SQL> COPY TO scott/tiger@db18_pdb1 CREATE t2 USING SELECT * FROM t;
Array fetch/bind size is 15 (less if there are longs or blobs). (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table T2 created.
    1 rows selected from DEFAULT HOST connection.
    1 rows inserted into T2.
    1 rows committed into T2 at scott@db18_pdb1.

SQL> desc scott.t2
Name Null? Type
---- ----- -----------------
X          VARCHAR2(30 CHAR)

3 Comments on “Take a COPY out of SQLcl’s book

  1. If you’re moving towards SQLcl anyway (good decision by the way), wouldn’t it be wiser to also move from the copy command to the bridge command?

    The fact that copy is “likely to be deprecated in a future release” of sql*plus might (!!) mean it will eventually disappear from SQLcl as well. Right?

  2. Dear Conner,
    I followed your ‘Create your first 19c database on Windows’ to the ‘Finish’ step, but got ‘copying database files failed’. Can you shed some light on it please?
    Many thanks in anticipation
    JF

    • Check under the base area where you choose to install, and look for a directory called “cfgtoollogs”. Thats where we dump any logfiles – somewhere in there should be some details about when we could not copy the database files

Leave a Reply to Erik van Roon Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: