Oracle 较大数据集的DBMS_CRYPTO加密方法的问题

表结构

CREATE TABLE TEST_PAYLOAD
(
  CNTNT_PAYLOAD_OID   NUMBER(20)                NOT NULL,
  CNTNT_PAYLOAD       CLOB,
  BLOB_CNTNT_PAYLOAD  BLOB,
  ENCRYPT_BLOB        BLOB,
  DECRYPT_BLOB        BLOB,
  CLOB_CNT_PAYLOAD    CLOB,
  CLOB_CNT_PAYLOAD1   CLOB
)

CREATE OR REPLACE function CLOB_TO_BLOB (p_clob CLOB) return BLOB
as
l_blob blob;
l_dest_offset integer := 1;
l_source_offset integer := 1;
l_lang_context integer := DBMS_LOB.DEFAULT_LANG_CTX;
l_warning integer := DBMS_LOB.WARN_INCONVERTIBLE_CHAR;
BEGIN

DBMS_LOB.CREATETEMPORARY(l_blob, TRUE);
DBMS_LOB.CONVERTTOBLOB
(
dest_lob =>l_blob,
src_clob =>p_clob,
amount =>DBMS_LOB.LOBMAXSIZE,
dest_offset =>l_dest_offset,
src_offset =>l_source_offset,
blob_csid =>DBMS_LOB.DEFAULT_CSID,
lang_context=>l_lang_context,
warning =>l_warning
);
return l_blob;
END;
/

CREATE OR REPLACE FUNCTION F_Encrypt( P_Encrypt_Data IN BLOB) RETURN BLOB
IS
G_CHARACTER_SET VARCHAR2(10) := 'AL32UTF8';
G_STRING VARCHAR2(32) := 12345678901234567890123456789012;
G_KEY RAW(250) := utl_i18n.string_to_raw
( data => G_STRING,
dst_charset => G_CHARACTER_SET);

G_ENCRYPTION_TYPE PLS_INTEGER := dbms_crypto.encrypt_aes256
+ dbms_crypto.chain_cbc
+ dbms_crypto.pad_pkcs5;


l_Encrypt_Data BLOB;
l_encrypted BLOB;
BEGIN

l_encrypted := dbms_crypto.encrypt
( src => P_Encrypt_Data,
typ => G_ENCRYPTION_TYPE,
key => G_KEY );

RETURN l_encrypted;
EXCEPTION WHEN OTHERS THEN
RETURN l_encrypted;
END F_Encrypt;
/


CREATE OR REPLACE FUNCTION F_Decrypt( P_Decrypt_Data IN BLOB) RETURN BLOB
IS
G_CHARACTER_SET VARCHAR2(10) := 'AL32UTF8';
G_STRING VARCHAR2(32) := 12345678901234567890123456789012;
G_KEY RAW(250) := utl_i18n.string_to_raw
( data => G_STRING,
dst_charset => G_CHARACTER_SET);

G_DecryptION_TYPE PLS_INTEGER := dbms_crypto.encrypt_aes256
+ dbms_crypto.chain_cbc
+ dbms_crypto.pad_pkcs5;


l_Decrypt_Data BLOB;
l_Decrypted BLOB;
BEGIN

l_Decrypted := dbms_crypto.Decrypt
( src => P_Decrypt_Data,
typ => G_DecryptION_TYPE,
key => G_KEY );

RETURN l_Decrypted;
EXCEPTION WHEN OTHERS THEN
RETURN l_Decrypted;
END F_Decrypt;
/


CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB)
RETURN CLOB
AS
v_clob CLOB;
v_varchar VARCHAR2(32767);
v_start PLS_INTEGER := 1;
v_buffer PLS_INTEGER := 32767;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);

FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
LOOP

v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));

DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
v_start := v_start + v_buffer;
END LOOP;
RETURN v_clob;
END blob_to_clob;
/


DECLARE
v_blob_cntnt_payload BLOB;
v_encrypt_blob BLOB;
v_decrypt_blob BLOB;
v_clob_cnt_payload CLOB;
v_clob_cnt_payload1 CLOB;
BEGIN

--Clob to Blob
SELECT CLOB_TO_BLOB(cntnt_payload)
into v_blob_cntnt_payload
from test_payload
where cntnt_payload_oid = 21811;

--Encrypt Blob
v_encrypt_blob:=F_ENCRYPT(v_blob_cntnt_payload);

--Decrypt Blob
SELECT F_DECRYPT(v_encrypt_blob)
into v_decrypt_blob
from dual;

--Blob to Clob
SELECT BLOB_TO_CLOB(v_decrypt_blob)
into v_clob_cnt_payload
from dual;


update test_payload
set blob_cntnt_payload = v_blob_cntnt_payload,
encrypt_blob = v_encrypt_blob,
decrypt_blob = v_decrypt_blob,
clob_cnt_payload = v_clob_cnt_payload,
clob_cnt_payload1 = v_clob_cnt_payload1
where cntnt_payload_oid = 21811;

commit;
EXCEPTION
WHEN OTHERS then
raise;
END;
/

我有一个表test_payload与250000字符的XML长度存储为cntnt_payload列中的CLOB。

我正在尝试执行以下操作

1) 使用函数CLOB_TO_BLOB函数 (附加代码) 将xml clob转换为Blob。
2) 使用DBMS_CRYPTO方法使用函数F_ENCRYPT (代码attcehd) 加密blob
3) 使用DBMS_CRYPTO方法使用函数F_DECRYPT (附加代码) 解密blob
4) 使用函数BLOB_TO_CLOB (附加代码) 将解密的blod转换回clob

我将上述所有四个步骤值更新到各自列中的test_payload表中。

使用小于32000个字符的XML长度时，上面的代码可以正常工作，并给我预期的结果。

但是使用250000个字符的XML长度，步骤1可以正常工作 (即将XML clob转换为Blob)，但是使用DBMS_CRYPTO加密blob的Stpe 2
失败。它给出ORA-06502: PL/SQL: 数字或值错误ORA-06512: 在第40行。随后的步骤3和4也没有被执行。

警告: 咆哮模式开启 :-)

*** 人们能否停止在他们的代码中添加当-其他处理程序 ****

因为你最终会得到垃圾。

不管怎样，我把所有的处理人员都拿出来了，我们得到了这个

SQL> @drop TEST_PAYLOAD

Y1                      Y2
----------------------- -------------------------
TABLE                   cascade constraints purge

1 row selected.


Table dropped.

SQL>
SQL> CREATE TABLE TEST_PAYLOAD
  2  (
  3    CNTNT_PAYLOAD_OID   NUMBER(20)                NOT NULL,
  4    CNTNT_PAYLOAD       CLOB,
  5    BLOB_CNTNT_PAYLOAD  BLOB,
  6    ENCRYPT_BLOB        BLOB,
  7    DECRYPT_BLOB        BLOB,
  8    CLOB_CNT_PAYLOAD    CLOB,
  9    CLOB_CNT_PAYLOAD1   CLOB
 10  );

Table created.

SQL>
SQL> declare
  2    x clob := '';
  3  begin
  4    insert into TEST_PAYLOAD ( CNTNT_PAYLOAD_OID,CNTNT_PAYLOAD)
  5    values (21811,empty_clob())
  6    returning CNTNT_PAYLOAD into x;
  7
  8    dbms_lob.writeappend(x,5,'');
  9    for i in 1 .. 660 loop
 10      dbms_lob.writeappend(x,50,'123456asdfghzxcvbnqweasd123qweuytgtwe');
 11    end loop;
 12    dbms_lob.writeappend(x,6,'');
 13    commit;
 14  end;
 15  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> CREATE OR REPLACE function CLOB_TO_BLOB (p_clob CLOB) return BLOB
  2  as
  3  l_blob blob;
  4  l_dest_offset integer := 1;
  5  l_source_offset integer := 1;
  6  l_lang_context integer := DBMS_LOB.DEFAULT_LANG_CTX;
  7  l_warning integer := DBMS_LOB.WARN_INCONVERTIBLE_CHAR;
  8  BEGIN
  9
 10  DBMS_LOB.CREATETEMPORARY(l_blob, TRUE);
 11  DBMS_LOB.CONVERTTOBLOB
 12  (
 13  dest_lob =>l_blob,
 14  src_clob =>p_clob,
 15  amount =>DBMS_LOB.LOBMAXSIZE,
 16  dest_offset =>l_dest_offset,
 17  src_offset =>l_source_offset,
 18  blob_csid =>DBMS_LOB.DEFAULT_CSID,
 19  lang_context=>l_lang_context,
 20  warning =>l_warning
 21  );
 22  return l_blob;
 23  END;
 24  /

Function created.

SQL>
SQL> CREATE OR REPLACE FUNCTION F_Encrypt( P_Encrypt_Data IN BLOB) RETURN BLOB
  2  IS
  3  G_CHARACTER_SET VARCHAR2(10) := 'AL32UTF8';
  4  G_STRING VARCHAR2(32) := 12345678901234567890123456789012;
  5  G_KEY RAW(250) := utl_i18n.string_to_raw
  6  ( data => G_STRING,
  7  dst_charset => G_CHARACTER_SET);
  8
  9  G_ENCRYPTION_TYPE PLS_INTEGER := dbms_crypto.encrypt_aes256
 10  + dbms_crypto.chain_cbc
 11  + dbms_crypto.pad_pkcs5;
 12
 13
 14  l_Encrypt_Data BLOB;
 15  l_encrypted BLOB;
 16  BEGIN
 17
 18  l_encrypted := dbms_crypto.encrypt
 19  ( src => P_Encrypt_Data,
 20  typ => G_ENCRYPTION_TYPE,
 21  key => G_KEY );
 22
 23  RETURN l_encrypted;
 24
 25  END F_Encrypt;
 26  /

Function created.

SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION F_Decrypt( P_Decrypt_Data IN BLOB) RETURN BLOB
  2  IS
  3  G_CHARACTER_SET VARCHAR2(10) := 'AL32UTF8';
  4  G_STRING VARCHAR2(32) := 12345678901234567890123456789012;
  5  G_KEY RAW(250) := utl_i18n.string_to_raw
  6  ( data => G_STRING,
  7  dst_charset => G_CHARACTER_SET);
  8
  9  G_DecryptION_TYPE PLS_INTEGER := dbms_crypto.encrypt_aes256
 10  + dbms_crypto.chain_cbc
 11  + dbms_crypto.pad_pkcs5;
 12
 13
 14  l_Decrypt_Data BLOB;
 15  l_Decrypted BLOB;
 16  BEGIN
 17
 18  l_Decrypted := dbms_crypto.Decrypt
 19  ( src => P_Decrypt_Data,
 20  typ => G_DecryptION_TYPE,
 21  key => G_KEY );
 22
 23  RETURN l_Decrypted;
 24
 25  END F_Decrypt;
 26  /

Function created.

SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB)
  2  RETURN CLOB
  3  AS
  4  v_clob CLOB;
  5  v_varchar VARCHAR2(32767);
  6  v_start PLS_INTEGER := 1;
  7  v_buffer PLS_INTEGER := 1000;
  8  BEGIN
  9  DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
 10
 11  FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
 12  LOOP
 13  v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));
 14
 15  DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
 16  v_start := v_start + v_buffer;
 17  END LOOP;
 18  RETURN v_clob;
 19  END blob_to_clob;
 20  /

Function created.

SQL>
SQL>
SQL> DECLARE
  2  v_blob_cntnt_payload BLOB;
  3  v_encrypt_blob BLOB;
  4  v_decrypt_blob BLOB;
  5  v_clob_cnt_payload CLOB;
  6  v_clob_cnt_payload1 CLOB;
  7  BEGIN
  8
  9  --Clob to Blob
 10  SELECT CLOB_TO_BLOB(cntnt_payload)
 11  into v_blob_cntnt_payload
 12  from test_payload
 13  where cntnt_payload_oid = 21811;
 14
 15  dbms_output.put_line('len1='||DBMS_LOB.GETLENGTH(v_blob_cntnt_payload));
 16
 17  --Encrypt Blob
 18  v_encrypt_blob:=F_ENCRYPT(v_blob_cntnt_payload);
 19
 20  dbms_output.put_line('len2='||DBMS_LOB.GETLENGTH(v_encrypt_blob));
 21
 22  --Decrypt Blob
 23  SELECT F_DECRYPT(v_encrypt_blob)
 24  into v_decrypt_blob
 25  from dual;
 26
 27  dbms_output.put_line('len3='||DBMS_LOB.GETLENGTH(v_decrypt_blob));
 28
 29  --Blob to Clob
 30  SELECT BLOB_TO_CLOB(v_decrypt_blob)
 31  into v_clob_cnt_payload
 32  from dual;
 33
 34
 35  update test_payload
 36  set blob_cntnt_payload = v_blob_cntnt_payload,
 37  encrypt_blob = v_encrypt_blob,
 38  decrypt_blob = v_decrypt_blob,
 39  clob_cnt_payload = v_clob_cnt_payload,
 40  clob_cnt_payload1 = v_clob_cnt_payload1
 41  where cntnt_payload_oid = 21811;
 42
 43  commit;
 44
 45  END;
 46  /
len1=33011
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "MCDONAC.F_ENCRYPT", line 18
ORA-06512: at line 18


SQL>

它正在做它应该做的事情。因为当我们看DBMS_CRYPTO时

FUNCTION ENCRYPT RETURNS RAW
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SRC                            RAW                     IN
 TYP                            BINARY_INTEGER          IN
 KEY                            RAW                     IN
 IV                             RAW                     IN     DEFAULT
PROCEDURE ENCRYPT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DST                            BLOB                    IN/OUT
 SRC                            BLOB                    IN
 TYP                            BINARY_INTEGER          IN
 KEY                            RAW                     IN
 IV                             RAW                     IN     DEFAULT

您正在使用 * 函数 *，它不是 * 为blob设计的。

使用该过程。