Posted at

"Oracle SQLcl"を使ってみる

More than 3 years have passed since last update.


はじめに


目的

JavaベースのOracleコマンドラインツールOracle SQLclを使ってみます。

JeffSmithさんの以下の動画やBLOGの内容を試したものになります。


環境


  • 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構築後の別サーバからの接続確認などに持って行っておくと便利そう。


参考


その他

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


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

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

https://www.java.com/ja/download/faq/java_mac.xml#cmdline

http://5log.jp/blog/path/