在ORA-06502之后如何在Oracle 11g PL/SQL中将VARCHAR2转换为BLOB [英] How to convert VARCHAR2 to BLOB inside Oracle 11g PL/SQL after ORA-06502
问题描述
我有一个函数,可以计算大字符串的哈希值!首先,我编写了一个游标T1_CUT,它可以包含SELECT语句的可变计数,例如:
I have a function, that calculates hash value of big string! First of all I wrote a cursor T1_CUT, which can include variable count of SELECT statements, something like:
SELECT T1.COL1||T1.COL2||...||T1.COLn FROM T1 WHERE id=documentid
SELECT T2.COL1||T2.COL2||...||T2.COLn FROM T2 WHERE id=documentid
...
SELECT Tn.COL1||Tn.COL2||...||Tn.COLn FROM Tn WHERE id=documentid
每个SELECT可以包含一个或多个行.因此,我将每个SELECT中的行中的所有值与一个大字符串V_RESULT中的所有SELECT值并置为VARCHAR2(32767).之后,我使用MD5获得此大字符串的哈希值(S_HASH_RESULT).它可以正常工作约8个月,但几天前我收到了ORA-06502(不足为奇).这意味着,我的最后一个大字符串包含超过32K个符号(我们使用1个字节的字符集-CL8MSWIN1251).
Each SELECT can contain one or more rows. So, I concat ALL values in rows in each SELECT and ALL SELECTs values in one big string V_RESULT with type VARCHAR2(32767). After that I get hash value (S_HASH_RESULT) of this big string using MD5. It works fine about 8 monthes, but few days ago I've got ORA-06502 (no surprise). It means, that my final big string have more than 32K symbols (we use 1byte character set - CL8MSWIN1251).
朋友,我该如何将使用BLOB数据类型的函数重写为V_RESULT变量,而不是VARCHAR2(32767).
Friends, how can I rewrite my function for using BLOB data type to V_RESULT variable, not VARCHAR2(32767).
下面是我的函数文本:
FUNCTION CALC_HASH (P_PARTAB_ID IN NUMBER, P_DOC_ID IN NUMBER)
RETURN VARCHAR2
IS
S_HASH_RESULT VARCHAR2(1000);
V_RESULT VARCHAR2(32767);
CURSOR T1_CUT IS
...
/*BIG COMPLAIN SELECT*/
...
T1 T1_CUT%ROWTYPE;
TYPE VALUES_T IS TABLE OF VARCHAR2(32767);
L_VALUES VALUES_T;
BEGIN
OPEN T1_CUT;
FETCH T1_CUT INTO T1;
WHILE T1_CUT%FOUND
LOOP
EXECUTE IMMEDIATE
T1.TEXT
BULK COLLECT INTO L_VALUES;
FOR INDX IN 1 .. L_VALUES.COUNT
LOOP
V_RESULT := V_RESULT || '' ||TO_CHAR(L_VALUES (INDX));
END LOOP;
FETCH T1_CUT INTO T1;
END LOOP;
CLOSE T1_CUT;
S_HASH_RESULT := DBMS_OBFUSCATION_TOOLKIT.MD5(input_string=>V_RESULT);
RETURN S_HASH_RESULT;
END CALC_HASH;
提前谢谢!
推荐答案
有一个名为utl_raw.cast_to_raw(vc)
的函数,该函数将varchar2
转换为BLOB
值.
there is a function called utl_raw.cast_to_raw(vc)
which transforms a varchar2
into a BLOB
value.
但是,我建议使用CLOB
来存储字符串值. BLOB
完全没有字符语义,即NLS_LANG
设置被忽略.
However, I recommend to use CLOB
to store string values. BLOB
has no character semantics at all, i.e., NLS_LANG
settings are ignored.
如果要将VARCHAR2
转换为CLOB
,只需使用TO_CLOB
if you want to transform VARCHAR2
to CLOB
, simply use TO_CLOB
这篇关于在ORA-06502之后如何在Oracle 11g PL/SQL中将VARCHAR2转换为BLOB的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!