Go to Qiita Advent Calendar 2024 Top
LoginSignup
21
21

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@mon_tu

"Oracle SQLcl"を使ってみる

Posted at

#はじめに
##目的

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/

21
21
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
21
21

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?