Edited at

SQL*Plus COL FORMATの自動設定スクリプト


内容

SQL*Plusにて、毎回毎回COL〜FOR〜で整形するのがめんどくさい!

そこで、自動でCOL〜FOR〜文を生成し、設定してくれるSQLスクリプト"colfmt.sql"を作ってみました。


使い方

以下のような使い方を想定している。



  1. SELECT文の実行

    SQL> SELECT INSTANCE_NAME, HOST_NAME, VERSION FROM V$INSTANCE;

    INSTANCE_NAME HOST_NAME VERSION
    ---------------- ---------------------------------------------------------------- -----------------
    TEST01 rhel01 11.2.0.4.0

    --列幅がめちゃくちゃな状態で表示される




  2. colfmt.sqlスクリプトの実行

    SQL> @colfmt
    
    COL "INSTANCE_NAME" FOR A13
    COL "HOST_NAME" FOR A9
    COL "VERSION" FOR A10



  3. 再度、SELECT文を実行

    SQL> /

    INSTANCE_NAME HOST_NAME VERSION
    ------------- --------- ----------
    TEST01 rhel01 11.2.0.4.0




SQLスクリプト

「★」マークの部分は環境に合わせて設定する必要があります。


colfmt.sql

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 TERMOUT ON