"Oracle SQLcl"を使ってみる


#はじめに
##目的

JavaベースのOracleコマンドラインツールOracle SQLclを使ってみます。
JeffSmithさんの以下の動画やBLOGの内容を試したものになります。


 * SQLcl overview – A new Command Line Interface for Oracle Database from Jeff Smith
https://www.youtube.com/watch?v=HApdy-o525A

 * Oracle SQLcl Slidedeck: Overview of our new command line interface
http://www.thatjeffsmith.com/archive/2015/02/oracle-sqlcl-slidedeck-overview-of-our-new-command-line-interface/

##環境


 * Oracle SQL Developer 4.1 Early Adopter (4.1.0.17.29)


####接続する環境
 * Oracle Linux Server release 6.5
 * Oracle Database 12.1.0.2




#試してみる

##Download



Oracle SQL Developer 4.1 Early Adopter (4.1.0.17.29)
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/sqldev-41ea-2372780.html

Accept License Agreementボタンをチェックし、下のほうにある「Command Line - SDSQL」をダウンロードします。



##インストール

任意の場所にダウンロードしてきたzipファイルを解凍します。

~~~linux
$ pwd
/home/oracle
$ unzip sqlcl-4.1.0.15.029.1246-no-jre.zip 
Archive:  sqlcl-4.1.0.15.029.1246-no-jre.zip
  inflating: sqlcl/bin/sql           
  inflating: sqlcl/bin/sql.bat       
  inflating: sqlcl/lib/SQLinForm.jar  
  inflating: sqlcl/lib/jline-2.12.jar  
  inflating: sqlcl/lib/ojdbc6.jar    
  inflating: sqlcl/lib/oracle.dbtools-common.jar  
  inflating: sqlcl/lib/oracle.sqldeveloper.sqlcl.jar  
  inflating: sqlcl/lib/orai18n-mapping.jar  
  inflating: sqlcl/lib/orai18n-utility.jar  
  inflating: sqlcl/lib/orai18n.jar   
  inflating: sqlcl/lib/xdb6.jar      
  inflating: sqlcl/lib/xmlparserv2.jar  
~~~~

bin/sqlに実行権限を付与します。

~~~linux
$ cd sqlcl/bin
$ chmod u+x sql
~~~







##起動＆接続

Java7 JREが動作することを確認します。

~~~linux
$ java -version
java version "1.7.0_71"
OpenJDK Runtime Environment (rhel-2.5.3.2.0.1.el6_6-x86_64 u71-b14)
OpenJDK 64-Bit Server VM (build 24.65-b04, mixed mode)
~~~

EZCONNECで接続します。

~~~linux
$ pwd
/home/oracle/sqlcl/bin
$ ./sql soe/soe@//localhost:1521/pdb_1

SQLcl: Release 4.1.0 Beta on Sun Mar 01 12:48:12 2015

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


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

SQL> 
~~~


SQLPlusで使用できるコマンドを試してみます。




##新しいコマンドを使う

以下のコマンドはSQLPlusにない、新しいコマンドのようです。
helpコマンド実行で一覧を表示した際に色違いで表示されました。

|SQLcl新しいコマンド |
|:-----------:|
| ALIAS      |
| APEX       |
| BRIDGE     |
| CD         |
| CTAS       |  
| DDL        |    
| FORMAT     |
| HISTORY    |
| INFORMATION|
   

以下でいくつかのコマンドを試してみます。

### ALIAS

SQLコマンドに別名をつけることができます。
オブジェクト一覧を表示するエイリアスをつけてみます。

~~~sql

SQL> help alias
ALIAS
------

Alias is a command which allows you to save a sql, plsql or sqlplus script and assign it a shortcut command.
"alias" - print a list of aliases

SQL> alias
locks
tables
SQL> alias objects=select object_name,object_type from user_objects
  2  /
SQL> alias
locks
objects
tables
SQL> objects
Command=objects

OBJECT_NAME                    OBJECT_TYPE                  
------------------------------ ------------------------------
CUSTOMERS                     TABLE                        
ADDRESSES                     TABLE                        
CARD_DETAILS                  TABLE                        
WAREHOUSES                    TABLE  


~~~


### CTAS

Create Table As Select（CTAS）のDDLを作成してくれます。
（テーブル作成の実行まではしません）
CUSTOMERS表をNEW_CUSTOMERS表でCTASのDDLを作ってみます。

~~~sql

SQL> help ctas
CTAS
 ctas table new_table 
Uses DBMS_METADATA to extract the DDL for the existing table
 Then modifies that into a create table as select * from 

SQL> ctas customers new_customers

  CREATE TABLE "SOE"."NEW_CUSTOMERS" 
   (	"CUSTOMER_ID",
	"CUST_FIRST_NAME",
	"CUST_LAST_NAME",
	"NLS_LANGUAGE",
	"NLS_TERRITORY",
	"CREDIT_LIMIT",
	"CUST_EMAIL",
 <中略>　
  STORAGE(INITIAL 8388608 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE" 

as 
select * from CUSTOMERS




~~~
	
   
###DDL


テーブルのDDL（関連する索引なども）を作成してくれる。
CUSTOMERS表のDDLを取得します。

~~~sql
SQL> help ddl
DDL [<object_name>] [<filename>]
SQL> ddl customers

  CREATE TABLE "SOE"."CUSTOMERS" 
   (	"CUSTOMER_ID" NUMBER(12,0) CONSTRAINT "CUST_CUSTID_NN" NOT NULL ENABLE, 
	"CUST_FIRST_NAME" VARCHAR2(40) CONSTRAINT "CUST_FNAME_NN" NOT NULL ENABLE, 
　 <中略>
  STORAGE(INITIAL 8388608 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE" 

  CREATE INDEX "SOE"."CUST_ACCOUNT_MANAGER_IX" ON "SOE"."CUSTOMERS" ("ACCOUNT_MGR_ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE" 
  PARALLEL 4 

 <中略>

SQL> 

~~~
   








###HISTORY

過去のコマンドの履歴を表示します。




~~~sql

SQL> help history
HISTORY
---------
history [<index> | FULL | USAGE | HELP ]

SQL>history help
history [<index> | FULL | USAGE | HELP ]

SQL>history full
1  select 1 from dual;
2  select 2
>  from dual;
3  select 3 from dual
>  where 1=1;

SQL>history usage
1  (2) select 1 from dual; 
2  (11) select 2 from dual; 
3  (2) select 3 from dual where 1=1; 

SQL>history 3
1  select 3 from dual
2* where 1=1;



~~~
   


###INFORMATION

テーブルの行数、定義、索引、参照整合性などの情報を表示します。
CUSTOMERS表の情報を表示します。

~~~
SQL> help information
INFORMATION
--------

This command is like describe but with more details about the objects requersted.

INFO[RMATION] {[schema.]object[@connect_identifier]}

SQL> info customers
TABLE: CUSTOMERS 
	 LAST ANALYZED:2014-12-23 17:00:45.0 
	 ROWS         :1000000 
	 SAMPLE SIZE  :1000000 
	 INMEMORY     :DISABLED 
	 COMMENTS     : 

Columns 
NAME                DATA TYPE            NULL  DEFAULT    COMMENTS
*CUSTOMER_ID        NUMBER(12,0)         No               
 CUST_FIRST_NAME    VARCHAR2(40 BYTE)    No               
 CUST_LAST_NAME     VARCHAR2(40 BYTE)    No               
 NLS_LANGUAGE       VARCHAR2(3 BYTE)     Yes              
 NLS_TERRITORY      VARCHAR2(30 BYTE)    Yes              
 CREDIT_LIMIT       NUMBER(9,2)          Yes              
 CUST_EMAIL         VARCHAR2(100 BYTE)   Yes              
 ACCOUNT_MGR_ID     NUMBER(12,0)         Yes              
 CUSTOMER_SINCE     DATE                 Yes              
 CUSTOMER_CLASS     VARCHAR2(40 BYTE)    Yes              
 SUGGESTIONS        VARCHAR2(40 BYTE)    Yes              
 DOB                DATE                 Yes              
 MAILSHOT           VARCHAR2(1 BYTE)     Yes              
 PARTNER_MAILSHOT   VARCHAR2(1 BYTE)     Yes              
 PREFERRED_ADDRESS  NUMBER(12,0)         Yes              
 PREFERRED_CARD     NUMBER(12,0)         Yes              

Indexes
INDEX_NAME                   UNIQUENESS  STATUS  FUNCIDX_STATUS  COLUMNS                     COLUMN_EXPRESSION         
SOE.CUST_DOB_IX              NONUNIQUE   VALID                   DOB                                                   
SOE.CUSTOMERS_PK             UNIQUE      VALID                   CUSTOMER_ID                                           
SOE.CUST_EMAIL_IX            NONUNIQUE   VALID                   CUST_EMAIL                                            
SOE.CUST_ACCOUNT_MANAGER_IX  NONUNIQUE   VALID                   ACCOUNT_MGR_ID                                        
SOE.CUST_FUNC_LOWER_NAME_IX  NONUNIQUE   VALID   ENABLED         SYS_NC00017$, SYS_NC00018$  LOWER("CUST_LAST_NAME")   
SOE.CUST_FUNC_LOWER_NAME_IX  NONUNIQUE   VALID   ENABLED         SYS_NC00017$, SYS_NC00018$  LOWER("CUST_FIRST_NAME")  


References
TABLE_NAME  CONSTRAINT_NAME        DELETE_RULE  STATUS   DEFERRABLE      VALIDATED      GENERATED  
ADDRESSES   ADD_CUST_FK            NO ACTION    ENABLED  DEFERRABLE      NOT VALIDATED  USER NAME  
ORDERS      ORDERS_CUSTOMER_ID_FK  SET NULL     ENABLED  NOT DEFERRABLE  NOT VALIDATED  USER NAME  

SQL> 

~~~
   


###SET SQLFORMAT 

#####SET SQLFORMAT ANSICONSOLE

いいかんじに列長とかを調整してくれる。

```sql
SQL> set sqlformat ansiconsole

SQL> l
  1   select * from order_items
  2* where rownum < 11; 
SQL> r

ORDER_ID  LINE_ITEM_ID  PRODUCT_ID  UNIT_PRICE  QUANTITY  DISPATCH_DATE  RETURN_DATE  GIFT_WRAP     CONDITION  SUPPLIER_ID  ESTIMATED_DELIVERY  
718,846   1             378         779         4         12-APR-12                   Plain         New        581          23-MAR-00           
718,846   2             351         789         6         12-MAR-12                   None          Return     391          26-MAR-07           
718,846   3             349         952         4         23-FEB-12                   None          New        474          29-OCT-11           
718,847   1             489         718         4         29-JAN-12                   None          New        456          02-MAR-00           
718,847   2             458         852         4         27-FEB-12                   None          New        351          08-NOV-04           
718,848   1             687         1,192       4         10-JAN-12                   Seasonal      New        317          27-SEP-08           
718,848   2             403         830         4         05-APR-12                   Boy_Birthday  New        494          11-APR-00           
718,848   3             508         760         3         12-MAR-12                   None          New        468          05-MAY-07           
718,849   1             339         1,373       6         22-FEB-12                   None          New        339          15-APR-07           
718,849   2             576         862         3         13-FEB-12                   None          New        372          30-JUL-11           


```

#####SET SQLFORMAT CSV

結果をCSV形式で出力



```sql
SQL> select * from order_items where rownum < 11;

"ORDER_ID","LINE_ITEM_ID","PRODUCT_ID","UNIT_PRICE","QUANTITY","DISPATCH_DATE","RETURN_DATE","GIFT_WRAP","CONDITION","SUPPLIER_ID","ESTIMATED_DELIVERY"
718846,1,378,779,4,12-APR-12,,"Plain","New",581,23-MAR-00
718846,2,351,789,6,12-MAR-12,,"None","Return",391,26-MAR-07
718846,3,349,952,4,23-FEB-12,,"None","New",474,29-OCT-11
718847,1,489,718,4,29-JAN-12,,"None","New",456,02-MAR-00
718847,2,458,852,4,27-FEB-12,,"None","New",351,08-NOV-04
718848,1,687,1192,4,10-JAN-12,,"Seasonal","New",317,27-SEP-08
718848,2,403,830,4,05-APR-12,,"Boy_Birthday","New",494,11-APR-00
718848,3,508,760,3,12-MAR-12,,"None","New",468,05-MAY-07
718849,1,339,1373,6,22-FEB-12,,"None","New",339,15-APR-07
718849,2,576,862,3,13-FEB-12,,"None","New",372,30-JUL-11
```

#####SET SQLFORMAT JSON


結果をJSON形式で出力


```
SQL> set sqlformat json
SQL> r

{"items":[
{"order_id":718846,"line_item_id":1,"product_id":378,"unit_price":779,"quantity":4,"dispatch_date":"12-APR-12","gift_wrap":"Plain","condition":"New","supplier_id":581,"estimated_delivery":"23-MAR-00"},{"order_id":718846,"line_item_id":2,"product_id":351,"unit_price":789,"quantity":6,"dispatch_date":"12-MAR-12","gift_wrap":"None","condition":"Return","supplier_id":391,"estimated_delivery":"26-MAR-07"},{"order_id":718846,"line_item_id":3,"product_id":349,"unit_price":952,"quantity":4,"dispatch_date":"23-FEB-12","gift_wrap":"None","condition":"New","supplier_id":474,"estimated_delivery":"29-OCT-11"},{"order_id":718847,"line_item_id":1,"product_id":489,"unit_price":718,"quantity":4,"dispatch_date":"29-JAN-12","gift_wrap":"None","condition":"New","supplier_id":456,"estimated_delivery":"02-MAR-00"},{"order_id":718847,"line_item_id":2,"product_id":458,"unit_price":852,"quantity":4,"dispatch_date":"27-FEB-12","gift_wrap":"None","condition":"New","supplier_id":351,"estimated_delivery":"08-NOV-04"},{"order_id":718848,"line_item_id":1,"product_id":687,"unit_price":1192,"quantity":4,"dispatch_date":"10-JAN-12","gift_wrap":"Seasonal","condition":"New","supplier_id":317,"estimated_delivery":"27-SEP-08"},{"order_id":718848,"line_item_id":2,"product_id":403,"unit_price":830,"quantity":4,"dispatch_date":"05-APR-12","gift_wrap":"Boy_Birthday","condition":"New","supplier_id":494,"estimated_delivery":"11-APR-00"},{"order_id":718848,"line_item_id":3,"product_id":508,"unit_price":760,"quantity":3,"dispatch_date":"12-MAR-12","gift_wrap":"None","condition":"New","supplier_id":468,"estimated_delivery":"05-MAY-07"},{"order_id":718849,"line_item_id":1,"product_id":339,"unit_price":1373,"quantity":6,"dispatch_date":"22-FEB-12","gift_wrap":"None","condition":"New","supplier_id":339,"estimated_delivery":"15-APR-07"},{"order_id":718849,"line_item_id":2,"product_id":576,"unit_price":862,"quantity":3,"dispatch_date":"13-FEB-12","gift_wrap":"None","condition":"New","supplier_id":372,"estimated_delivery":"30-JUL-11"},{}]}
SQL> 
```
   

#おわりに

Oracle ClientなどインストールしなくてもJREさえあれば、実行できるのがよいですね。
PCからの接続や、DB構築後の別サーバからの接続確認などに持って行っておくと便利そう。


##参考
 * SQLcl overview – A new Command Line Interface for Oracle Database from Jeff Smith
https://www.youtube.com/watch?v=HApdy-o525A

 * Oracle SQLcl Slidedeck: Overview of our new command line interface
http://www.thatjeffsmith.com/archive/2015/02/oracle-sqlcl-slidedeck-overview-of-our-new-command-line-interface/


##その他
本筋と関係ないところで、ハマったメモ

####MACでjavaのパスが通ってない問題

JREをMACにインストールしたのに、javaコマンドを打つと、インストールしろと言われる事象について。

https://www.java.com/ja/download/faq/java_mac.xml#cmdline
http://5log.jp/blog/path/