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 .



Get notified when a new post is published!

 

Loading

Comments

There are no comments yet. Why not start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *

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