BLOBデータのバックアップ

• 多数ユーザがそれぞれデータ列を持つ必要があったため以下のようなデータベースを作った

CREATE TABLE "ADMIN"."USERS"
( "ID" NUMBER NOT NULL ENABLE,
"STORAGE" NVARCHAR2(260) ,
)

• 当初そのデータ列 STORAGEとして、 VARCHAR2でやっていたが、文字列がUNICODEだと切り出しが面倒だったり、部分置き換えが面倒だったりして、BLOBを使うことにした

CREATE TABLE "ADMIN"."USERS"
( "ID" NUMBER NOT NULL ENABLE,
"STORAGE" BLOB
)

• こうすると GOLANGからは

var storage []byte
err = db.QueryRowContext(ctx, "SELECT STORAGE FROM USERS WHERE ID = :1", userid).Scan(&storage)

• といった感じにすぐ読め、storage[添字]として配列へのアクセスや読み書きが楽だった
• ちなみに var storage [100]byteとしてはいけない
• 書き出しは

tx.Exec("UPDATE USERS SET STORAGE=:1  WHERE ID=:2", storage, userid)  // あとでCOMMITも

• ただし問題があって、SQL Developerなどからダンプすると、 通常のデータはそのまま書き出されるのに対して、BLOBデータだけは書き出されない
• やむを得ず、下記の PL/SQL を作成

create or replace PROCEDURE DUMP_USERS_BLOB AS 
  CURSOR cur1 IS
    SELECT ID,STORAGE FROM USERS;
  HEXSTR VARCHAR2(2000);
  IDSTR VARCHAR2(20);
BEGIN
-- SET SERVEROUTPUT ON
  FOR userrec IN cur1 LOOP
    IDSTR := TO_CHAR(userrec.ID);
    HEXSTR :=  sys.dbms_lob.substr(userrec.STORAGE);
    dbms_output.put_line( 'UPDATE USERS SET STORAGE=HEXTORAW(''' || HEXSTR || ''') WHERE ID=' || IDSTR || ';' );
  END LOOP;
END DUMP_USERS_BLOB;
/

• これを SQLPLUS上で読み込んだ上で、
  • SET SERVEROUTPUT ON
  • set linesize 2000
  • EXEC DUMP_USERS_BLOB()
  • として実行すると、BLOB を再構成できるUPDATE文を出力してくれる