解码大base64编码的blob(Decoding big base64 encoded blobs)
我有一个大约3.5mb的xml文件。 我正在将它包围并传播到我需要解码的另一个数据库。 我在迭代中对它进行解码，因为它太大而无法一次解码，但是在某些迭代中它无法解码，我得到了一些细节。 我相信这是因为一些符号可以是1个字节而其他符号可以是2个字节，有时子字符串会将2个字节的一个符号切成两半，并且迭代会出现在giberish中。 我认为我可以尝试将每个子字符串转换为clob，因为它在转换失败时会出现警告，如果警告出现，则会增加子字符串中的数量，我还没有成功解码这个办法。 这有什么解决方法吗？
UPDATE
成功解码，并进行警告检查。 您需要做的就是尝试将substring转换为clob，并使用dbms_lob.convertblobtoclob检查warning != 0 ，如果是这样，请将偏移dbms_lob.convertblobtoclob 1并转到下一次迭代，而不将子字符串写入blob。 然而，这是非常记忆的costy检查，因为它需要创建blob并将该blob转换为clob。 有没有更简单的解决方法，也许我错过了一些非常明显的东西？
UPDATE
我有一个xml文件，其中包含以base64编码的付款xml，以及有关该xml的其他数据。 FE
<envelope> <file_name>a.xml</file_name> <...><...> <data>BASE64 ENCODED XML FILE</data> </envelope>完整的脚本，几个例子。 早些时候我已经提到了警告检查的解决方法，但在这个例子中似乎不起作用，当我更好地考虑它时，它不应该。 无论如何，这里的脚本：
declare l_clob clob := empty_clob(); function convert_clob_to_blob( p_clob in clob) return blob is l_dest_offsset number := 1; l_src_offsset number := 1; l_lang_context number := dbms_lob.default_lang_ctx; l_warning number; l_result blob; begin dbms_lob.createtemporary(l_result, false); dbms_lob.convertToBlob( dest_lob => l_result, src_clob => p_clob, amount => dbms_lob.lobmaxsize, dest_offset => l_dest_offsset, src_offset => l_src_offsset, blob_csid => dbms_lob.default_csid, lang_context => l_lang_context, warning => l_warning); if l_warning != 0 then raise_application_error(-20001, 'sd' || '.convert_blob_to_clob ' || l_warning); end if; return l_result; end; function gen_rand_xml return clob is l_xml xmltype := xmltype('<envelope><nullnode></nullnode></envelope>'); begin for i in 1..50 loop SELECT insertXMLafter( l_xml, '/envelope/nullnode', XMLType('<node>' || i || '</node>')) INTO l_xml FROM dual; end loop; return l_xml.getClobVal(); end; function to_base64( p_clob in clob) return clob is l_length integer; l_offset integer := 1; l_amt binary_integer := 600; l_buffer varchar2(1800); l_result clob := empty_clob(); l_temp_blob blob; begin dbms_lob.createtemporary(l_temp_blob, false); l_temp_blob := convert_clob_to_blob(p_clob); l_length := dbms_lob.getlength(l_temp_blob); while l_offset < l_length loop l_result := l_result || utl_raw.cast_to_varchar2(utl_encode.base64_encode(dbms_lob.substr(l_temp_blob, l_amt, l_offset))); l_offset := l_offset + l_amt; end loop; return l_result; end; function from_base64( p_clob in clob) return clob is l_length integer := dbms_lob.getLength(p_clob); l_offset integer := 1; l_amt binary_integer := 800; l_buffer varchar2(3200); l_result clob := empty_clob(); begin while l_offset <= l_length loop l_buffer := replace(replace(dbms_lob.substr(p_clob, l_amt, l_offset), chr(10), null), chr(13), null); l_offset := l_offset + l_amt; while l_offset <= l_length and mod(dbms_lob.getLength(l_buffer), 4) > 0 loop l_buffer := l_buffer || replace(replace(dbms_lob.substr(p_clob, 1, l_offset), chr(10), null), chr(13), null); l_offset := l_offset + 1; end loop; l_result := l_result || utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(l_buffer))); end loop; return l_result; end; procedure print_clob( p_clob in clob ) as l_offset number default 1; begin loop exit when l_offset > dbms_lob.getlength(p_clob); dbms_output.put_line( dbms_lob.substr( p_clob, 4000, l_offset ) ); l_offset := l_offset + 4000; end loop; end; begin l_clob := gen_rand_xml; print_clob(from_base64(to_base64(l_clob))); end; /I have an xml file which is about 3.5mb in size. I'm encdoing it and propagating to another database where i need to decode it. I'm decoding it in iterations since it's too big to decode in one go, however in some iterations it fails to decode, i get some giberish. I believe it's because some symbols can be 1 byte and others 2 byte's, and sometimes substring cuts one symbol of 2 byte's in half and that iteration would come up in giberish. I've figured that i can try to convert every substring to clob, since it has warnings when it fails to convert, characters and if warning comes up to, increase amount in substring by some number, byt i've yet to succeed decoding this way. Is there any workaround for this?
UPDATE
Succeeded in decoding, with warnings checks. All you need to do is try to convert substring to clob with dbms_lob.convertblobtoclob check for warning != 0 and if so reduce offset by 1 and go to next iteration, without writing substring to your blob. However this is very memmory costy check, because it requires to create blob and to transform that blob to a clob. Is there any simplier workaround to this, maybe i'am missing something very obvious?
UPDATE
I've an xml file which contains payment xml encoded in base64, and other data about that xml. f.e.
<envelope> <file_name>a.xml</file_name> <...><...> <data>BASE64 ENCODED XML FILE</data> </envelope>Complete script with few examples. Earlier i've mentioned workaround with warning checking, but in this example it seems to not work, and when i better think about it, it shouldn't. Anyway, here's script:
declare l_clob clob := empty_clob(); function convert_clob_to_blob( p_clob in clob) return blob is l_dest_offsset number := 1; l_src_offsset number := 1; l_lang_context number := dbms_lob.default_lang_ctx; l_warning number; l_result blob; begin dbms_lob.createtemporary(l_result, false); dbms_lob.convertToBlob( dest_lob => l_result, src_clob => p_clob, amount => dbms_lob.lobmaxsize, dest_offset => l_dest_offsset, src_offset => l_src_offsset, blob_csid => dbms_lob.default_csid, lang_context => l_lang_context, warning => l_warning); if l_warning != 0 then raise_application_error(-20001, 'sd' || '.convert_blob_to_clob ' || l_warning); end if; return l_result; end; function gen_rand_xml return clob is l_xml xmltype := xmltype('<envelope><nullnode></nullnode></envelope>'); begin for i in 1..50 loop SELECT insertXMLafter( l_xml, '/envelope/nullnode', XMLType('<node>' || i || '</node>')) INTO l_xml FROM dual; end loop; return l_xml.getClobVal(); end; function to_base64( p_clob in clob) return clob is l_length integer; l_offset integer := 1; l_amt binary_integer := 600; l_buffer varchar2(1800); l_result clob := empty_clob(); l_temp_blob blob; begin dbms_lob.createtemporary(l_temp_blob, false); l_temp_blob := convert_clob_to_blob(p_clob); l_length := dbms_lob.getlength(l_temp_blob); while l_offset < l_length loop l_result := l_result || utl_raw.cast_to_varchar2(utl_encode.base64_encode(dbms_lob.substr(l_temp_blob, l_amt, l_offset))); l_offset := l_offset + l_amt; end loop; return l_result; end; function from_base64( p_clob in clob) return clob is l_length integer := dbms_lob.getLength(p_clob); l_offset integer := 1; l_amt binary_integer := 800; l_buffer varchar2(3200); l_result clob := empty_clob(); begin while l_offset <= l_length loop l_buffer := replace(replace(dbms_lob.substr(p_clob, l_amt, l_offset), chr(10), null), chr(13), null); l_offset := l_offset + l_amt; while l_offset <= l_length and mod(dbms_lob.getLength(l_buffer), 4) > 0 loop l_buffer := l_buffer || replace(replace(dbms_lob.substr(p_clob, 1, l_offset), chr(10), null), chr(13), null); l_offset := l_offset + 1; end loop; l_result := l_result || utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(l_buffer))); end loop; return l_result; end; procedure print_clob( p_clob in clob ) as l_offset number default 1; begin loop exit when l_offset > dbms_lob.getlength(p_clob); dbms_output.put_line( dbms_lob.substr( p_clob, 4000, l_offset ) ); l_offset := l_offset + 4000; end loop; end; begin l_clob := gen_rand_xml; print_clob(from_base64(to_base64(l_clob))); end; /最满意答案
我仍然不明白你的意思，大多数我不明白你为什么要从BLOB转换为CLOB。
无论如何，我的应用程序中有类似的情况，在XML文件中使用BASE64编码的XML。
对于编码和解码，我使用这些功能，也许它可以帮助你的情况。
CREATE OR REPLACE FUNCTION DecodeBASE64(InBase64Char IN OUT NOCOPY CLOB) RETURN CLOB IS blob_loc BLOB; clob_trim CLOB; res CLOB; lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX; dest_offset INTEGER := 1; src_offset INTEGER := 1; read_offset INTEGER := 1; warning INTEGER; ClobLen INTEGER; amount INTEGER := 1440; -- must be a whole multiple of 4 buffer RAW(1440); stringBuffer VARCHAR2(1440); BEGIN -- Remove all NEW_LINE from base64 string ClobLen := DBMS_LOB.GETLENGTH(InBase64Char); 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(blob_loc, 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(blob_loc, DBMS_LOB.GETLENGTH(buffer), buffer); read_offset := read_offset + amount; END LOOP; DBMS_LOB.CREATETEMPORARY(res, TRUE); DBMS_LOB.CONVERTTOCLOB(res, blob_loc, DBMS_LOB.LOBMAXSIZE, dest_offset, src_offset, DBMS_LOB.DEFAULT_CSID, lang_context, warning); DBMS_LOB.FREETEMPORARY(blob_loc); DBMS_LOB.FREETEMPORARY(clob_trim); RETURN res; END DecodeBASE64; CREATE OR REPLACE FUNCTION EncodeBASE64(InClearChar IN OUT NOCOPY CLOB) RETURN CLOB IS dest_lob BLOB; lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX; dest_offset INTEGER := 1; src_offset INTEGER := 1; read_offset INTEGER := 1; warning INTEGER; amount INTEGER := 1440; -- must be a whole multiple of 3 buffer RAW(1440); res CLOB := EMPTY_CLOB(); BEGIN IF DBMS_LOB.GETLENGTH(InClearChar) IS NULL THEN RETURN NULL; END IF; DBMS_LOB.CREATETEMPORARY(dest_lob, TRUE); DBMS_LOB.CONVERTTOBLOB(dest_lob, InClearChar, DBMS_LOB.LOBMAXSIZE, dest_offset, src_offset, DBMS_LOB.DEFAULT_CSID, lang_context, warning); LOOP EXIT WHEN read_offset >= dest_offset; DBMS_LOB.READ(dest_lob, amount, read_offset, buffer); res := res || UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(buffer)); read_offset := read_offset + amount; END LOOP; DBMS_LOB.FREETEMPORARY(dest_lob); RETURN res; END EncodeBASE64;你可以像这样使用它
DECLARE str VARCHAR2(1000) := '<envelope><file_name>a.xml</file_name><data>some text</data></envelope>'; base64 VARCHAR2(1000); BEGIN base64 := EncodeBASE64(str); DBMS_OUTPUT.PUT_LINE( base64 ); str := DecodeBASE64(base64); DBMS_OUTPUT.PUT_LINE( str ); END;输出：
PGVudmVsb3BlPjxmaWxlX25hbWU+YS54bWw8L2ZpbGVfbmFtZT48ZGF0YT5zb21l IHRleHQ8L2RhdGE+PC9lbnZlbG9wZT4= <envelope><file_name>a.xml</file_name><data>some text</data></envelope>也许这个例子更接近你的用例：
DECLARE payment CLOB := '<payment><amout>50 Cent</amout><recipient>Wernfried Domscheit</recipient></payment>'; envelope XMLTYPE; base64 CLOB; BEGIN SELECT XMLELEMENT("envelope", XMLELEMENT("file_name", 'a.xml'), XMLELEMENT("data", EncodeBASE64(payment)) ) INTO envelope FROM dual; DBMS_OUTPUT.PUT_LINE( envelope.getclobval() || CHR(13)); SELECT RETURN_BASE64 INTO base64 FROM XMLTABLE('envelope/data' PASSING envelope COLUMNS RETURN_BASE64 CLOB PATH '/'); DBMS_OUTPUT.PUT_LINE( base64 || CHR(13) ); payment := DecodeBASE64(base64); DBMS_OUTPUT.PUT_LINE( payment ); END;输出：
<envelope><file_name>a.xml</file_name><data>PHBheW1lbnQ+PGFtb3V0PjUwIENlbnQ8L2Ftb3V0PjxyZWNpcGllbnQ+V2VybmZy aWVkIERvbXNjaGVpdDwvcmVjaXBpZW50PjwvcGF5bWVudD4=</data></envelope> PHBheW1lbnQ+PGFtb3V0PjUwIENlbnQ8L2Ftb3V0PjxyZWNpcGllbnQ+V2VybmZy aWVkIERvbXNjaGVpdDwvcmVjaXBpZW50PjwvcGF5bWVudD4= <payment><amout>50 Cent</amout><recipient>Wernfried Domscheit</recipient></payment>为了不浪费内存，在使用LOB时应始终使用IN OUT NOCOPY子句。 这样就不会复制LOB用于过程调用，只需传递指针即可。
I still don't get your point and most I don't understand why do you convert from BLOB to CLOB.
Anyway, I have a similar situation in my application, with BASE64 encoded XML inside a XML file.
For encoding and decoding I use these functions, maybe it helps in your situation.
CREATE OR REPLACE FUNCTION DecodeBASE64(InBase64Char IN OUT NOCOPY CLOB) RETURN CLOB IS blob_loc BLOB; clob_trim CLOB; res CLOB; lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX; dest_offset INTEGER := 1; src_offset INTEGER := 1; read_offset INTEGER := 1; warning INTEGER; ClobLen INTEGER; amount INTEGER := 1440; -- must be a whole multiple of 4 buffer RAW(1440); stringBuffer VARCHAR2(1440); BEGIN -- Remove all NEW_LINE from base64 string ClobLen := DBMS_LOB.GETLENGTH(InBase64Char); 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(blob_loc, 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(blob_loc, DBMS_LOB.GETLENGTH(buffer), buffer); read_offset := read_offset + amount; END LOOP; DBMS_LOB.CREATETEMPORARY(res, TRUE); DBMS_LOB.CONVERTTOCLOB(res, blob_loc, DBMS_LOB.LOBMAXSIZE, dest_offset, src_offset, DBMS_LOB.DEFAULT_CSID, lang_context, warning); DBMS_LOB.FREETEMPORARY(blob_loc); DBMS_LOB.FREETEMPORARY(clob_trim); RETURN res; END DecodeBASE64; CREATE OR REPLACE FUNCTION EncodeBASE64(InClearChar IN OUT NOCOPY CLOB) RETURN CLOB IS dest_lob BLOB; lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX; dest_offset INTEGER := 1; src_offset INTEGER := 1; read_offset INTEGER := 1; warning INTEGER; amount INTEGER := 1440; -- must be a whole multiple of 3 buffer RAW(1440); res CLOB := EMPTY_CLOB(); BEGIN IF DBMS_LOB.GETLENGTH(InClearChar) IS NULL THEN RETURN NULL; END IF; DBMS_LOB.CREATETEMPORARY(dest_lob, TRUE); DBMS_LOB.CONVERTTOBLOB(dest_lob, InClearChar, DBMS_LOB.LOBMAXSIZE, dest_offset, src_offset, DBMS_LOB.DEFAULT_CSID, lang_context, warning); LOOP EXIT WHEN read_offset >= dest_offset; DBMS_LOB.READ(dest_lob, amount, read_offset, buffer); res := res || UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(buffer)); read_offset := read_offset + amount; END LOOP; DBMS_LOB.FREETEMPORARY(dest_lob); RETURN res; END EncodeBASE64;You can use it like this
DECLARE str VARCHAR2(1000) := '<envelope><file_name>a.xml</file_name><data>some text</data></envelope>'; base64 VARCHAR2(1000); BEGIN base64 := EncodeBASE64(str); DBMS_OUTPUT.PUT_LINE( base64 ); str := DecodeBASE64(base64); DBMS_OUTPUT.PUT_LINE( str ); END;Output:
PGVudmVsb3BlPjxmaWxlX25hbWU+YS54bWw8L2ZpbGVfbmFtZT48ZGF0YT5zb21l IHRleHQ8L2RhdGE+PC9lbnZlbG9wZT4= <envelope><file_name>a.xml</file_name><data>some text</data></envelope>Perhaps this example is closer to your use case:
DECLARE payment CLOB := '<payment><amout>50 Cent</amout><recipient>Wernfried Domscheit</recipient></payment>'; envelope XMLTYPE; base64 CLOB; BEGIN SELECT XMLELEMENT("envelope", XMLELEMENT("file_name", 'a.xml'), XMLELEMENT("data", EncodeBASE64(payment)) ) INTO envelope FROM dual; DBMS_OUTPUT.PUT_LINE( envelope.getclobval() || CHR(13)); SELECT RETURN_BASE64 INTO base64 FROM XMLTABLE('envelope/data' PASSING envelope COLUMNS RETURN_BASE64 CLOB PATH '/'); DBMS_OUTPUT.PUT_LINE( base64 || CHR(13) ); payment := DecodeBASE64(base64); DBMS_OUTPUT.PUT_LINE( payment ); END;Output:
<envelope><file_name>a.xml</file_name><data>PHBheW1lbnQ+PGFtb3V0PjUwIENlbnQ8L2Ftb3V0PjxyZWNpcGllbnQ+V2VybmZy aWVkIERvbXNjaGVpdDwvcmVjaXBpZW50PjwvcGF5bWVudD4=</data></envelope> PHBheW1lbnQ+PGFtb3V0PjUwIENlbnQ8L2Ftb3V0PjxyZWNpcGllbnQ+V2VybmZy aWVkIERvbXNjaGVpdDwvcmVjaXBpZW50PjwvcGF5bWVudD4= <payment><amout>50 Cent</amout><recipient>Wernfried Domscheit</recipient></payment>In order not to waste memory you should always use IN OUT NOCOPY clause when you work with LOB's. By this the LOB is not copied for procedure call, you just pass a pointer.
更多推荐
发布评论