BLOB2String #string

ここは広告です。（よろしくお願いいたします。 )

AMAZON (アーケード) ARCADE チノパンツチノスキニーメンズチノパンカーゴ小尻・脚長カラー

以下は、PLSQLでBLOB項目を文字列に変換し、ローカルファイルに出力する処理です。

--sqlplus -s username/password@tns @thissql

WHENEVER SQLERROR EXIT 1
SET LINES 32000
SET TERMOUT OFF
SET ECHO OFF
SET NEWP 0
SET SPA 0
SET PAGES 0
SET FEED OFF
SET HEAD OFF
SET TRIMS ON
SET TAB OFF

PROMPT
SET VERIFY OFF

VARIABLE PDF_FILE_ID NUMBER

BEGIN
    :PDF_FILE_ID := -1;
    :PDF_FILE_ID := TO_NUMBER('&1');
EXCEPTION
    WHEN OTHERS THEN NULL;
END;
/

SET SERVEROUTPUT ON SIZE UNLIMITED

spool &1..txt

DECLARE
    CURSOR cur_LOB_SIZE(id NUMBER) IS
        SELECT
            DBMS_LOB.GETLENGTH(LOB_ITEM) AS "SIZE"
        FROM
            USER_SCHEMA.LOB_TABLE_NAME TBL
        WHERE
            ID_PRIMARY = id
        AND COND_ITEM IN ('application/pdf', 'application/x-fmfcm')
    ;

    CURSOR cur_LOB_TEXT(id NUMBER, offset NUMBER, amount NUMBER) IS
        SELECT
            DBMS_LOB.SUBSTR( LOB_ITEM, amount, offset) AS PDF
        FROM
            USER_SCHEMA.LOB_TABLE_NAME TBL
        WHERE
           ID_PRIMARY = id
    ;

    C__SIZE             NUMBER := 1000;
    pw_BUFFER           VARCHAR2(32767);

    pw_LENGTH           NUMBER := 0;

    pw_COUNT            NUMBER := 0;
    pw_SIZE             NUMBER := 0;

BEGIN
    DBMS_OUTPUT.ENABLE(NULL);

    OPEN cur_LOB_SIZE(:PDF_FILE_ID);
    FETCH cur_LOB_SIZE INTO pw_LENGTH;
    CLOSE cur_LOB_SIZE;

    IF pw_LENGTH > 0 THEN
        LOOP
            IF pw_COUNT + C__SIZE > pw_LENGTH THEN
                pw_SIZE := pw_LENGTH - pw_COUNT;
            ELSE
                pw_SIZE := C__SIZE;
            END IF;

            OPEN cur_LOB_TEXT(:PDF_FILE_ID, pw_COUNT + 1, pw_SIZE);
            FETCH cur_LOB_TEXT INTO pw_BUFFER;
            CLOSE cur_LOB_TEXT;

            DBMS_OUTPUT.PUT_LINE(pw_BUFFER);

            pw_COUNT := pw_COUNT + pw_SIZE;

            IF pw_COUNT >= pw_LENGTH THEN
                EXIT;
            END IF;
        END LOOP;
    END IF;
END;
/

spool off

SET TERMOUT ON

PROMPT
HOST if exist &1..txt. ECHO &1..txt is created.

SET TERMOUT OFF