はじめに
目的
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-o525AOracle 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ファイルを解凍します。
$ 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に実行権限を付与します。
$ cd sqlcl/bin
$ chmod u+x sql
起動＆接続
Java7 JREが動作することを確認します。
$ 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で接続します。
$ 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> 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> 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> 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> 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> 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> 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-o525AOracle 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/