cast VARCHAR2 to LONG,RAW,CLOB and find HASH md5

There is a table A with fields (str1 VARCHAR2(4000 CHAR),str2 VARCHAR2(4000 CHAR))

There is a table B with fields (str1 VARCHAR2(4000 CHAR),str2 VARCHAR2(4000 CHAR),hash_code NUMBER)

begin
insert into B(str1,str2,hash_code)
select str1, str2, standart_hash(str1 ||str2,'MD5') as hash_code from A;
commit;
end;

As a result there is an exception when length(str1||str2) is longer than 4000 (varchar2 is too small).

How can I cast this to other type like clob in such select? to_clob(str1 || str2) does not work.

And standart_hash does not work with clob,long,raw.

What can I use to solve this after solving problem with casting?

Answer

The varchar2 datatype actually has a max length of 32K in PL/SQL, and standard_hash has no limit on the size of the input. Also note that standard_hash returns the RAW datatype, not NUMBER as you have the hash_code column defined in your example.

SQL> desc A
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STR1                           VARCHAR2(4000)
 STR2                           VARCHAR2(4000)

SQL> desc B
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STR1                           VARCHAR2(4000)
 STR2                           VARCHAR2(4000)
 HASH_VALUE                     RAW(50)

SQL> select length(str1), length(str2) from A;

LENGTH(STR1) LENGTH(STR2)
------------ ------------
        4000         4000
           3            3

SQL> insert into B (str1, str2, hash_value) select str1, str2, standard_hash(str1||str2,'MD5') as hash_value from A;

2 rows created.

SQL> begin
  2  insert into B (str1, str2, hash_value) select str1, str2, standard_hash(str1||str2,'MD5') as hash_value from A;
  3  commit;
  4  end;
  5  /

PL/SQL procedure successfully completed.

As you can see, I was able to run your example with no problem.