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)
Documentation is here. Let’s show how to copy
$ sqlplus /nolog SQL*Plus: Release 18.104.22.168.0 Production on Mon Dec 8 22:49:40 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL> copy from system/[email protected]//192.168.78.216/DB1 to demo/[email protected]//192.168.78.216/DB1 CREATE DEMO_COPY using select * from dual; connect demo/[email protected]//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 [email protected]//192.168.78.216/DB1. 1 rows inserted into DEMO_COPY. 1 rows committed into DEMO_COPY at [email protected]//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/[email protected]//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:
— 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/[email protected]//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 22.214.171.124.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.
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…
— krisrice (@krisrice) December 8, 2014
Let’s see in previous version: