在Oracle中连续进行sha1-hash [英] Making a sha1-hash of a row in Oracle

问题描述

我在用Oracle数据库中的select进行行的sha1-hash时遇到问题.我已经在MSSQL中做到了，如下所示:

I'm having a problem with making a sha1-hash of a row in a select on an Oracle database. I've done it in MSSQL as follows:

SELECT *,HASHBYTES('SHA1',CAST(ID as varchar(10)+
  TextEntry1+TextEntry2+CAST(Timestamp as varchar(10)) as Hash
FROM dbo.ExampleTable
WHERE ID = [foo]

但是，在使用Oracle时，我似乎找不到要使用的类似功能. 就我的谷歌搜索带给我的是，我猜测dbms_crypto.hash_sh1与它有关，但是我还无法将其包裹住...

However, I can't seem to find a similar function to use when working with Oracle. As far as my googling has brought me, I'm guessing dbms_crypto.hash_sh1 has something to do with it, but I haven't been able to wrap my brain around it yet...

任何指针将不胜感激.

推荐答案

包 DBMS_CRYPTO 是生成哈希的正确软件包.默认情况下，它不授予PUBLIC，您必须专门授予它(GRANT EXECUTE ON SYS.DBMS_CRYPTO TO user1).

The package DBMS_CRYPTO is the correct package to generate hashes. It is not granted to PUBLIC by default, you will have to grant it specifically (GRANT EXECUTE ON SYS.DBMS_CRYPTO TO user1).

此函数的结果为数据类型RAW.您可以将其存储在RAW列中，或使用RAWTOHEX或UTL_ENCODE.BASE64_ENCODE函数将其转换为VARCHAR2.

The result of this function is of datatype RAW. You can store it in a RAW column or convert it to VARCHAR2 using the RAWTOHEX or UTL_ENCODE.BASE64_ENCODE functions.

HASH函数被重载以接受三种数据类型作为输入:RAW，CLOB和BLOB.由于隐式转换规则，如果您使用VARCHAR2作为输入，Oracle会尝试将其转换为RAW，并且极有可能失败，因为此转换仅适用于十六进制字符串.

The HASH function is overloaded to accept three datatypes as input: RAW, CLOB and BLOB. Due to the rules of implicit conversion, if you use a VARCHAR2 as input, Oracle will try to convert it to RAW and will most likely fail since this conversion only works with hexadecimal strings.

如果使用VARCHAR2，则需要将输入转换为二进制数据类型或CLOB，例如:

If you use VARCHAR2 then, you need to convert the input to a binary datatype or a CLOB, for instance :

DECLARE
   x RAW(20);
BEGIN
   SELECT sys.dbms_crypto.hash(utl_raw.cast_to_raw(col1||col2||to_char(col3)), 
                               sys.dbms_crypto.hash_sh1) 
     INTO x 
     FROM t;
END;

您将在 DBMS_CRYPTO.hash

you will find additional information in the documentation of DBMS_CRYPTO.hash

这篇关于在Oracle中连续进行sha1-hash的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！