How to convert VARCHAR2 to BLOB inside Oracle 11g PL/SQL after ORA-06502

Question

I have a function, that calculates hash value of big string! First of all I wrote a cursor T1_CUT, which can include variable count of SELECT statements, something like: ``` SELECT T1.COL1||T1.COL2||...||T1.COLn FROM T1 WHERE id=documentid SELECT T2.COL1||T2.COL2||...||T2.COLn FROM T2 WHERE id=documentid ... SELECT Tn.COL1||Tn.COL2||...||Tn.COLn FROM Tn WHERE id=documentid ``` Each SELECT can contain one or more rows. So, I concat ALL values in rows in each SELECT and ALL SELECTs values in one big string V_RESULT with type VARCHAR2(32767). After that I get hash value (S_HASH_RESULT) of this big string using MD5. It works fine about 8 monthes, but few days ago I've got ORA-06502 (no surprise). It means, that my final big string have more than 32K symbols (we use 1byte character set - CL8MSWIN1251). Friends, how can I rewrite my function for using BLOB data type to V_RESULT variable, not VARCHAR2(32767). Below, text of my function: ``` FUNCTION CALC_HASH (P_PARTAB_ID IN NUMBER, P_DOC_ID…

Accepted Answer

there is a function called `utl_raw.cast_to_raw(vc)` which transforms a `varchar2` into a `BLOB` value.

However, I recommend to use `CLOB` to store string values. `BLOB` has no character semantics at all, i.e., `NLS_LANG` settings are ignored.

EDIT:

if you want to transform `VARCHAR2` to `CLOB`, simply use `TO_CLOB`

How to convert VARCHAR2 to BLOB inside Oracle 11g PL/SQL after ORA-06502

Solution

Problem