title: How to convert VARCHAR2 to BLOB inside Oracle 11g PL/SQL after ORA-06502
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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). </p>
  
  <p>Friends, how can I rewrite my function for using BLOB data type to V_RESULT variable, not VARCHAR2(32767).</p>
  
  <p>Below, text of my function:</p>
  
  <pre><code>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=&gt;V_RESULT);
  
     RETURN S_HASH_RESULT;
  END CALC_HASH;
  </code></pre>
  
  <p>Thanks in advance!</p>