oracle
plsql

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(30);
    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