如何在数据类型为xmltype的列上使用ora_hash

问题描述

我想对xmltype数据类型使用ORA_HASH，而解决方法是直接的解决方案?

I would like to use ORA_HASH on xmltype datatype, and workarounds are straight forward solutions ?

我正在使用oracle 11g r2和二进制xml作为xmltype列的存储选项

I am using oracle 11g r2 and binary xml as storage option for xmltype column

用于创建表的查询是

create table samplebinary ( indexid number(19,0) , xmlcolumn xmltype not null) xmltype column xmlcolumn store as binary xml;

推荐答案

您已经知道， ora_hash不接受long或LOB值.您可以传入XML内容的前4k或32k，但是如果您需要确保整个XML文档都没有更改，那将是不够的.正如Ben所提到的，ora_hash最多具有4294967295个存储桶，因此与SHA-1或MD5发生碰撞的可能性更大.如文档所述，ora_hash对于诸如分析数据子集和生成随机样本之类的操作很有用".

As you already know, ora_hash doesn't accept long or LOB values. You could pass in the first 4k or 32k of the XML content, but if you need to make sure that the entire XML document hasn't changed, that won't be sufficient. And as Ben mentioned, ora_hash has a maximum of 4294967295 buckets, so collisions are rather more likely than with SHA-1 or MD5. As the documentation says, ora_hash 'is useful for operations such as analyzing a subset of data and generating a random sample'.

您可以使用 dbms_crypto软件包散列整个XMLType值，例如使用 getClobVal函数，具有包装函数，使使用起来更简单:

You can use the dbms_crypto package to hash the whole XMLType value, as a CLOB extracted with the getClobVal function, with a wrapper function to make it simpler to use:

create or replace function my_hash(xml xmltype) return raw is
begin
  return dbms_crypto.hash(src=>xml.getclobval(), typ=>dbms_crypto.hash_sh1);
end;
/

然后，您可以将XMLType作为值或列作为select的一部分进行传递:

You can then pass in your XMLType, as a value or as a column as part of a select:

select my_hash(xml) from t42;

MY_HASH(XML)                                 
---------------------------------------------
494C4E7688963BCF312B709B33CD1B5CCA7C0289     

这篇关于如何在数据类型为xmltype的列上使用ora_hash的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！