SQL*Plus COPY Command is back as BRIDGE

By Franck Pachot

.
Did you ever use the COPY command in sqlplus? It’s very old, and documentation says :
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.

Deprecated? But it is back, with a new name, in the new SQL Developer based SQL*Plus (currently called sdsql in beta)

SQL*Plus COPY

Documentation is here. Let’s show how to copy

$ sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Mon Dec 8 22:49:40 2014

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

SQL> copy from system/manager@//192.168.78.216/DB1 to demo/demo@//192.168.78.216/DB1 CREATE DEMO_COPY using select * from dual;
connect demo/demo@//192.168.78.216/DB1

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 DEMO_COPY created.

   1 rows selected from system@//192.168.78.216/DB1.
   1 rows inserted into DEMO_COPY.
   1 rows committed into DEMO_COPY at demo@//192.168.78.216/DB1.

I’ve created a table DEMO_COPY in the DEMO schema from a select. And then I can check the table that has been created:

SQL> connect demo/demo@//192.168.78.216/DB1
Connected.

SQL> desc DEMO_COPY;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DUMMY                                              VARCHAR2(1)

SQL> select * from DEMO_COPY;

D
-
X

This is not very useful nowadays as we have Create Table as Select, Data Pump, etc. But by the way it can still be used to copy data from one database to another without having to create a database link nor move files on the database servers.

And the new BRIDGE command

Here is the new feature. The new SQL Developer 4.1 Early Adopter is there before Christmas:

Ho, ho, ho. Merry Christmas from the #SQLDev team http://t.co/4893ZUEpBv #41

— Jeff Smith (@thatjeffsmith) December 8, 2014

and it includes the new SQL*Plus which is a sqlplus like command line based on sqldev and is currently called ‘sdsql’:

F:sd41sdsqlbin>sdsql.bat demo/demo@//192.168.78.216/DB1

sdsql: Release 4.1.0 Beta on lun. déc. 08 23:29:58 2014

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

So I tested the BRIDGE command that looks like the old COPY one, but now with jdbc (and thus is database independant…)

SQL> BRIDGE DEMO_BRIDGE as "jdbc:oracle:thin://192.168.78.216/DB1;user=system;password=manager"(select * from dual);
null
null
Table DEMO_BRIDGE : insert succeeded

We can check the table

SQL> select * from DEMO_BRIDGE;
DUMMY
-----
X

desc is still there but we also have INFO:

SQL> info DEMO_BRIDGE
Columns
NAME    DATA TYPE     NULL  DEFAULT    COMMENTS
 DUMMY  VARCHAR2(1)   Yes

Indexes

And we have a new command to get the DDL:

SQL> ddl DEMO_BRIDGE

  CREATE TABLE "DEMO"."DEMO_BRIDGE"
   (    "DUMMY" VARCHAR2(1)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

That were my first steps on the new sqlplus based on sqldeveloper. It’s still in beta, but this a nice Christmas gift to play with.

Update

So it seems this is not a new feature.
The fact that it is in the HELP is the new features that made me aware of it…

@FranckPachot @thatjeffsmith that’s been in sqldev for a long time. Works across DB types too say MySQL to oracle.

— krisrice (@krisrice) December 8, 2014

Let’s see in previous version: