Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

oracle - How to use ora_hash on a column of datatype xmltype

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

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

Query which I used for creation of the table is

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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'.

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;
/

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     

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...