With the new version of SQL Developer 4.1, Oracle makes available early adopter of the SQLcl, a new tool that might replace SQL*Plus tool.
I’ve make some tests with version last updated on September 14th, 2015 (version 4.2.0.15.257.0801).
This is the list of features that I like the most.
1. autocomplete
Better than rlwrap utility although in the current states still buggy.
SQL history by using up and down arrows is implemented as well as TAB autocomplete.
Thus there will be no need to use rlwrap utility which has a few restrictions (only interactive sessions supported and changed behavior of the CTRL+C which could have side effects on RMAN and SQL*Plus scripts behaviour).
2. alias
Spare some keystrokes as you can make aliases for the most common commands.
SCOTT@orcl> alias
locks
sessions
tables
tables2
SCOTT@orcl> alias list tables
tables
------
select table_name "TABLES"from user_tables
3. CTAS
Create DDL for “Create Table as Select” command.
SCOTT@orcl> ddl emp
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(10,0),
"ENAME" VARCHAR2(30),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
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" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
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" ;
CREATE INDEX "SCOTT"."EMP_JOB_IX" ON "SCOTT"."EMP" ("JOB")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
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" ;
CREATE INDEX "SCOTT"."FK_DEPT" ON "SCOTT"."EMP" ("DEPTNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
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" ;
4. info
This command is like describe but with more details about the objects requested.
Also can be run from SQL Developer GUI as script.
SCOTT@orcl> info emp
Columns
NAME DATA TYPE NULL DEFAULT COMMENTS
*EMPNO NUMBER(10,0) No
ENAME VARCHAR2(30 BYTE) Yes
JOB VARCHAR2(9 BYTE) Yes
MGR NUMBER(4,0) Yes
HIREDATE DATE Yes
SAL NUMBER(7,2) Yes
COMM NUMBER(7,2) Yes
DEPTNO NUMBER(2,0) Yes
Indexes
INDEX_NAME UNIQUENESS STATUS FUNCIDX_STATUS COLUMNS COLUMN_EXPRESSION
SCOTT.PK_EMP UNIQUE VALID EMPNO
SCOTT.FK_DEPT NONUNIQUE VALID DEPTNO
SCOTT.EMP_JOB_IX NONUNIQUE VALID JOB
5. info+
Provides additional details like the column statistics.
Can be run from the SQL Developer GUI.
SCOTT@orcl> info+ emp
Columns
NAME DATA TYPE NULL DEFAULT LOW_VALUE HIGH_VALUE NUM_DISTINCT HISTOGRAM
*EMPNO NUMBER(10,0) No 7369 1008793 646732 NONE
ENAME VARCHAR2(30 BYTE) Yes /1000323d_DelegateInvocationHa yCbCrSubSamplingType229_T 32738 NONE
JOB VARCHAR2(9 BYTE) Yes ANALYST SALESMAN 5 FREQUENCY
MGR NUMBER(4,0) Yes 7566 7902 6 NONE
HIREDATE DATE Yes 1836.04.30.00.00.00 1997.04.18.00.00.00 59012 NONE
SAL NUMBER(7,2) Yes 800 9999 9002 NONE
COMM NUMBER(7,2) Yes 0 1400 4 NONE
DEPTNO NUMBER(2,0) Yes 10 30 3 FREQUENCY
Indexes
INDEX_NAME UNIQUENESS STATUS FUNCIDX_STATUS COLUMNS COLUMN_EXPRESSION
SCOTT.PK_EMP UNIQUE VALID EMPNO
SCOTT.FK_DEPT NONUNIQUE VALID DEPTNO
SCOTT.EMP_JOB_IX NONUNIQUE VALID JOB
6. history
This command will show SQL history.
6. history
This command will show SQL history
SCOTT@orcl> history
29 cd
30 ddl items
31 ctas item
32 ctas item item_ctas
33 ctas emp emp_ctas
34 SELECT * from USER_TABLES
35 ddl emp
36 set sqlformat CSV
37 select * from emp where rownum < 10
To show just one SQL from the history, you can use the following construction:
SCOTT@orcl> hist 37
1* select * from emp where rownum < 10
SCOTT@orcl> select * from emp where rownum < 10;
There are many more options to search the history. You can consult SQLcl HELP.
SQL>history help
history [ | FULL | USAGE | SCRIPT | HELP | TIME | CLEAR (SESSION)?]
7. Formating
Helps to format output from the SQL.
--CSV formatting
SCOTT@orcl> set sqlformat CSV
SCOTT@orcl> select * from emp where rownum < 10;
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,17-DEC-80,800,,20
7499,"ALLEN","SALESMAN",7698,20-FEB-81,1600,300,30
7521,"WARD","SALESMAN",7698,22-FEB-81,1250,500,30
7566,"JONES","MANAGER",7839,02-APR-81,2975,,20
7654,"MARTIN","SALESMAN",7698,28-SEP-81,1250,1400,30
7698,"BLAKE","MANAGER",7839,01-MAY-81,2850,,30
7782,"CLARK","MANAGER",7839,09-JUN-81,2450,,10
7788,"SCOTT","ANALYST",7566,19-APR-87,3000,,20
7839,"KING","PRESIDENT",,17-NOV-81,5000,,10
9 rows selected
--Or JSON formatting
SCOTT@orcl> set sqlformat json
SCOTT@orcl> select * from emp where rownum < 3;
{"items":[
{"empno":7369,"ename":"SMITH","job":"CLERK","mgr":7902,"hiredate":"17-DEC-80","sal":800,"deptno":20},{"empno":7499,"ename":"ALLEN","job":"SALESMAN","mgr":7698,"hiredate":"20-FEB-81","sal":1600,"comm":300,"deptno":30}]}
8. tnsping
Provides the tnsping functionality directly from the sqlcli tool.
SCOTT@orcl> tnsping localhost:2263:orcl
ping:33ms
9. oerr
–Help you find description of ORA- errors
SCOTT@orcl> oerr ora 600
00600. 00000 - "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
*Cause: This is the generic internal error number for Oracle program
exceptions. It indicates that a process has encountered a low-level,
unexpected condition. The first argument is the internal message
number. This argument and the database version number are critical in
identifying the root cause and the potential impact to your system.
10. repeat
Repeats execution of query from the buffer. Very useful for performance benchmark.
SCOTT@orcl> repeat 3 5;
Running 1 of 3 @ 9:47:7 with a delay of 5.0s
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
Running 2 of 3 @ 9:47:12 with a delay of 5.0s
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
Running 3 of 3 @ 9:47:17 with a delay of 5.0s
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
11. SSHTUNNEL
I didn’t test it yet, but I’ve found there are issues with it.
If that is true, hopefully it will be fixed when the product will be released.
Conclusion:
I believe SQLCl has bright future once the product become more mature.
During the tests, I had the same feelings as when I’ve started to use Project Raptor, today known as SQL Developer .
Comments