Oracle STANDARD_HASH not available in PLSQL?

Question

I'm trying to use STANDARD_HASH Oracle (12c) function in PL/SQL but seems not available:

SQL> exec   dbms_output.put_line(STANDARD_HASH('test'));
BEGIN dbms_output.put_line(STANDARD_HASH('test')); END;

                           *
ERROR at line 1:
ORA-06550: line 1, column 28:
PLS-00201: identifier 'STANDARD_HASH' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

From sql is working just fine:

SQL> select STANDARD_HASH('test') from dual;

STANDARD_HASH('TEST')
----------------------------------------
A94A8FE5CCB19BA61C4C0873D391E987982FBBD3

Why? What is the best way to implement the STANDARD_HASH in PLSQL?

Regards

Not all SQL functions are available in PL/SQL; it isn't on this list but perhaps should be. Might fall into the same category as LNNVL. — Alex Poole
– Alex Poole, Commented Feb 23, 2016 at 10:32
That is the more correct answer than usage of custom wrapper function which is re-invention of the wheel. Definitely this variant must be marked as a correct answer. — Alexander Zemerov
– Alexander Zemerov, Commented Jun 23, 2017 at 14:29

Lalit Kumar B · Accepted Answer · 2016-02-25 11:18:42Z

21

Seems like it isn't yet a part of PL/SQL in 12c.

As a workaround, use SELECT INTO in PL/SQL:

SQL> set serveroutput on
SQL> DECLARE
  2    str VARCHAR2(40);
  3  BEGIN
  4    SELECT STANDARD_HASH('test') INTO str FROM dual;
  5    dbms_output.put_line(str);
  6  END;
  7  /
A94A8FE5CCB19BA61C4C0873D391E987982FBBD3

PL/SQL procedure successfully completed.

SQL>

I would suggest to create a function, and use it whenever you need it in PL/SQL.

For example,

SQL> CREATE OR REPLACE FUNCTION STANDARD_HASH_OUTPUT(str IN VARCHAR2)
  2    RETURN VARCHAR2
  3  AS
  4    op VARCHAR2(40);
  5  BEGIN
  6    SELECT STANDARD_HASH(str) INTO op FROM dual;
  7    RETURN op;
  8  END;
  9  /

Function created.

Call the function directly in PL/SQL block:

SQL> BEGIN
  2     dbms_output.put_line(STANDARD_HASH_OUTPUT('test'));
  3  END;
  4  /
A94A8FE5CCB19BA61C4C0873D391E987982FBBD3

PL/SQL procedure successfully completed.

SQL>

edited Feb 25, 2016 at 11:18

answered Feb 23, 2016 at 10:31

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

StefanG Over a year ago

Thanks Lalit! Seems like I will implement this method as the dbms_crypto.hash function seems to be much slower (avg.43%). I prefer to avoid select from dual (personal opinion) but in this case I will go with this solution.

Naeel Maqsudov Over a year ago

In 19c still the same

Codo · Accepted Answer · 2016-02-23 10:57:13Z

3

For text, STANDARD_HASH is the same as DBMS_CRYPTO.HASH with SHA1:

begin
  dbms_output.put_line(dbms_crypto.hash(cast('test' as clob), dbms_crypto.hash_sh1));
end;
/

Output:

A94A8FE5CCB19BA61C4C0873D391E987982FBBD3

For other data types, it's not documented how they are passed to the hash function.

answered Feb 23, 2016 at 10:57

Codo

79.7k18 gold badges176 silver badges218 bronze badges

2 Comments

StefanG Over a year ago

Thanks Codo! For some reason this method is slower (because of the cast to clob?) than a select from dual using the STANDARD_HASH function. I'm surprised!

Codo Over a year ago

That's interesting indeed. I would have expected the SELECT to be slower as it involves a context switch. But it's always better to measure than to assume. Thanks for the update.

user14193099 · Accepted Answer · 2020-08-30 22:07:45Z

-3

standard hash allows you to specify hash algorithm. Algorithms are SHA1, SHA256, SHA384, SHA512, and MD5. If you omit this argument, then SHA1 is used.

To get a more familiar GUID than sys_guid provides:

LOWER (CAST (standard_hash (SYS_GUID (), 'MD5') AS VARCHAR2 (40)))

I've tested on 20.5 million records with no collisions...

answered Aug 30, 2020 at 22:07

user14193099

1

2 Comments

user14193099 Over a year ago

Another benefit is that this method uses all built-in functions and can be used for a column DEFAULT. Since you can't use user defined functions, procedures and packages in DEFAULT values I was looking for a way to stick with buit-in functions to morph the SYS_GUID into a more visually distinguishable format .

Quantenmechaniker Apr 22 at 9:06

That was not the OP's question, and the GUID tidbit might be interesting but is irrelevant here.

Collectives™ on Stack Overflow

Oracle STANDARD_HASH not available in PLSQL?

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related