Search

Top 60 Oracle Blogs

Recent comments

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)