title: Migration script with dbms_lob.substr gets "character string buffer too small"
body: <p>I have a problem with a script that migrates data from an old table to a new one. One of the columns in the old table is a CLOB but in the new table it is VARCHAR2. I tried to insert those using the code a below. But I got in trouble with error:</p>
  
  <blockquote>
    <p>ORA-06502: PL/SQL: numeric or value error: character string buffer too small.</p>
  </blockquote>
  
  <pre><code>DECLARE
      CURSOR CUR IS
          SELECT T.*
            FROM ACTIVITY_EVENT T
           WHERE T.POST_TEXT IS NOT NULL;
      R CUR%ROWTYPE;
  BEGIN
   FOR R IN CUR
      LOOP
          INSERT INTO STREAM_TEXT
              WITH STR AS
               (SELECT T.*
                  FROM STREAM T
                 WHERE T.OLD_ID = R.ID)
              SELECT SEQ$STREAM_TEXT.NEXTVAL AS ID,
                     DBMS_LOB.SUBSTR(T.POST_TEXT, 4000, 1) AS TEXT,
                     T.DT AS DT,
                     'READY' AS STATE,
                     STR.ID AS STREAM_ID
                FROM ACTIVITY_EVENT T
                LEFT JOIN STR
                  ON STR.OLD_ID = T.ID
               WHERE T.POST_TEXT IS NOT NULL
                 AND STR.OLD_ID = T.ID;
      END LOOP;
  END;
  </code></pre>
  
  <p>I did this code without the loop first and got the same problem so I tried to create a loop. But the result is the same.</p>
  
  <p>This simple query fails with the same error:</p>
  
  <pre><code>SELECT T.ID, DBMS_LOB.SUBSTR(T.POST_TEXT, 4000, 1)
  FROM ACTIVITY_EVENT T
  WHERE T.POST_TEXT IS NOT NULL
  </code></pre>