ORA-24805:LOB type mismatch

Question

Let there be table A(text_1 varchar2(4000 CHAR), text_2 varchar(4000 CHAR)) And here is the select for this:

SELECT sys.dbms_crypto.hash(empty_clob() || text_1 || text_2, 2)  from A;

I am getting error 24805. I am expecting casting text_1 and text_2 to CLOB , and connecting to each other. After that I need to get hash_code using function below. This code will also be part of PL/SQL block ( part of insert statement in function). If I will use this:

SELECT sys.dbms_crypto.hash(to_clob('') || text_1 || text_2, 2)  from A;

Everything is ok now. So, what was the problem?

 create table A(text_1 varchar2(4000 CHAR), text_2 varchar2(4000 CHAR));
 insert into A(text_1,text_2)values ('test1','test2');
 commit;

Here is an example of creating data for A.

Answer 1

You can use TO_CLOB for concatenate your char based columns. Then use CONCAT which seems can work with CLOB. Try below.

SELECT DBMS_CRYPTO.HASH(CONCAT(TO_CLOB (TEXT_1) , TO_CLOB (TEXT_2)), 2)  FROM A;

Answer 2

Please try below query,

SELECT sys.dbms_crypto.hash(to_clob(text_1 || text_2), 2)  from A;