0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@jri_narita(成田 裕一郎)

SQLcl のすゝめ (n番煎じ)

0
Last updated at Posted at 2026-10-19

はじめに

みなさん SQLcl は使っていますか？
「身体がsqlplus xxx/xxx…を覚えていて中々抜け出せない…」という方もまだたくさんいらっしゃるんじゃないでしょうか。
便利な機能を知って使えば逆に SQL*Plus には戻れなくなると思うので、n番煎じではありますがアラカルトで紹介します。(できるだけマイナーなものに絞ったつもりです)

オブジェクトの DDL 取得

SQL*Plus でオブジェクトの DDL を取得するときにはdbms_metadata.get_ddlを使う方が多いと思います。

SQL> select dbms_metadata.get_ddl('TABLE','CUSTOMERS','SOE') from dual;

DBMS_METADATA.GET_DDL('TABLE','CUSTOMERS','SOE')
________________________________________________________________________________________________________

  CREATE TABLE "SOE"."CUSTOMERS"
   (    "CUSTOMER_ID" NUMBER(12,0) CONSTRAINT "CUST_CUSTID_NN" NOT NULL ENABLE,
        "CUST_FIRST_NAME" VARCHAR2(40) COLLATE "USING_NLS_COMP" CONSTRAINT "CUST_FNAME_NN" NOT NULL ENABLE,
        "CUST_LAST_NAME" VARCHAR2(40) COLLATE "USING_NLS_COMP" CONSTRAINT "CUST_LNAME_NN" NOT NULL ENABLE,
        "NLS_LANGUAGE" VARCHAR2(3) COLLATE "USING_NLS_COMP",
        "NLS_TERRITORY" VARCHAR2(90) COLLATE "USING_NLS_COMP",
        "CREDIT_LIMIT" NUMBER(9,2),
        "CUST_EMAIL" VARCHAR2(100) COLLATE "USING_NLS_COMP",
        "ACCOUNT_MGR_ID" NUMBER(12,0),
        "CUSTOMER_SINCE" DATE,
        "CUSTOMER_CLASS" VARCHAR2(40) COLLATE "USING_NLS_COMP",
        "SUGGESTIONS" VARCHAR2(40) COLLATE "USING_NLS_COMP",
        "DOB" DATE,
        "MAILSHOT" VARCHAR2(1) COLLATE "USING_NLS_COMP",
        "PARTNER_MAILSHOT" VARCHAR2(1) COLLATE "USING_NLS_COMP",
        "PREFERRED_ADDRESS" NUMBER(12,0),
        "PREFERRED_CARD" NUMBER(12,0),
         CONSTRAINT "CUSTOMER_CREDIT_LIMIT_MAX" CHECK (credit_limit <= 50000) DEFERRABLE ENABLE NOVALIDATE,
         CONSTRAINT "CUSTOMER_ID_MIN" CHECK (customer_id > 0) DEFERRABLE ENABLE NOVALIDATE
   )  DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA"
  CREATE UNIQUE INDEX "SOE"."CUSTOMERS_PK" ON "SOE"."CUSTOMERS" ("CUSTOMER_ID") REVERSE
  PCTFREE 10 INITRANS 20 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA"
  PARALLEL 2
ALTER TABLE "SOE"."CUSTOMERS" ADD CONSTRAINT "CUSTOMERS_PK" PRIMARY KEY ("CUSTOMER_ID")
  USING INDEX "SOE"."CUSTOMERS_PK"  ENABLE NOVALIDATE

SQL>

SQLcl では、ddl オブジェクト名で DDL を取得できるようになりました。
長ったらしいプロシージャ名を覚える必要がなくなりました。

SQL> ddl soe.customers


  CREATE TABLE "SOE"."CUSTOMERS"
   (    "CUSTOMER_ID" NUMBER(12,0) CONSTRAINT "CUST_CUSTID_NN" NOT NULL ENABLE,
        "CUST_FIRST_NAME" VARCHAR2(40) COLLATE "USING_NLS_COMP" CONSTRAINT "CUST_FNAME_NN" NOT NULL ENABLE,
        "CUST_LAST_NAME" VARCHAR2(40) COLLATE "USING_NLS_COMP" CONSTRAINT "CUST_LNAME_NN" NOT NULL ENABLE,
        "NLS_LANGUAGE" VARCHAR2(3) COLLATE "USING_NLS_COMP",
        "NLS_TERRITORY" VARCHAR2(90) COLLATE "USING_NLS_COMP",
        "CREDIT_LIMIT" NUMBER(9,2),
        "CUST_EMAIL" VARCHAR2(100) COLLATE "USING_NLS_COMP",
        "ACCOUNT_MGR_ID" NUMBER(12,0),
        "CUSTOMER_SINCE" DATE,
        "CUSTOMER_CLASS" VARCHAR2(40) COLLATE "USING_NLS_COMP",
        "SUGGESTIONS" VARCHAR2(40) COLLATE "USING_NLS_COMP",
        "DOB" DATE,
        "MAILSHOT" VARCHAR2(1) COLLATE "USING_NLS_COMP",
        "PARTNER_MAILSHOT" VARCHAR2(1) COLLATE "USING_NLS_COMP",
        "PREFERRED_ADDRESS" NUMBER(12,0),
        "PREFERRED_CARD" NUMBER(12,0),
         CONSTRAINT "CUSTOMER_CREDIT_LIMIT_MAX" CHECK (credit_limit <= 50000) DEFERRABLE ENABLE NOVALIDATE,
         CONSTRAINT "CUSTOMER_ID_MIN" CHECK (customer_id > 0) DEFERRABLE ENABLE NOVALIDATE
   )  DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA" ;
  CREATE UNIQUE INDEX "SOE"."CUSTOMERS_PK" ON "SOE"."CUSTOMERS" ("CUSTOMER_ID") REVERSE
  PCTFREE 10 INITRANS 20 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA"
  PARALLEL 2 ;
ALTER TABLE "SOE"."CUSTOMERS" ADD CONSTRAINT "CUSTOMERS_PK" PRIMARY KEY ("CUSTOMER_ID")
  USING INDEX "SOE"."CUSTOMERS_PK"  ENABLE NOVALIDATE;

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

  CREATE INDEX "SOE"."CUST_DOB_IX" ON "SOE"."CUSTOMERS" ("DOB")
  PCTFREE 10 INITRANS 20 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA"
  PARALLEL 2 ;

  CREATE INDEX "SOE"."CUST_EMAIL_IX" ON "SOE"."CUSTOMERS" ("CUST_EMAIL")
  PCTFREE 10 INITRANS 20 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA"
  PARALLEL 2 ;

  CREATE INDEX "SOE"."CUST_FUNC_LOWER_NAME_IX" ON "SOE"."CUSTOMERS" (LOWER("CUST_LAST_NAME"), LOWER("CUST_FIRST_NAME"))
  PCTFREE 10 INITRANS 20 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA"
  PARALLEL 2 ;
SQL>

CTAS文の生成

ctas 既存テーブル名 新規テーブル名で CTAS文を生成できます。
CTAS文を生成するだけで、実行はされません。

SQL> ctas warehouses wh

  CREATE TABLE "SOE"."WH"
   (    "WAREHOUSE_ID",
        "WAREHOUSE_NAME",
        "LOCATION_ID"
   )  DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA"
  CREATE UNIQUE INDEX "SOE"."WAREHOUSES_PK" ON "SOE"."WH" ("WAREHOUSE_ID")
  PCTFREE 10 INITRANS 20 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA"
  PARALLEL 2
ALTER TABLE "SOE"."WH" ADD CONSTRAINT "WAREHOUSES_PK" PRIMARY KEY ("WAREHOUSE_ID")
  USING INDEX "SOE"."WAREHOUSES_PK"  ENABLE NOVALIDATE
 as
select * from WAREHOUSES
SQL>
SQL> select table_name from user_tables order by 1;

TABLE_NAME
_______________________
ADDRESSES
CARD_DETAILS
CUSTOMERS
INVENTORIES
LOGON
ORDERENTRY_METADATA
ORDERS
ORDER_ITEMS
PRODUCT_DESCRIPTIONS
PRODUCT_INFORMATION
WAREHOUSES

11 rows selected.

SQL>

SQL のエイリアス作成

SQL のエイリアスを作成して実行することができます。
秘伝の SQL が書かれたテキストファイルを用意しておく必要がなくなります。

SQL> alias ashcnt = select count(1) from dba_hist_active_sess_history;
Alias ashcnt added
SQL>
SQL> ashcnt

   COUNT(1)
___________
      28679

SQL>
SQL> alias list
ashcnt
SQL>
SQL> alias drop ashcnt
Alias ashcnt dropped
SQL> alias list
ALIAS-026: No user defined aliases found

SQL>

SELECT結果の出力フォーマットの変更

set sqlformat フォーマット名で SELECT結果の出力フォーマットを変更可能です。
csv や json を指定可能です。

SQL> sho sqlformat
SQL Format : ansiconsole
SQL>
SQL>
SQL> select * from soe.warehouses fetch first 5 rows only;

   WAREHOUSE_ID WAREHOUSE_NAME                     LOCATION_ID
_______________ _______________________________ ______________
              1 80lweAGBlE6T6W7NLJrVwgvPmkpt              2375
              2 asx12Iu                                   3556
              3 mnLSG4FwFPyBodgN                          9055
              4 i1P1lQT                                   7084
              5 95fW8                                     3112

SQL>
SQL> set sqlformat csv
SQL> select * from soe.warehouses fetch first 5 rows only;
"WAREHOUSE_ID","WAREHOUSE_NAME","LOCATION_ID"
1,"80lweAGBlE6T6W7NLJrVwgvPmkpt",2375
2,"asx12Iu",3556
3,"mnLSG4FwFPyBodgN",9055
4,"i1P1lQT",7084
5,"95fW8",3112

SQL>
SQL> set sqlformat json
SQL> select * from soe.warehouses fetch first 5 rows only;
{"results":[{"columns":[{"name":"WAREHOUSE_ID","type":"NUMBER"},{"name":"WAREHOUSE_NAME","type":"VARCHAR2"},{"name":"LOCATION_ID","type":"NUMBER"}],"items":
[
{"warehouse_id":1,"warehouse_name":"80lweAGBlE6T6W7NLJrVwgvPmkpt","location_id":2375}
,{"warehouse_id":2,"warehouse_name":"asx12Iu","location_id":3556}
,{"warehouse_id":3,"warehouse_name":"mnLSG4FwFPyBodgN","location_id":9055}
,{"warehouse_id":4,"warehouse_name":"i1P1lQT","location_id":7084}
,{"warehouse_id":5,"warehouse_name":"95fW8","location_id":3112}
]}]}
SQL>
SQL>
SQL> set sqlformat html
SQL>
SQL> select * from soe.warehouses fetch first 5 rows only;
<!DOCTYPE html>
<html>

<head>
  <meta charset='UTF-8'>

  <title>Result Data</title>

  <meta name="viewport" content="width=device-width, initial-scale=1.0">


  <style>
  * {
    margin: 0;
    padding: 0;
  }
  body {
    font: 14px/1.4 Palatino, Serif;
  }

  /*
  Generic Styling, for Desktops/Laptops
  */
  table {
    width: 100%;
    border-collapse: collapse;
  }
  /* Zebra striping */
  tr:nth-of-type(odd) {
    background: #eee;
  }
  th {
    background: #333;
    color: white;
    font-weight: bold;
  }
  td, th {
    padding: 6px;
    border: 1px solid #9B9B9B;
    text-align: left;
  }
  @media
  only screen and (max-width: 760px),
  (min-device-width: 768px) and (max-device-width: 1024px)  {
    table, thead, tbody, th, td, tr { display: block; }
    thead tr { position: absolute;top: -9999px;left: -9999px;}
    tr { border: 1px solid #9B9B9B; }
    td { border: none;border-bottom: 1px solid #9B9B9B; position: relative;padding-left: 50%; }

    td:before { position: absolute;top: 6px;left: 6px;width: 45%; padding-right: 10px; white-space: nowrap;}

    /*
    Label the data
    */
td:nth-of-type(1):before { content: "WAREHOUSE_ID"; }
td:nth-of-type(2):before { content: "WAREHOUSE_NAME"; }
td:nth-of-type(3):before { content: "LOCATION_ID"; }
  }

  /* Smartphones (portrait and landscape) ----------- */
  @media only screen
  and (min-device-width : 320px)
  and (max-device-width : 480px) {
    body {
      padding: 0;
      margin: 0;
      width: 320px; }
    }

  /* iPads (portrait and landscape) ----------- */
  @media only screen and (min-device-width: 768px) and (max-device-width: 1024px) {
    body {
      width: 495px;
    }
  }

  </style>
  <!--<![endif]-->
<script type="text/javascript">

function search(){

  var s = document.getElementById('search').value;

  rows = document.getElementById('data').getElementsByTagName('TR');
  for(var i=0;i<rows.length;i++){
    if ( rows[i].textContent.indexOf(s)>0  || s.length==0 ) {
          rows[i].style.display ='';
    } else {
      rows[i].style.display ='none';
    }
  }
}


var timer;
function delayedSearch() {
        clearTimeout(timer);
        console.log('delay-ing')
    timer = setTimeout(function () {
                console.log('delay-running')
                search();
    }, 500);
  }</script>
</head>

<body>
<div><input type="text" size="30" maxlength="1000" value="" id="search" onkeyup="delayedSearch();" /><input type="button" value="Go" onclick="lsearch();"/> </div>
<table><thead><tr>      <th>WAREHOUSE_ID</th>
        <th>WAREHOUSE_NAME</th>
        <th>LOCATION_ID</th>
</tr></thead>
<tbody id="data">

        <tr>
<td align="right">920</td>
<td>f0B9u40FctuuhoXz0sBOxc5BoxuEvVy</td>
<td align="right">783</td>
        </tr>
        <tr>
<td align="right">921</td>
<td>lBQ86IKvPOa8ccgUoEsrcackRKsVi1M27</td>
<td align="right">6289</td>
        </tr>
        <tr>
<td align="right">922</td>
<td>vF6txK</td>
<td align="right">1556</td>
        </tr>
        <tr>
<td align="right">923</td>
<td>LnDN7YIPtUfWGPrpc6Q</td>
<td align="right">3842</td>
        </tr>
        <tr>
<td align="right">924</td>
<td>h6eC</td>
<td align="right">8251</td>
        </tr>
</tbody></table><!-- SQL:
select * from soe.warehouses fetch first 5 rows only--></body></html>

SQL>
SQL>

image.png

簡易的なデータロード

loadを使うことで簡易的なデータロードが可能です。

SQL> !ls -la *csv
-rw-r--r--. 1 opc opc 322 Oct 15 05:23 sales_channels.csv

SQL> !cat sales_channels.csv
"CHANNEL_ID","CHANNEL_DESC","CHANNEL_CLASS","CHANNEL_CLASS_ID","CHANNEL_TOTAL","CHANNEL_TOTAL_ID"
3,"Direct Sales","Direct",12,"Channel total",1
9,"Tele Sales","Direct",12,"Channel total",1
5,"Catalog","Indirect",13,"Channel total",1
4,"Internet","Indirect",13,"Channel total",1
2,"Partners","Others",14,"Channel total",1

SQL>
SQL>
SQL> create table testtab(CHANNEL_ID number, CHANNEL_DESC varchar2(20), CHANNEL_CLASS varchar2(20), CHANNEL_CLASS_ID number, CHANNEL_TOTAL varchar2(20),CHANNEL_TOTAL_ID number);

Table TESTTAB created.

SQL> load testtab sales_channels.csv

csv
column_names on
delimiter ,
enclosures ""
double
encoding UTF8
row_limit off
row_terminator default
skip_rows 0
skip_after_names

Load data into table ADMIN.TESTTAB

#INFO Number of rows processed: 5
#INFO Number of rows in error: 0
#INFO Last row processed in final committed batch: 5
SUCCESS: Processed without errors
SQL>
SQL>
SQL> select * from testtab;

   CHANNEL_ID CHANNEL_DESC    CHANNEL_CLASS       CHANNEL_CLASS_ID CHANNEL_TOTAL       CHANNEL_TOTAL_ID
_____________ _______________ ________________ ___________________ ________________ ___________________
            3 Direct Sales    Direct                            12 Channel total                      1
            9 Tele Sales      Direct                            12 Channel total                      1
            5 Catalog         Indirect                          13 Channel total                      1
            4 Internet        Indirect                          13 Channel total                      1
            2 Partners        Others                            14 Channel total                      1

SQL>

SQL を指定回数・指定間隔で繰り返し実行

repeat 回数 間隔(秒)で、直後に実行した SQL を繰り返し実行することが可能です。
監視が必要になった時などに重宝します。

SQL> select systimestamp from dual;

SYSTIMESTAMP
______________________________________
15-OCT-26 06.36.33.907154000 AM GMT

SQL> repeat 10 1

Running 1 of 10  @ 06:36:57.013 with a delay of 1s
  1* select systimestamp from dual;

SYSTIMESTAMP
______________________________________
15-OCT-26 06.36.57.027012000 AM GMT


Running 2 of 10  @ 06:36:58.037 with a delay of 1s
  1* select systimestamp from dual;

SYSTIMESTAMP
______________________________________
15-OCT-26 06.36.58.050173000 AM GMT


Running 3 of 10  @ 06:36:59.057 with a delay of 1s
  1* select systimestamp from dual;

SYSTIMESTAMP
______________________________________
15-OCT-26 06.36.59.063320000 AM GMT


Running 4 of 10  @ 06:37:00.070 with a delay of 1s
  1* select systimestamp from dual;

SYSTIMESTAMP
______________________________________
15-OCT-26 06.37.00.078294000 AM GMT


Running 5 of 10  @ 06:37:01.083 with a delay of 1s
  1* select systimestamp from dual;

SYSTIMESTAMP
______________________________________
15-OCT-26 06.37.01.090794000 AM GMT


Running 6 of 10  @ 06:37:02.101 with a delay of 1s
  1* select systimestamp from dual;

SYSTIMESTAMP
______________________________________
15-OCT-26 06.37.02.109918000 AM GMT


Running 7 of 10  @ 06:37:03.118 with a delay of 1s
  1* select systimestamp from dual;

SYSTIMESTAMP
______________________________________
15-OCT-26 06.37.03.124997000 AM GMT


Running 8 of 10  @ 06:37:04.132 with a delay of 1s
  1* select systimestamp from dual;

SYSTIMESTAMP
______________________________________
15-OCT-26 06.37.04.141545000 AM GMT


Running 9 of 10  @ 06:37:05.149 with a delay of 1s
  1* select systimestamp from dual;

SYSTIMESTAMP
______________________________________
15-OCT-26 06.37.05.154024000 AM GMT


Running 10 of 10  @ 06:37:06.158 with a delay of 1s
  1* select systimestamp from dual;

SYSTIMESTAMP
______________________________________
15-OCT-26 06.37.06.164075000 AM GMT

SQL>
SQL>

SQLcl の便利機能を使いこなして、SQL*Plus を卒業しましょう！
1.1 SQLclコマンド(アルファベット順)

0
0
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
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?