我可以将二进制字符串存储在CLOB列中吗 [英] Can I store binary string in CLOB column

问题描述

我有一个带CLOB列的大桌子.现在将文本数据存储在其中.但是我想写二进制字符串.在那种情况下我会遇到麻烦吗?

I have a large table with CLOB column. A text data is stored in it now. But I want to write binary string. Would I face some kind of troubles in that case?

迁移到BLOB不适当-该表非常大.

Migration to BLOB isn't appropriate — the table is very large for that.

推荐答案

通常，您不能这样做.特别是如果您的数据库使用UTF-8(当今的默认值)，则您有许多不匹配有效字符的位值，并且在插入和选择时它们将被占位符(通常为¿)替换.

In general you cannot do that. Especially if your database uses UTF-8 (the default nowadays) you have many bit values which do not match a valid character and they would be replaced with a placeholder (typically ¿) while insert and select.

您可以做的是将二进制数据编码为 Base64 字符串.这是在仅支持文本(例如XML文件或SMTP邮件)的接口上传输二进制数据的一种非常常见的方式.

What you can do is to encode your binary data as Base64 string. This is a very common way to transfer binary data at interfaces which supports only text (e.g. XML files or SMTP mails)

使用此功能将二进制数据编码为文本:

Use this function to encode your binary data as text:

FUNCTION EncodeBASE64(InBlob IN BLOB) RETURN CLOB IS

    BlobLen INTEGER := DBMS_LOB.GETLENGTH(InBlob);
    read_offset INTEGER := 1;
    warning INTEGER;

    amount INTEGER := 1440; -- must be a whole multiple of 3
    -- size of a whole multiple of 48 is beneficial to get NEW_LINE after each 64 characters 
    buffer RAW(1440);
    res CLOB := EMPTY_CLOB();

BEGIN

    IF InBlob IS NULL OR NVL(BlobLen, 0) = 0 THEN 
        RETURN NULL;
    ELSIF BlobLen <= 24000 THEN
        RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(InBlob));
    ELSE
        -- UTL_ENCODE.BASE64_ENCODE is limited to 32k, process in chunks if bigger
        LOOP
            EXIT WHEN read_offset >= BlobLen;
            DBMS_LOB.READ(InBlob, amount, read_offset, buffer);
            res := res || UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(buffer));       
            read_offset := read_offset + amount;
        END LOOP;
    END IF;
    RETURN res;

END EncodeBASE64;

此函数可以转换回BLOB

And this function to convert back to BLOB

FUNCTION DecodeBASE64(InBase64Char IN CLOB) RETURN BLOB IS

    res BLOB;
    clob_trim CLOB;

    dest_offset INTEGER := 1;
    src_offset INTEGER := 1;
    read_offset INTEGER := 1;
    ClobLen INTEGER := DBMS_LOB.GETLENGTH(InBase64Char);

    amount INTEGER := 1440; -- must be a whole multiple of 4
    buffer RAW(1440);
    stringBuffer VARCHAR2(1440);
    -- BASE64 characters are always simple ASCII. Thus you get never any Mulit-Byte character and having the same size as 'amount' is sufficient

BEGIN

    IF InBase64Char IS NULL OR NVL(ClobLen, 0) = 0 THEN 
        RETURN NULL;
    ELSIF ClobLen <= 32000 THEN
        RETURN TO_BLOB(UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(InBase64Char)));
    ELSE
        -- Remove all NEW_LINE from base64 string
        DBMS_LOB.CREATETEMPORARY(clob_trim, TRUE);
        LOOP
            EXIT WHEN read_offset > ClobLen;
            stringBuffer := REPLACE(REPLACE(DBMS_LOB.SUBSTR(InBase64Char, amount, read_offset), CHR(13), NULL), CHR(10), NULL);
            DBMS_LOB.WRITEAPPEND(clob_trim, LENGTH(stringBuffer), stringBuffer);
            read_offset := read_offset + amount;
        END LOOP;

        read_offset := 1;
        ClobLen := DBMS_LOB.GETLENGTH(clob_trim);
        DBMS_LOB.CREATETEMPORARY(res, TRUE);
        LOOP
            EXIT WHEN read_offset > ClobLen;
            buffer := UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(clob_trim, amount, read_offset)));
            DBMS_LOB.WRITEAPPEND(res, DBMS_LOB.GETLENGTH(buffer), buffer);
            read_offset := read_offset + amount;
        END LOOP;
        DBMS_LOB.FREETEMPORARY(clob_trim);
    END IF;

    RETURN res;    

END DecodeBASE64;

在互联网上可以找到许多在线Base64解码器/编码器，您可以在其中验证您的程序.

You find many online Base64 Decoder/Encoder at the internet where you can verify your procedure.

这篇关于我可以将二进制字符串存储在CLOB列中吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！