如何在数据类型为 xmltype 的列上使用 ora_hash [英] How to use ora_hash on a column of datatype xmltype

问题描述

我想在 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 列 xmlcolumn 存储为二进制 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 作为值或作为选择的一部分作为列传递:

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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！