Table Structure
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;
/
I have a table test_payload with an XML length of 250000 character stored as CLOB in column cntnt_payload.
I am trying to do the following
1) Convert the xml clob to Blob using function CLOB_TO_BLOB function (code attached).
2) Encrypt the blob using the DBMS_CRYPTO method using function F_ENCRYPT (code attcehd)
3) Decrypt the blob using the DBMS_CRYPTO method using the function F_DECRYPT (code attached)
4) Converting back the decrypted blod to clob using the function BLOB_TO_CLOB (code attached)
I am upating all the above four steps values onto test_payload tables in their respective columns.
While working with an XML length less than 32000 characters the above code works fine and give me the expected results.
But with XML length of 250000 characters Step 1 works fine (i.e. Converts XML clob to Blob) but the Stpe 2 for Encrypting the blob using the DBMS_CRYPTO
fails. It gives ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 40. Also the subsequent step 3 and 4 does not get executed.
Warning: ranting mode on :-)
*** CAN PEOPLE PLEASE STOP ADDING WHEN-OTHERS HANDLERS TO THEIR CODE ****
because you end up with junk.
Anyway, I took all the handlers out and we get this
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 := '<xml>';
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,'<xml>');
9 for i in 1 .. 660 loop
10 dbms_lob.writeappend(x,50,'<elem>123456asdfghzxcvbnqweasd123qweuytgtwe</elem>');
11 end loop;
12 dbms_lob.writeappend(x,6,'</xml>');
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>
which is doing *exactly* what it should do. Because when we look at 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
you are using the *function* which is *not* designed for blobs.
Use the procedure.