Invalid Identifier error while using standard_hash function in oracle 11g

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.

Solution

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>