I'm trying to generate a hash based on a field but got the following error:
Query:
select standard_hash(pk_time) from schema.table
Error:
"STANDARD_HASH": invalid identifier
The column type is Integer, but I've also tried with other types such as varchar, date and got the same result.
As an extra comment, I started working with this function due to a partitioned table read from AWS Glue using DynamicFrame parallel reading using hashfield option.
That won't - can't - work in Oracle 11g as standard_hash was introduced in Oracle 12c.
Use dbms_crypto instead. One option is to create your own function, e.g.
SQL> select * From v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> CREATE OR REPLACE FUNCTION f_hash (par_string IN VARCHAR2)
2 RETURN RAW
3 IS
4 retval RAW (256);
5 BEGIN
6 retval :=
7 DBMS_CRYPTO.HASH (UTL_I18N.STRING_TO_RAW ('test', 'AL32UTF8'),
8 DBMS_CRYPTO.HASH_SH1);
9
10 RETURN retval;
11 END;
12 /
Function created.
SQL> SELECT f_hash ('test') FROM DUAL;
F_HASH('TEST')
--------------------------------------------------------------------------------
A94A8FE5CCB19BA61C4C0873D391E987982FBBD3
SQL>
Just to verify the result - this is 21c XE which does offer standard_hash:
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
SQL> select standard_hash('test') from dual;
STANDARD_HASH('TEST')
----------------------------------------
A94A8FE5CCB19BA61C4C0873D391E987982FBBD3
SQL>