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 IN NUMBER)
RETURN VARCHAR2
IS
  S_HASH_RESULT VARCHAR2(1000);
  V_RESULT VARCHAR2(32767);
  CURSOR T1_CUT IS
   ...
   /*BIG COMPLAIN SELECT*/
   ...

    T1 T1_CUT%ROWTYPE;
  TYPE VALUES_T IS TABLE OF VARCHAR2(32767);
  L_VALUES VALUES_T;
BEGIN
   OPEN T1_CUT;
   FETCH T1_CUT INTO T1;
     WHILE T1_CUT%FOUND
      LOOP
        EXECUTE IMMEDIATE
         T1.TEXT
        BULK COLLECT INTO L_VALUES;
        FOR INDX IN 1 .. L_VALUES.COUNT
           LOOP
              V_RESULT := V_RESULT || '' ||TO_CHAR(L_VALUES (INDX));
           END LOOP;
      FETCH T1_CUT INTO T1;
      END LOOP;
   CLOSE T1_CUT;

   S_HASH_RESULT := DBMS_OBFUSCATION_TOOLKIT.MD5(input_string=>V_RESULT);

   RETURN S_HASH_RESULT;
END CALC_HASH;

Thanks in advance!

Solution

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

Comments

[Sergey]

Probably, it'll be photo column in some table(s), so, I think BLOB is better option..

[TuGordoBello]

Works, ty. Do you know how to convert blob to varchar2?

[Uday Shankar]

try this: select utl_raw.cast_to_varchar2(dbms_lob.substr(BLOB_FIELD)) from TABLE_WITH_BLOB where ID = '<row id>';