CODEWITHSUNDEEP
pythonsqloracle-databasecx-oracle
wolφi
wolφi

Reputation: 8361

How to call DBMS_CRYPTO.HASH when inserting a CLOB into Oracle with Python?

I'm storing CLOBs in Oracle. To speed up finding identical CLOBs, I'd like to introduce a hash value of the CLOBs. What I tried so far is

  1. INSERT the CLOB
  2. UPDATE the hash value based on the stored clob.

How can this be done in one operation instead of two?

#!/usr/local/bin/python3
import cx_Oracle

con = cx_Oracle.connect('scott/tiger@localhost:1512/ORCLPDB1', encoding="UTF-8")
cursor = con.cursor()
cursor.execute("CREATE TABLE t (id NUMBER, script CLOB, script_hash RAW(32))")  

my_text = '$'*2**10

statement = "INSERT INTO t (id, script) VALUES (:my_id, :my_clob)"
cursor.execute(statement, (1, my_text))

statement = """
    UPDATE t 
       SET script_hash = DBMS_CRYPTO.HASH(script, 2) 
     WHERE id = :my_id"""
cursor.execute(statement, {'my_id': 1})

con.commit()    
con.close()

This doesn't work:

statement = """
   INSERT INTO t (id, script, script_hash) 
   VALUES (:my_id, :my_clob, DBMS_CRYPTO.HASH(:my_clob, 2))"""
cursor.execute(statement, (2, my_text, my_text))

# cx_Oracle.DatabaseError: ORA-01465: invalid hex number

(Oracle 12.2 using Python and cx_Oracle 6.3)

Upvotes: 0

Views: 1948

Answers (1)

Luke Woodward
Luke Woodward

Reputation: 64969

This works for me, admittedly with Oracle 11g XE (and cx_Oracle 6.3.1):

statement = """
   DECLARE
      l_clob         CLOB   := :my_clob;
   BEGIN
     INSERT INTO t (id, script, script_hash) 
       VALUES (:my_id, l_clob, DBMS_CRYPTO.HASH(l_clob, 2));
   END;"""

cursor.execute(statement, (my_text, 2))

I couldn't reproduce your error ORA-01465: invalid hex number with your doesn't-work code: my 11g XE database gave me the error ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column instead.

Upvotes: 1

Related Questions