最近一段一直被大字段困扰,想了很多办法。最终在朋友的指引下使用MD5。
( p_str in varchar2)
RETURN varchar2 IS
BEGIN
RETURN Utl_Raw.Cast_To_Raw(DBMS_OBFUSCATION_TOOLKIT.MD5(input_string => Upper(P_Str)));
END;
但是在使用过程中,还是会出现各种问题,clob类型的有时候是null,有时候又是empty。最严重的问题是超过长度,相当于还是不能处理clob字段。
clob为empty时会报:ORA-28231: no data passed to obfuscation toolkit
查看原因:Cause: A NULL value was passed to a function or procedure.
Action: Make sure that the data passed is not empty.
处理完这个问题,又来一个新问题:ORA-22835:Buffer too small for CLOB to CHAR or BLOB to RAW conversion(actual:10004,maximum:4000)
转了一圈还是回归到原点,但至少有了一个解决问题的方向,最后在网上找到一个可以解决CLOB问题的自定义java source。总算解决了难题,虽然代码不是很懂,但至少能得到想要的结果,也算是自己的一大进步。
摘抄源码如下(转载):
------------Java source------------------------
create or replace and compile java source named md5 as
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Clob;
import java.sql.Blob;
public class MD5 {
private static final byte [] hexDigit = {
'0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F'
};
/** Converts a byte array to a hex string
* Returns an empty string if the byte array is null
*/
public static final String toHexString(byte [] bytes) {
if (bytes == null) return new String("");
StringBuffer buf = new StringBuffer(bytes.length * 2);
for (int i = 0; i < bytes.length; i++) {
buf.append((char) hexDigit[((bytes[i] >>> 4) & 0x0F)]);
buf.append((char) hexDigit[(bytes[i] & 0x0F)]);
}
return buf.toString();
}
// Convert Hex String to Byte Array
public static final byte[] byteArrayFromHexString(String str) {
byte[] bytes = new byte[str.length() / 2];
for (int i = 0; i < bytes.length; i++)
{
bytes[i] = (byte) Integer.parseInt(str.substring(2 * i, 2 * i + 2), 16);
}
return bytes;
}
public static String getMD5HashFromClob(Clob inhalt) throws Exception{
MessageDigest algorithm;
StringBuffer hexString;
String s = null;
String salida = null;
int i;
byte[] digest;
String tepFordigest = inhalt.getSubString(1L, (int)inhalt.length());
try {
algorithm = MessageDigest.getInstance("MD5");
algorithm.reset();
algorithm.update(tepFordigest.getBytes("UTF-8"));
digest = algorithm.digest();
s = toHexString(digest);
} catch (java.security.NoSuchAlgorithmException nsae) {
s = "No es posible cifrar MD5";
}
return s;
}
}
/
CREATE OR REPLACE FUNCTION get_md5_CLOB(inhalt CLOB) RETURN VARCHAR2 DETERMINISTIC
AS LANGUAGE JAVA
name 'MD5.getMD5HashFromClob(java.sql.Clob) return java.lang.String';
/