3
6

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 1 year has passed since last update.

【SQL*Plus】SETシステム変数の設定前後の具体例

Last updated at Posted at 2021-09-11

概要

SETシステム変数を変更すると何がどう変わるのかを具体的にSQL等を実行して確認します。

環境

Oracle 11.2.0

SETシステム変数の一覧

SETシステム変数の一覧は下記リンク(Oracleリファレンス)から取得しています。
リファレンスを見れば使い方がすぐに分かる(またはさっぱり分からない)場合は本記事での説明を無しにしています。
SETシステム変数の一覧

No SETシステム変数 本記事での説明有無 簡易説明
1 SET APPI[NFO] -
2 SET ARRAY[SIZE] -
3 SET AUTO[COMMIT] -
4 SET AUTOP[RINT] バインド変数自動出力
5 SET AUTORECOVERY -
6 SET AUTOT[RACE] 実行計画
7 SET BLO[CKTERMINATOR] -
8 SET CMDS[EP] -
9 SET COLSEP 列の区切り文字
10 SET CON[CAT] -
11 SET COPYC[OMMIT] -
12 SET COPYTYPECHECK -
13 SET DEF[INE] -
14 SET DESCRIBE -
15 SET ECHO 実行コマンド表示
16 SET EDITF[ILE] EDITコマンドの出力ファイル名
17 SET EMB[EDDED] -
18 SET ERRORL[OGGING] エラーログ記録
19 SET ESC[APE] -
20 SET ESCCHAR -
21 SET EXITC[OMMIT] -
22 SET FEED[BACK] 取得件数
23 SET FLAGGER SQL92準拠
24 SET FLU[SH] -
25 SET HEA[DING] カラム表示
26 SET HEADS[EP] カラム区切り
27 SET INSTANCE -
28 SET LIN[ESIZE] 1行表示数
29 SET LOBOF[FSET] -
30 SET LOGSOURCE -
31 SET LONG -
32 SET LONGC[HUNKSIZE] -
33 SET MARK[UP] -
34 SET NEWP[AGE] ページ最上部の空白行数
35 SET NULL null置換文字
36 SET NUMF[ORMAT] 数値フォーマット
37 SET NUM[WIDTH] 数値幅
38 SET PAGES[IZE] 1ページのサイズ
39 SET PAU[SE] -
40 SET RECSEP 行のセパレータ方式
41 SET RECSEPCHAR 行のセパレータ文字列
42 SET SERVEROUT[PUT] ストアド・プロシージャの出力(DBMS_OUTPUT PUT_LINE)またはPL/SQLブロックの出力
43 SET SHIFT[INOUT] -
44 SET SHOW[MODE] setコマンドの設定前後出力
45 SET SQLBL[ANKLINES] 空白行の入力
46 SET SQLC[ASE] 実行直前に大/小文字変換
47 SET SQLCO[NTINUE] ハイフン使用後のプロンプト
48 SET SQLN[UMBER] コマンド改行時の行番号
49 SET SQLPLUSCOMPAT[IBILITY] -
50 SET SQLPRE[FIX] 別コマンド実行時の接頭辞
51 SET SQLP[ROMPT] プロンプト変更
52 SET SQLT[ERMINATOR] 終了文字列
53 SET SUF[FIX] 拡張子
54 SET TAB タブ
55 SET TERM[OUT] 結果出力
56 SET TI[ME] 現在時刻
57 SET TIMI[NG] 実行時間
58 SET TRIM[OUT] 出力の空白除去
59 SET TRIMS[POOL] SPOOL出力の空白除去
60 SET UND[ERLINE] ヘッダー下線
61 SET VER[IFY] 置換前後
62 SET WRA[P] 行の折り返し
63 SET XMLOPT[IMIZATIONCHECK] -
64 SET XQUERY BASEURI -
65 SET XQUERY ORDERING -
66 SET XQUERY NODE -
67 SET XQUERY CONTEXT -

No4 SET AUTOP[RINT]

・デフォルト(OFF)

SQL> !cat test.sql
VARIABLE n NUMBER
BEGIN
:n := 1;
END;
/

SQL> @test.sql

PL/SQLプロシージャが正常に完了しました。

SQL> 

・ONに設定

SQL> !cat test.sql
VARIABLE n NUMBER
BEGIN
:n := 1;
END;
/

SQL> set AUTOPRINT ON
SQL> @test.sql

PL/SQLプロシージャが正常に完了しました。


         N
----------
         1

SQL> 

No6 SET AUTOT[RACE]

・デフォルト(OFF)

SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven

・ONに設定

SQL> set AUTOTRACE ON
SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven


実行計画
----------------------------------------------------------
Plan hash value: 1833546154

--------------------------------------------------------------------------------
-------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU
)| Time     |

--------------------------------------------------------------------------------
-------------

|   0 | SELECT STATEMENT            |               |     1 |    11 |     1   (0
)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    11 |     1   (0
)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0
)| 00:00:01 |

--------------------------------------------------------------------------------
-------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID"=100)


統計
----------------------------------------------------------
         94  recursive calls
          0  db block gets
        112  consistent gets
          0  physical reads
          0  redo size
        631  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

No9 SET COLSEP

・デフォルト(単一の空白)

SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven

・半角カンマ(,)に設定

SQL> set COLSEP ,
SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100;

EMPLOYEE_ID,FIRST_NAME
-----------,--------------------
        100,Steven

No15 SET ECHO

・デフォルト(OFF)

SQL> @test.sql

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven

・ONに設定

SQL> @test.sql
SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven

No16 SET EDITF[ILE]

・デフォルト(afiedt.buf)

SQL> show editf
editfile "afiedt.buf"
SQL> select EMPLOYEE_ID, MANAGER_ID from HR.EMPLOYEES where EMPLOYEE_ID = 100;

EMPLOYEE_ID MANAGER_ID
----------- ----------
        100

SQL> EDIT
file afiedt.bufが書き込まれました。
/bin/bash: ed: command not found

  1* select EMPLOYEE_ID, MANAGER_ID from HR.EMPLOYEES where EMPLOYEE_ID = 100
SQL> !ls
afiedt.buf

・test.txtに設定

SQL> select EMPLOYEE_ID, MANAGER_ID from HR.EMPLOYEES where EMPLOYEE_ID = 100;

EMPLOYEE_ID MANAGER_ID
----------- ----------
        100

SQL> EDIT
file test.txtが書き込まれました。
/bin/bash: ed: command not found

  1* select EMPLOYEE_ID, MANAGER_ID from HR.EMPLOYEES where EMPLOYEE_ID = 100
SQL> !ls
test.txt

No18 SET ERRORL[OGGING]

・デフォルト(OFF)

SQL> select EMPLOYEE_ID, MANAGER_ID;
select EMPLOYEE_ID, MANAGER_ID
                             *
1でエラーが発生しました。:
ORA-00923: FROMキーワードが指定の位置にありません。


SQL> select message from SPERRORLOG;

レコードが選択されませんでした。

SQL> 

・ONに設定

SQL> set ERRORLOGGING ON
SQL> select EMPLOYEE_ID, MANAGER_ID;
select EMPLOYEE_ID, MANAGER_ID
                             *
1でエラーが発生しました。:
ORA-00923: FROMキーワードが指定の位置にありません。


SQL> select message from SPERRORLOG;

MESSAGE
--------------------------------------------------------------------------------
ORA-00923: FROMキーワードが指定の位置にありません。

SQL> 

No22 SET FEED[BACK]

・デフォルト(ON)
 ※デフォルトだと6行以上の場合に「~行が選択されました。」メッセージが表示される。

SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where rownum <= 6;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven
        101 Neena
        102 Lex
        103 Alexander
        104 Bruce
        105 David

6行が選択されました。

・OFFに設定

SQL> set FEEDBACK OFF
SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where rownum <= 6;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven
        101 Neena
        102 Lex
        103 Alexander
        104 Bruce
        105 David

No23 SET FLAGGER

・デフォルト(OFF)

SQL> create table test (col1 BLOB);

表が作成されました。

・ON(ENTRY)に設定

SQL> set FLAGGER ENTRY
SQL> create table test (col1 BLOB);
create table test (col1 BLOB)
*
1でエラーが発生しました。:
ORA-00097: Oracle SQL機能は、SQL92 Entryレベルでは使用できません。

No25 SET HEA[DING]

・デフォルト(ON)

SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven

・OFFに設定

SQL> set HEADING OFF
SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100;

        100 Steven

No26 SET HEADS[EP]

・デフォルト(|)

SQL> COLUMN FIRST_NAME HEADING 'FIRST|NAME,TEST'
SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100;

            FIRST
EMPLOYEE_ID NAME,TEST
----------- --------------------
        100 Steven

・半角カンマ(,)に設定
 ※設定後はCOLUMNコマンドを再度実行しないと反映されない

SQL> set HEADSEP ,
SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100;

            FIRST
EMPLOYEE_ID NAME,TEST
----------- --------------------
        100 Steven

SQL> COLUMN FIRST_NAME HEADING 'FIRST|NAME,TEST'
SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100;

            FIRST|NAME
EMPLOYEE_ID TEST
----------- --------------------
        100 Steven

No28 SET LIN[ESIZE]

・デフォルト(80)

SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven

SQL> show lines

・10に設定

SQL> set LINESIZE 10
SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100;

EMPLOYEE_I
----------
FIRST_NAME
----------
       100
Steven

No34 SET NEWP[AGE]

・デフォルト(1)

SQL> select EMPLOYEE_ID, MANAGER_ID from HR.EMPLOYEES where EMPLOYEE_ID = 100;

EMPLOYEE_ID MANAGER_ID
----------- ----------
        100

・3に設定

SQL> select EMPLOYEE_ID, MANAGER_ID from HR.EMPLOYEES where EMPLOYEE_ID = 100;



EMPLOYEE_ID MANAGER_ID
----------- ----------
        100

No35 SET NULL

・デフォルト(空白)

SQL> select EMPLOYEE_ID, MANAGER_ID from HR.EMPLOYEES where EMPLOYEE_ID = 100;

EMPLOYEE_ID MANAGER_ID
----------- ----------
        100

・emptyという文字列に設定

SQL> set NULL empty
SQL> select EMPLOYEE_ID, MANAGER_ID from HR.EMPLOYEES where EMPLOYEE_ID = 100;

EMPLOYEE_ID MANAGER_ID
----------- ----------
        100 empty

No36 SET NUMF[ORMAT]

・デフォルト(空白)

SQL> select SALARY from HR.EMPLOYEES where EMPLOYEE_ID = 100;

    SALARY
----------
     24000

・999,999,999に設定

SQL> set NUMFORMAT 999,999,999
SQL> select SALARY from HR.EMPLOYEES where EMPLOYEE_ID = 100;

      SALARY
------------
      24,000

No37 SET NUM[WIDTH]

・デフォルト(10)

SQL> select SALARY from HR.EMPLOYEES where EMPLOYEE_ID = 100;

    SALARY
----------
     24000

・5に設定

SQL> set NUMWIDTH 5
SQL> select SALARY from HR.EMPLOYEES where EMPLOYEE_ID = 100;

SALARY
------
 24000

No38 SET PAGES[IZE]

・デフォルト(14)

SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where rownum <= 14;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven
        101 Neena
        102 Lex
        103 Alexander
        104 Bruce
        105 David
        106 Valli
        107 Diana
        108 Nancy
        109 Daniel
        110 John

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        111 Ismael
        112 Jose Manuel
        113 Luis

14行が選択されました。

・15に設定

SQL> set PAGESIZE 15
SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where rownum <= 14;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven
        101 Neena
        102 Lex
        103 Alexander
        104 Bruce
        105 David
        106 Valli
        107 Diana
        108 Nancy
        109 Daniel
        110 John
        111 Ismael

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        112 Jose Manuel
        113 Luis

14行が選択されました。

No40, 41 SET RECSEP, SET RECSEPCHAR

・デフォルト(RECSEPはWRAP、RECSEPCHARは単一の空白)

SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven

・RECSEPはEACH、RECSEPCHARは*に設定

SQL> set RECSEP EACH
SQL> set RECSEPCHAR *
SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven
********************************************************************************

No42 SET SERVEROUT[PUT]

・デフォルト(OFF)

SQL> show serverout
serveroutput OFF
SQL> begin
  2  DBMS_OUTPUT.PUT_LINE('test');
  3  end; 
  4  /

PL/SQLプロシージャが正常に完了しました。

・ONに設定

SQL> set SERVEROUTPUT ON
SQL> begin
  2  DBMS_OUTPUT.PUT_LINE('test');
  3  end;
  4  /
test

PL/SQLプロシージャが正常に完了しました。

No44 SET SHOW[MODE]

・デフォルト(OFF)

SQL> set ECHO ON
SQL> 

・ONに設定

SQL> set SHOWMODE ON
: showmode BOTH
SQL> set ECHO ON
: echo OFF
: echo ON
SQL> 

No45 SET SQLBL[ANKLINES]

・デフォルト(OFF)

SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100          
  2  
SQL> 

・ONに設定

SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100 
  2  
  3  ;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven

SQL> 

No46 SET SQLC[ASE]

・デフォルト(MIXED)

SQL> select EMPLOYEE_ID, MANAGER_ID from HR.EMPLOYEES where EMPLOYEE_ID = 101;

EMPLOYEE_ID MANAGER_ID
----------- ----------
        101        100

SQL> select sql_text from (select * from v$sql where sql_text not like '%v$sql%' and sql_text like '%EMPLOYEE_ID%' order by first_load_time desc) where rownum = 1;

SQL_TEXT
--------------------------------------------------------------------------------
select EMPLOYEE_ID, MANAGER_ID from HR.EMPLOYEES where EMPLOYEE_ID = 101

・UPPERに設定

SQL> set SQLCASE UPPER
SQL> select EMPLOYEE_ID, MANAGER_ID from HR.EMPLOYEES where EMPLOYEE_ID = 101;

EMPLOYEE_ID MANAGER_ID
----------- ----------
        101        100

SQL> select sql_text from (select * from v$sql where sql_text not like '%v$sql%' and sql_text like '%EMPLOYEE_ID%' order by first_load_time desc) where rownum = 1;

SQL_TEXT
--------------------------------------------------------------------------------
SELECT EMPLOYEE_ID, MANAGER_ID FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = 101

No47 SET SQLCO[NTINUE]

・デフォルト("> ")

SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100 -
> ;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven

・"% "に設定

SQL> set SQLCONTINUE "% "
SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100 -
% ;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven

No48 SET SQLN[UMBER]

・デフォルト(ON)

SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100
  2  ;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven

・OFFに設定

SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100
SQL> ;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven

No50 SET SQLPRE[FIX]

・デフォルト(#)

SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where
  2  #select 'test' from dual;

'TES
----
test

・%に設定

SQL> set SQLPREFIX %
SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where
  2  %select 'test' from dual;

'TES
----
test

No51 SET SQLP[ROMPT]

・デフォルト("SQL> ")

SQL> 

・"test> "に設定

SQL> set SQLPROMPT "test> "
test> 

No52 SET SQLT[ERMINATOR]

・デフォルト(;)

SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven

・%に設定

SQL> set SQLTERMINATOR %
SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100%

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven

No53 SET SUF[FIX]

・デフォルト(sql)

SQL> !ls
test.sql  test.txt

SQL> get test
  1* sql extension file

・OFFに設定

SQL> set SUFFIX txt
SQL> !ls
test.sql  test.txt

SQL> get test
  1* txt extension file

No54 SET TAB

・デフォルト(ON)
trimout_on.png

・OFFに設定
tab_off.png

No55 SET TERM[OUT]

・デフォルト(ON)

SQL> @test.sql

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven

・OFFに設定

SQL> set TERMOUT OFF
SQL> @test.sql
SQL> 

No56 SET TI[ME]

・デフォルト(OFF)

SQL> 
SQL> 

・ONに設定

08:25:14 SQL> 
08:25:17 SQL> 

No57 SET TIMI[NG]

・デフォルト(OFF)

SQL> select count(*) from all_tables;

  COUNT(*)
----------
      1663

・ONに設定

SQL> set TIMING ON
SQL> select count(*) from all_tables;

  COUNT(*)
----------
      1663

経過: 00:00:00.02

No58 SET TRIM[OUT]

・デフォルト(ON)
trimout_on.png

・OFFに設定
trimout_off.png

No59 SET TRIMS[POOL]

・デフォルト(OFF)
trims_off.png

・ONに設定
trims_on.png

No60 SET UND[ERLINE]

・デフォルト(-)

SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven

・*に設定

SQL> select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100;

EMPLOYEE_ID FIRST_NAME
*********** ********************
        100 Steven

No61 SET VER[IFY]

・デフォルト(ON)

SQL> @test.sql
1に値を入力してください: 100
   1: select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = &1
   1: select EMPLOYEE_ID, FIRST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 100

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven

・OFFに設定

SQL> set VERIFY OFF
SQL> @test.sql
1に値を入力してください: 100

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven

No62 SET WRAP

・デフォルト(ON)

SQL> select * from HR.EMPLOYEES where EMPLOYEE_ID = 100;

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
EMAIL                     PHONE_NUMBER         HIRE_DATE           JOB_ID
------------------------- -------------------- ------------------- ----------
    SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- -------------- ---------- -------------
        100 Steven               King
SKING                     515.123.4567         2003/06/17 00:00:00 AD_PRES
     24000                                      90

・OFFに設定

SQL> set WRAP OFF
SQL> select * from HR.EMPLOYEES where EMPLOYEE_ID = 100;
行は切り捨てられます。

行は切り捨てられます。

行は切り捨てられます。

行は切り捨てられます。

行は切り捨てられます。

行は切り捨てられます。

行は切り捨てられます。


EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL
----------- -------------------- ------------------------- ---------------------
        100 Steven               King                      SKING
3
6
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
3
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?