Computing ORA_HASH

I wrote a short Python function to compute a hash value for a given expression. The return value of the function matches the return value of ORA_HASH on inputs I tested it with in Oracle 23c on Linux x86-64.

In its simplest form, the invocation can be as follows:

?

1 2 3 4 5 6 7 8

>>> ora_hash(b'test') 2662839991   SQL> select ora_hash('test');   ORA_HASH('TEST') ----------------       2662839991

Handling non-string datatypes

In case of a non-string datatype, some conversion should be performed. ORA_HASH accepts a variety of datatypes. I use NUMBER and DATE in the examples below. The procedure is essentially the same with both datatypes: need to take the internal representation of a value in Oracle and pass it to Python. It should be possible to handle any other supported datatypes following the same procedure.

NUMBER

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SQL> select ora_hash(2023);   ORA_HASH(2023) --------------     2671887358   SQL> select dump(2023, 16);   DUMP(2023,16) --------------------- Typ=2 Len=3: c2,15,18   >>> ora_hash(b'\xc2\x15\x18') 2671887358

DATE

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SQL> select ora_hash(to_date('2023-03-01 12:34:56', 'yyyy-mm-dd hh24:mi:ss'));   ORA_HASH(TO_DATE('2023-03-0112:34:56','YYYY-MM-DDHH24:MI:SS')) --------------------------------------------------------------                                                      112410422                                                        SQL> select dump(to_date('2023-03-01 12:34:56', 'yyyy-mm-dd hh24:mi:ss'), 16);   DUMP(TO_DATE('2023-03-0112:34:56 -------------------------------- Typ=13 Len=8: e7,7,3,1,c,22,38,0   >>> ora_hash(b'\xe7\x07\x03\x01\x0c\x22\x38\x00') 112410422

Specifying max_bucket and seed_value

These parameters are also supported.

MAX_BUCKET

?

1 2 3 4 5 6 7 8

SQL> select ora_hash('abracadabra', 255);   ORA_HASH('ABRACADABRA',255) ---------------------------                          82   >>> ora_hash(b'abracadabra', 255) 82

SEED_VALUE

?

1 2 3 4 5 6 7 8

SQL> select ora_hash('abracadabra', power(2,32)-1, 123);   ORA_HASH('ABRACADABRA',POWER(2,32)-1,123) -----------------------------------------                                4012392341   >>> ora_hash(b'abracadabra', seed=123) 4012392341

MAX_BUCKET and SEED_VALUE

?

1 2 3 4 5 6 7 8

SQL> select ora_hash('abracadabra', 255, 123);   ORA_HASH('ABRACADABRA',255,123) -------------------------------                             149   >>> ora_hash(b'abracadabra', 255, 123) 149