Oracle Error - ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I have written the following code:



CREATE OR REPLACE PROCEDURE procUpdateTempEventDetails
    (comp number, seevent number, seeventline number)
  IS
    req NUMBER;
    reqline number;
    lindex varchar(4);
    cmt varchar(500);
    seeventsourceline number;
 BEGIN
          SELECT count(*) into seeventsourceline from
            sourcingeventlinesource
            WHERE sourcingevent = seevent
            AND sourcingeventline = 1;
      SELECT sourcingeventlinesource.sourcedocn INTO req
                FROM sourcingeventlinesource
                WHERE sourcingevent = seevent
                AND sourcingeventline = seeventline
            AND sourcingeventlinesource = seeventsourceline
            AND origincode = 'RQ';
      UPDATE tempEventDetails
            SET req_number = req
            WHERE company = comp
            AND sourcingevent = seevent
            AND sourcingeventline = seeventline;
       DBMS_OUTPUT.PUT_LINE('Req is: '||req);
      SELECT sourcingeventlinesource.linenbr INTO reqline
                FROM sourcingeventlinesource
                WHERE sourcingevent = seevent
                AND sourcingeventline = seeventline
            AND sourcingeventlinesource = seeventsourceline;
      UPDATE tempEventDetails
            SET req_line = reqline
            WHERE company = comp
            AND sourcingevent = seevent
            AND sourcingeventline = seeventline;
       DBMS_OUTPUT.PUT_LINE('Reqline is: '||reqline);
      SELECT reqline.l_index INTO lindex
                FROM lawprod.reqline
                WHERE req_number = req
                AND line_nbr = reqline;
      UPDATE tempEventDetails
            SET l_index = lindex
            WHERE company = comp
            AND sourcingevent = seevent
            AND sourcingeventline = seeventline;
       DBMS_OUTPUT.PUT_LINE('L Index is: '||lindex);
        cmt := get_comments;
      UPDATE SourcingEventLine
            SET description = cmt
            WHERE company = comp
            AND sourcingevent = seevent
            AND sourcingeventline = seeventline;
      UPDATE tempEventDetails
            SET update_status = 2
            WHERE company = comp
            AND sourcingevent = seevent
            AND sourcingeventline = seeventline;
EXCEPTION
      WHEN NO_DATA_FOUND THEN
            UPDATE tempEventDetails
                  SET update_status = 9
                  WHERE company = comp
                  AND sourcingevent = seevent
                  AND sourcingeventline = seeventline;
       DBMS_OUTPUT.PUT_LINE('No Data for this record.');
END;
/

CREATE OR REPLACE FUNCTION get_comments
  RETURN VARCHAR
IS
  l_text  VARCHAR(500) := NULL;
BEGIN
  FOR cur_rec IN (SELECT object FROM lawprod.l_hrln WHERE object like 'TYPE=P,%')
  LOOP
    l_text := l_text || chr(10) || substr(cur_rec.object,(instr(cur_rec.object,',',1,3)+1));
  END LOOP;
  RETURN RTRIM(LTRIM(l_text));
END;
/

when I run it I get the error at the "_text := l_text || chr(10) || substr(cur_rec.object,(instr(cur_rec.object,',',1,3)+1));" line.
Start Free Trial