この記事ではSQL*PlusでCOLUMN FORMATを自動で実行する方法を解説します。
自動でCOLUMN FORMAT文を実行する方法
SQL*PlusでSELECT文を実行するとデフォルトだと列の表示幅が狭く検索結果が見にくかったりします。
col for文を使うと列の表示幅を変更できますが、いちいち打つのも面倒ですよね。
そんなときに便利なのが、実行したselect 文に対して自動でcol for文を実行してくれるSQLスクリプトです。
スクリプトの内容は下記の参考にした記事まんまです。
参考にした記事
https://qiita.com/sowd/items/34da8eb99ec5f22fd7a4
set linesizeがうまく動作していなかったので、最後にset linesize を加えて自動でset linesize もされるように変更しています。
SQLスクリプト
下記の内容をcolfmt.sqlで保存し、SQL*Plusを実行するホームディレクトリに配置しましょう。
4～6行目は使用するクライアントの環境に合わせてパスを設定してください。
SET TERMOUT OFF
-- 一時ファイル名の設定 (環境に合わせて設定してください)
DEF _MY_TMPFILE_PATH_SAVE = "/tmp/__colfmtstr_save.tmp"
DEF _MY_TMPFILE_PATH_STORE = "/tmp/__colfmtstr_store.tmp"
DEF _MY_TMPFILE_PATH_SPOOL = "/tmp/__colfmtstr_spool.tmp"
-- バッファ・環境設定の保存
SAVE &_MY_TMPFILE_PATH_SAVE REPLACE
STORE SET &_MY_TMPFILE_PATH_STORE REPLACE
-- バッファの内容を取得
VAR MY_BUFFSTR CLOB
0 :MY_BUFFSTR := q'\
0 BEGIN
9999 \';;
9999 END;;
/
-- '
-- SETによる環境設定
SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WRAPPED
SET LINESIZE 32767
SET PAGESIZE 50000
SET LONG 2000000000
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING ON
SET MARKUP HTML OFF SPOOL OFF
SET TRIMOUT ON TRIMSPOOL ON
SET TAB OFF
SET TERMOUT ON
-- COL-FORMAT文生成
SPOOL &_MY_TMPFILE_PATH_SPOOL
DECLARE
vCRSR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
vVCHR2 VARCHAR2(4000);
vANYDATA ANYDATA;
vINT INTEGER;
vDEC INTEGER;
vSTATUS INTEGER;
vDESCTBL DBMS_SQL.DESC_TAB;
vCOLCNT INTEGER;
TYPE vCHRCNTARRTYPE IS TABLE OF INTEGER INDEX BY VARCHAR2(64);
vCHRCNTARR vCHRCNTARRTYPE;
vIDX INTEGER;
vFLG INTEGER;
BEGIN
-- SQL実行
DBMS_SQL.PARSE(vCRSR, :MY_BUFFSTR, DBMS_SQL.NATIVE);
DBMS_SQL.DESCRIBE_COLUMNS(vCRSR, vCOLCNT, vDESCTBL);
FOR I IN 1 .. vCOLCNT LOOP
IF vDESCTBL(I).COL_TYPE=109 THEN
-- ANYDATA型(COL_TYPEは109)の場合、第3引数をVARCHAR2型にするとエラーになる。
DBMS_SQL.DEFINE_COLUMN( vCRSR, I, vANYDATA);
ELSE
DBMS_SQL.DEFINE_COLUMN( vCRSR, I, vVCHR2, 4000);
END IF;
END LOOP;
vSTATUS := DBMS_SQL.EXECUTE(vCRSR);
-- =================================================================================
-- LENGTHB(CONVERT(vDESCTBL(I).COL_NAME, 'JA16EUC'))について
-- CONVERTで変換しているのは、日本語に対応するため。
-- 日本語一文字のサイズを"A2"としたい。しかし、EUC/SJISでは2バイト、UTF8では3バイト
-- だったりする。
-- そのため、一度JA16EUCに変換し、そのバイト数を取得するようにしている。
-- =================================================================================
-- 配列の初期化
FOR I IN 1 .. vCOLCNT LOOP
-- 初期値には列名のサイズを設定する。
-- （データの最大長が列名より短い場合に、一行目の列名文字列が切れてしまうのを防ぐため）
IF vDESCTBL(I).COL_TYPE = 2 THEN
-- 数値対応
-- 小数に対応
vCHRCNTARR(vDESCTBL(I).COL_NAME || '_int') := LENGTHB(CONVERT(vDESCTBL(I).COL_NAME, 'JA16EUC'));
vCHRCNTARR(vDESCTBL(I).COL_NAME || '_dec') := 0;
ELSE
-- その他の型
vCHRCNTARR(vDESCTBL(I).COL_NAME) := LENGTHB(CONVERT(vDESCTBL(I).COL_NAME, 'JA16EUC'));
END IF;
END LOOP;
-- 最大長の抽出
WHILE ( DBMS_SQL.FETCH_ROWS(vCRSR) > 0 ) LOOP
FOR I IN 1 .. vCOLCNT LOOP
IF (vDESCTBL(I).COL_TYPE = 109) THEN
-- ANYDATA型(COL_TYPEは109)は対処しようが無いので、無視。
CONTINUE;
END IF;
-- 値を取得
DBMS_SQL.COLUMN_VALUE(vCRSR, I, vVCHR2);
-- NULLの場合、なにもしない。
IF vVCHR2 IS NULL THEN
CONTINUE;
ELSIF (vDESCTBL(I).COL_TYPE = 2) THEN
-- 数値対応
vINT :=0;
vDEC :=0;
vFLG :=0;
FOR vIDX IN 1..length(vVCHR2) LOOP
IF substr(vVCHR2, vIDX, 1) = '.' THEN
vFLG := 1;
ELSIF vFLG = 0 THEN
vINT := vINT+1;
ELSE
vDEC := vDEC+1;
END IF;
END LOOP;
IF vINT > vCHRCNTARR(vDESCTBL(I).COL_NAME || '_int') THEN
vCHRCNTARR(vDESCTBL(I).COL_NAME || '_int') := vINT;
END IF;
IF vDEC > vCHRCNTARR(vDESCTBL(I).COL_NAME || '_dec') THEN
vCHRCNTARR(vDESCTBL(I).COL_NAME || '_dec') := vDEC;
END IF;
ELSE
-- その他の型
vINT := LENGTHB(CONVERT(vVCHR2, 'JA16EUC'));
IF vINT > vCHRCNTARR(vDESCTBL(I).COL_NAME) THEN
vCHRCNTARR(vDESCTBL(I).COL_NAME) := vINT;
END IF;
END IF;
END LOOP;
END LOOP;
-- COL-FORMAT文の生成
FOR I IN 1 .. vCOLCNT LOOP
IF vDESCTBL(I).COL_TYPE = 109 THEN
-- ANYDATA型(COL_TYPEは109)は対処しようが無いので、無視。
CONTINUE;
END IF;
DBMS_OUTPUT.PUT('COL ' || RPAD('"' || vDESCTBL(I).COL_NAME || '"', 32) || ' ');
IF vDESCTBL(I).COL_TYPE = 2 THEN
-- 数値フォーマット
vINT := vCHRCNTARR(vDESCTBL(I).COL_NAME || '_int');
vDEC := vCHRCNTARR(vDESCTBL(I).COL_NAME || '_dec');
DBMS_OUTPUT.PUT('FOR ' || LPAD('9', vINT, '9'));
IF vDEC <> 0 THEN
DBMS_OUTPUT.PUT('.' || LPAD('9', vDEC, '9'));
END IF;
DBMS_OUTPUT.PUT_LINE('');
ELSE
-- 文字フォーマット
vINT := vCHRCNTARR(vDESCTBL(I).COL_NAME);
-- 100文字以上の場合は"A100"に設定する。
IF vINT > 100 THEN
vINT := 100;
END IF;
DBMS_OUTPUT.PUT_LINE('FOR A' || vINT);
END IF;
END LOOP;
--- カーソルを閉じる。
DBMS_SQL.CLOSE_CURSOR(vCRSR);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('COLFMT_ERROR: ' || SQLCODE || ': ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('COLFMT_ERROR: (SELECT文でない可能性があります。)');
END;
/
SPOOL OFF
-- バッファ・環境設定の復元
SET TERMOUT OFF
@&_MY_TMPFILE_PATH_STORE
CL BUFF
GET &_MY_TMPFILE_PATH_SAVE NOLIST
-- 生成したCOL-FORMATの実行
@&_MY_TMPFILE_PATH_SPOOL
set linesize 250
SET TERMOUT ON 実行例
最初に普通にselect文を実行します。
SQL> select a.TABLESPACE_NAME
2 , min(a.BYTES)/1024/1024 "現サイズ(MB)"
3 , round(min(a.BYTES)/(1024*1024) - sum(b.BYTES)/ (1024*1024),2) "使用量(MB)"
4 , round((min(a.BYTES)/(1024*1024) - sum(b.BYTES)/(1024*1024))/
5 (min(a.BYTES)/1024/1024)*100,2) "使用率(%)"
6 , round(sum(b.BYTES)/(1024*1024),2) "空き容量(MB)"
7 from dba_data_files a, dba_free_space b
8 where a.FILE_ID = b.FILE_ID
9 group by a.TABLESPACE_NAME;
TABLESPACE_NAME 現サイズ(MB)
------------------------------------------------------------ ------------
使用量(MB) 使用率(%) 空き容量(MB)
---------- ---------- ------------
SYSTEM 1360
1358.19 99.87 1.81
SYSAUX 970
910.31 93.85 59.69
UNDOTBS1 120
18.63 15.52 101.38
TABLESPACE_NAME 現サイズ(MB)
------------------------------------------------------------ ------------
使用量(MB) 使用率(%) 空き容量(MB)
---------- ---------- ------------
USERS 5
2.69 53.75 2.31デフォルトのままだと表示が崩れています。
一度SQLを実行後colfmt.sqlを実行します。
SQL> @colfmt
COL "TABLESPACE_NAME" FOR A15
COL "現サイズ(MB)" FOR 999999999999
COL "使用量(MB)" FOR 9999999999.99
COL "使用率(%)" FOR 999999999.99
COL "空き容量(MB)" FOR 999999999999.99自動でCOL文が実行されます。
再度select文を実行します。
「/」を入力すると再度SQLを実行できます。
SQL> /
TABLESPACE_NAME 現サイズ(MB) 使用量(MB) 使用率(%) 空き容量(MB)
--------------- ------------- -------------- ------------- ----------------
SYSTEM 1360 1358.19 99.87 1.81
SYSAUX 970 910.31 93.85 59.69
UNDOTBS1 120 18.63 15.52 101.38
USERS 5 2.69 53.75 2.31表示がきれいになりました。