SDSQL: O Substituto do SQL*Plus

Enfim parece que a Oracle planeja um substituto para o SQL*Plus, o SDSQL.

O SDSQL é a versão linha de comando do SQL Developer. Entre as vantagens dele, estão:
– Histórico;
– Edição muito mais simples;
– Formatação de colunas (veja abaixo);
– Recurso que completa automaticamente sintaxe e nomes de objetos (com TAB, como no shell do Linux);
– Não precisa de instalação. É só descompactar e conectar, como demonstro abaixo.

[ricardo@Melquior Database]$ ls -lh sqlcl-4.1.0.15.067.0446-no-jre.zip
-rw-rw-r--. 1 ricardo ricardo 12M Mar 10 14:37 sqlcl-4.1.0.15.067.0446-no-jre.zip
[ricardo@Melquior Database]$ unzip -q sqlcl-4.1.0.15.067.0446-no-jre.zip
[ricardo@Melquior Database]$ sh sqlcl/bin/sql SYSTEM@//localhost:1521/ORCL

SQLcl: Release 4.1.0 Beta on Ter Mar 10 16:31:36 2015

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


Password? (**********?) ********
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL> SET PAGES 1000
SQL> SELECT HOST_NAME FROM V$INSTANCE;

HOST_NAME
----------------------------------------------------------------
Melquior.localdomain

SQL> SELECT BANNER FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL>

Veja abaixo a formatação de um SELECT na SCOTT.EMP.

[ricardo@Melquior Database]$ sh sqlcl/bin/sql SCOTT@//localhost:1521/ORCL

SQLcl: Release 4.1.0 Beta on Ter Mar 10 16:38:12 2015

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


Password? (**********?) *****
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production


SQL> SELECT * FROM EMP;

     EMPNO ENAME      JOB              MGR HIREDATE                           SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17/12/80                           800                    20
      7499 ALLEN      SALESMAN        7698 20/02/81                          1600        300         30
      7521 WARD       SALESMAN        7698 22/02/81                          1250        500         30
      7566 JONES      MANAGER         7839 02/04/81                          2975                    20
      7654 MARTIN     SALESMAN        7698 28/09/81                          1250       1400         30
      7698 BLAKE      MANAGER         7839 01/05/81                          2850                    30
      7782 CLARK      MANAGER         7839 09/06/81                          2450                    10
      7788 SCOTT      ANALYST         7566 19/04/87                          3000                    20
      7839 KING       PRESIDENT            17/11/81                          5000                    10
      7844 TURNER     SALESMAN        7698 08/09/81                          1500          0         30
      7876 ADAMS      CLERK           7788 23/05/87                          1100                    20
      7900 JAMES      CLERK           7698 03/12/81                           950                    30
      7902 FORD       ANALYST         7566 03/12/81                          3000                    20
      7934 MILLER     CLERK           7782 23/01/82                          1300                    10

 14 linhas selecionadas

SQL>

Outra função muito útil, e muito mais avançada que um simples DEST é a INFO.

SQL> INFO HR.EMPLOYEES
TABLE: EMPLOYEES
	 LAST ANALYZED:2014-07-07 06:56:25.0
	 ROWS         :107
	 SAMPLE SIZE  :107
	 INMEMORY     :DISABLED
	 COMMENTS     :employees table. Contains 107 rows. References with departments,
                       jobs, job_history tables. Contains a self reference.

Columns
NAME             DATA TYPE           NULL  DEFAULT    COMMENTS
*EMPLOYEE_ID     NUMBER(6,0)         No                   Primary key of employees table.
 FIRST_NAME      VARCHAR2(20 BYTE)   Yes                  First name of the employee. A not null column.
 LAST_NAME       VARCHAR2(25 BYTE)   No                   Last name of the employee. A not null column.
 EMAIL           VARCHAR2(25 BYTE)   No                   Email id of the employee
 PHONE_NUMBER    VARCHAR2(20 BYTE)   Yes                  Phone number of the employee; includes country code and area code
 HIRE_DATE       DATE                No                   Date when the employee started on this job. A not null column.
 JOB_ID          VARCHAR2(10 BYTE)   No                   Current job of the employee; foreign key to job_id column of the
                                                                    jobs table. A not null column.
 SALARY          NUMBER(8,2)         Yes                  Monthly salary of the employee. Must be greater
                                                                    than zero (enforced by constraint emp_salary_min)
 COMMISSION_PCT  NUMBER(2,2)         Yes                  Commission percentage of the employee; Only employees in sales
                                                                    department elgible for commission percentage
 MANAGER_ID      NUMBER(6,0)         Yes                  Manager id of the employee; has same domain as manager_id in
                                                                    departments table. Foreign key to employee_id column of employees table.
                                                                    (useful for reflexive joins and CONNECT BY query)
 DEPARTMENT_ID   NUMBER(4,0)         Yes                  Department id where employee works; foreign key to department_id
                                                                    column of the departments table

Indexes
INDEX_NAME            UNIQUENESS  STATUS  FUNCIDX_STATUS  COLUMNS                COLUMN_EXPRESSION
HR.EMP_JOB_IX         NONUNIQUE   VALID                   JOB_ID
HR.EMP_NAME_IX        NONUNIQUE   VALID                   LAST_NAME, FIRST_NAME
HR.EMP_EMAIL_UK       UNIQUE      VALID                   EMAIL
HR.EMP_EMP_ID_PK      UNIQUE      VALID                   EMPLOYEE_ID
HR.EMP_MANAGER_IX     NONUNIQUE   VALID                   MANAGER_ID
HR.EMP_DEPARTMENT_IX  NONUNIQUE   VALID                   DEPARTMENT_ID


References
TABLE_NAME   CONSTRAINT_NAME               DELETE_RULE  STATUS   DEFERRABLE      VALIDATED  GENERATED
CUSTOMERS    CUSTOMERS_ACCOUNT_MANAGER_FK  SET NULL     ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME
DEPARTMENTS  DEPT_MGR_FK                   NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME
EMPLOYEES    EMP_MANAGER_FK                NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME
JOB_HISTORY  JHIST_EMP_FK                  NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME
ORDERS       ORDERS_SALES_REP_FK           SET NULL     ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME

SQL>

Abaixo, um exemplo do comando history.

SQL> history
  1  SET PAGES 1000
  2  SELECT HOST_NAME FROM V$INSTANCE
  3  SELECT BANNER FROM V$VERSION
  4  ALTER USER SCOTT ACCOUNT UNLOCK
  5  CONN SCOTT/TIGER
  6  SET AUTOTRACE TRACEONLY
  7  SET AUTOTRACE ON
  8  ALTER USER SCOTT ACCOUNT UNLOCK IDENTIFIED BY TIGER
  9  SELECT * FROM EMP
SQL>

Outro recurso que achei muito legal foi o ALIAS, também como em um shell.

SQL> alias STATUS=SELECT STATUS FROM V$INSTANCE;
SQL> STATUS
Command=STATUS

STATUS
------------
OPEN

SQL>

O SDSQL pode ser baixado na mesma página do SQL Developer 4.1 EA2, mais abaixo do SQL Developer. Repare que esta versão teve mais de 350 Bugs corrigidos…

SDSQL

Download SQLCL: http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/sqldev-41ea-2372780.html

One comment

  1. Viche Maria vão substituir o outro lado da força?Estava tão habituado com o SQLPLUS rs
    Evolução natural das coisas é assim mesmo.

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.