title: Oracle Stored Procedure OUT CLOB return error character string buffer too small
body: <p>I have an Oracle stored procedure which I'm calling from an application. The stored procedure will search my the parameter given from the application and query out the data and then return the information message to my application. </p>
  
  <p>However, I get an error</p>
  
  <blockquote>
    <p>ORA-06502: PL/SQL: numeric or value error: character string buffer too small</p>
  </blockquote>
  
  <p>on line 37, which is P_INFO and I'm using CLOB data type here, suppose it can support a very large amount of character, assuming the data return is more than 100k of characters.</p>
  
  <pre><code>create or replace PROCEDURE SimpleProcedure
  (
    P_ID  VARCHAR2,
    P_INFO OUT CLOB
  ) AS
    V_PROJ_ID   VARCHAR2(50);
    V_PROJ_NAME NVARCHAR2(1000);
    V_APP_ID     VARCHAR2(50);
    V_APP_STATUS VARCHAR2(50);
    CURSOR SUB_ATTACH_CURSOR(APP_ID VARCHAR2) IS
      SELECT S.SUBMISSION_ID,
             SA.FILE_ID,
             SA.STATUS_CODE,
             E.FILENAME_TEXT
        FROM SUBM_ATTCHMNT SA
       INNER JOIN SUB_SUBMISSION S
          ON S.SUBMISSION_ID = SA.SUBMISSION_ID
       INNER JOIN SUB_FILE E
          ON E.FILE_ID = SA.FILE_ID
       WHERE S.APP_ID = APP_ID;
  BEGIN
    SELECT P.PROJECT_ID,
           P.NAME_TEXT,
           A.APP_ID,
           A.STATUS_CODE
      INTO V_PROJ_ID, V_PROJ_NAME, V_APP_ID, V_APP_STATUS
      FROM SUB_APP A
     INNER JOIN SUB_PROJ P
        ON P.PROJECT_ID = A.PROJECT_ID
     WHERE A.ID = P_ID;
    P_INFO := 'Information:
                 Proj id: ' || V_PROJ_ID || ',
                 Proj name: ' || V_PROJ_NAME || ',
                 App id: ' || V_APP_ID || ',
                 App status: ' || V_APP_STATUS || ',';
    FOR SA IN SUB_ATTACH_CURSOR(V_APP_ID) LOOP
      P_INFO := P_INFO || 'Submission id: ' || SA.SUBMISSION_ID ||
                   ',file id: ' || SA.FILE_ID || ', file name: ' ||
                   SA.FILENAME_TEXT || ', file Status: ' ||
                   SA.STATUS_CODE || ' ';
    END LOOP;
  END SimpleProcedure;
  </code></pre>