PL/SQL: numeric or value error: character string buffer too small

Question

I have written the following PL/SQL code to generate a report.

CREATE OR REPLACE

PACKAGE CAcxref AS 
    PROCEDURE CAcxref_PROC (inp_str     IN  VARCHAR2,out_retCode OUT NUMBER,out_rec OUT VARCHAR2);
END CAcxref;
/
CREATE OR REPLACE
PACKAGE BODY CAcxref AS
OutArr      custom.ArrayType;
V_PROCESS1  VARCHAR2(100);
V_PROCESS2  VARCHAR2(100);
V_PROCESS3  VARCHAR2(100);
V_PROCESS4  VARCHAR2(100);
v_CutOff    VARCHAR2(100);
TYPE RecTyp IS RECORD (
rec_PROCESS_NAME  custom.c_master_notify_tbl.PROCESS_NAME%type,
rec_SOL_ID custom.c_master_notify_tbl.SOL_ID%type,
rec_schm_type custom.c_master_notify_tbl.SCHM_TYPE%type,
rec_foracid  custom.c_master_notify_tbl.FORACID%type,
rec_RCRE_TIME custom.c_master_notify_tbl.RCRE_TIME%type);
    TYPE Cacxref_data IS TABLE OF RecTyp
  INDEX BY BINARY_INTEGER;
    Cacxref_FetchData Cacxref_data;

    lv_variable             VARCHAR2 (5000) := '';
    lv_cnt                  NUMBER        ; 
CURSOR GetDetails (v_Process1 VARCHAR2,v_Process2 VARCHAR2,v_Process3 VARCHAR2,v_Process4 VARCHAR2,v_CutOff VARCHAR2) IS

SELECT PROCESS_NAME,SOL_ID,SCHM_TYPE,FORACID,RCRE_TIME 
FROM custom.C_MASTER_NOTIFY_TBL 
WHERE PROCESS_NAME IN (V_Process1)
AND TO_CHAR(RCRE_TIME,'DD-MM-YYYY HH24:MI:SS') <= TO_CHAR(TO_DATE('12042016220000','DDMMYYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')
AND CXREF_STATUS IN ('O') AND DEL_FLG='N' AND ONLINE_OR_BATCH='B'
UNION
SELECT PROCESS_NAME,SOL_ID,SCHM_TYPE,FORACID,RCRE_TIME 
FROM custom.C_MASTER_NOTIFY_TBL 
WHERE PROCESS_NAME IN (v_Process2)
AND TO_CHAR(RCRE_TIME,'DD-MM-YYYY HH24:MI:SS') <= TO_CHAR(TO_DATE('12042016220000','DDMMYYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')
AND CXREF_STATUS IN ('O') AND DEL_FLG='N' AND ONLINE_OR_BATCH='B'
UNION
SELECT PROCESS_NAME,SOL_ID,SCHM_TYPE,FORACID,RCRE_TIME 
FROM custom.C_MASTER_NOTIFY_TBL 
WHERE PROCESS_NAME IN (v_Process3)
AND TO_CHAR(RCRE_TIME,'DD-MM-YYYY HH24:MI:SS') <= TO_CHAR(TO_DATE('12042016220000','DDMMYYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')
AND CXREF_STATUS IN ('O') AND DEL_FLG='N' AND ONLINE_OR_BATCH='B'
UNION
SELECT PROCESS_NAME,SOL_ID,SCHM_TYPE,FORACID,RCRE_TIME 
FROM custom.C_MASTER_NOTIFY_TBL 
WHERE PROCESS_NAME IN (v_Process4)
AND TO_CHAR(RCRE_TIME,'DD-MM-YYYY HH24:MI:SS') <= TO_CHAR(TO_DATE('12042016220000','DDMMYYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')
AND CXREF_STATUS IN ('O') AND DEL_FLG='N' AND ONLINE_OR_BATCH='B';
PROCEDURE CAcxref_PROC(inp_str IN VARCHAR2, out_retCode OUT NUMBER, out_rec OUT VARCHAR2)
IS
BEGIN
  out_retCode := 0;
  out_rec := '';
  custom.stringToArray.formInputArr (inp_str,OutArr);
  V_Process1 := OutArr(0);
  DBMS_OUTPUT.PUT_LINE('Process one:'||V_Process1); 
  V_Process2 := OutArr(1);
  DBMS_OUTPUT.PUT_LINE('Process two:'||V_Process2); 
  V_Process3 := OutArr(2);
  DBMS_OUTPUT.PUT_LINE('Process THREE:'||V_Process3); 
  V_Process4 := OutArr(3);
  DBMS_OUTPUT.PUT_LINE('Process four:'||V_Process4); 
  v_CutOff := OutArr(4);
  DBMS_OUTPUT.PUT_LINE('Cutoff Time:'||v_CutOff); 
    IF NOT GetDetails%ISOPEN then
        OPEN GetDetails(v_Process1,v_Process2,v_Process3,v_Process4,v_CutOff);
        DBMS_OUTPUT.PUT_LINE('CUSRSOR HAS BEEN OPENED');
    END IF;
DBMS_OUTPUT.PUT_LINE('COUNT IS:'||Cacxref_FetchData.COUNT);

LOOP
  FETCH GetDetails  
  BULK COLLECT 
  INTO Cacxref_FetchData
  LIMIT 10; /* WARNING: NUMBER OF RECORDS FETCHED SHOULD MATCH SIZE OF OUTREC VARIABLE */
  DBMS_OUTPUT.PUT_LINE('INSIDE THE BULK COLLECT LOOP');
  EXIT WHEN GetDetails%NOTFOUND;
END LOOP;

FOR indx in 1 .. Cacxref_FetchData.COUNT
LOOP
    DBMS_OUTPUT.PUT_LINE('INSIDE THE FOR LOOP');
  lv_variable := Cacxref_FetchData(indx).rec_PROCESS_NAME || '|' || Cacxref_FetchData(indx).rec_SOL_ID || '|' || 
  Cacxref_FetchData(indx).rec_SCHM_TYPE || '|' || Cacxref_FetchData(indx).rec_FORACID||'|' ||'|' ||'|' ||'|' ||'|' ||'|' ||'|' ||'|'
  ||'|' ||'|' ||'|' ||'|' ||'|' ||'|' ||'|' ||'|' ||'|' ||'|' ||'|' ||'|' ||'|' ||'|' 
  ||Cacxref_FetchData(indx).rec_RCRE_TIME   ;
  DBMS_OUTPUT.PUT_LINE('lv_cnt is 1 and lv_variable is:'||lv_variable);
  out_rec := lv_variable;
END LOOP;
END CAcxref_PROC;
END CAcxref;
/
DROP PUBLIC SYNONYM CAcxref
/
CREATE PUBLIC SYNONYM CAcxref FOR CAcxref
/
Grant EXECUTE ON CAcxref TO TBAADM, TBAUTIL,TBAGEN,SYSTEM
/
SET SERVEROUTPUT ON
/

When executing I am getting character string buffer too small error for the first time, for the second time though I am getting no error but no row is getting fetched in the cursor either.

Below is my execution screenshot:

I am not able to understand for which variable this error is being thrown.

Execution prompt:

SQL> SET SERVEROUTPUT ON
SQL> exec custom.CAcxref.CAcxref_Proc ('ACOPN!ACMOD!TEST!TEST1!12042016220000',:out_retCode,:out_rec);
Process one:ACOPN
Process two:ACMOD
Process THREE:TEST
Process four:TEST1
Cutoff Time:12042016220000
CUSRSOR HAS BEEN OPENED
COUNT IS:0
INSIDE THE BULK COLLECT LOOP
INSIDE THE FOR LOOP
lv_cnt is 1 and lv_variable
is:ACMOD|102|SBA|99101000036||||||||||||||||||||||12-APR-16
BEGIN custom.CAcxref.CAcxref_Proc ('ACOPN!ACMOD!TEST!TEST1!12042016220000',:out_retCode,:out_rec); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "CUSTOM.CACXREF", line 84
ORA-06512: at line 1


SQL> exec custom.CAcxref.CAcxref_Proc ('ACOPN!ACMOD!TEST!TEST1!12042016220000',:out_retCode,:out_rec);
Process one:ACOPN
Process two:ACMOD
Process THREE:TEST
Process four:TEST1
Cutoff Time:12042016220000
COUNT IS:2
INSIDE THE BULK COLLECT LOOP

PL/SQL procedure successfully completed.

SQL> print :out_rec

OUT_REC
--------------------------------

EDIT I made some changes in the PL/SQL and now it is working every alternate time if only I set the out_rec variable with a large varchar2 value.

CREATE OR REPLACE
PACKAGE CAcxref AS 
    PROCEDURE CAcxref_PROC (inp_str     IN  VARCHAR2,out_retCode OUT NUMBER,out_rec OUT VARCHAR2);
END CAcxref;
/
CREATE OR REPLACE
PACKAGE BODY CAcxref AS
OutArr      tbaadm.basp0099.ArrayType;
V_PROCESS1  VARCHAR2(20);
V_PROCESS2  VARCHAR2(20);
V_PROCESS3  VARCHAR2(20);
V_PROCESS4  VARCHAR2(20);
v_CutOff    VARCHAR2(14);
TYPE RecTyp IS RECORD (
rec_PROCESS_NAME  custom.c_master_notify_tbl.PROCESS_NAME%type,
rec_SOL_ID custom.c_master_notify_tbl.SOL_ID%type,
rec_schm_type custom.c_master_notify_tbl.SCHM_TYPE%type,
rec_foracid  custom.c_master_notify_tbl.FORACID%type,
rec_RCRE_TIME custom.c_master_notify_tbl.RCRE_TIME%type);
    TYPE Cacxref_data IS TABLE OF RecTyp
  INDEX BY BINARY_INTEGER;
    Cacxref_FetchData Cacxref_data;

    lv_variable             VARCHAR2 (3000) := '';
    lv_cnt                  NUMBER        ; 
CURSOR GetDetails (v_Process1 VARCHAR2,v_Process2 VARCHAR2,v_Process3 VARCHAR2,v_Process4 VARCHAR2,v_CutOff VARCHAR2) IS

SELECT PROCESS_NAME,SOL_ID,SCHM_TYPE,FORACID,RCRE_TIME 
FROM custom.C_MASTER_NOTIFY_TBL 
WHERE PROCESS_NAME IN (V_Process1)
AND TO_CHAR(RCRE_TIME,'DD-MM-YYYY HH24:MI:SS') <= TO_CHAR(TO_DATE('12042016220000','DDMMYYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')
AND CXREF_STATUS IN ('O') AND DEL_FLG='N' AND ONLINE_OR_BATCH='B'
UNION
SELECT PROCESS_NAME,SOL_ID,SCHM_TYPE,FORACID,RCRE_TIME 
FROM custom.C_MASTER_NOTIFY_TBL 
WHERE PROCESS_NAME IN (v_Process2)
AND TO_CHAR(RCRE_TIME,'DD-MM-YYYY HH24:MI:SS') <= TO_CHAR(TO_DATE('12042016220000','DDMMYYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')
AND CXREF_STATUS IN ('O') AND DEL_FLG='N' AND ONLINE_OR_BATCH='B'
UNION
SELECT PROCESS_NAME,SOL_ID,SCHM_TYPE,FORACID,RCRE_TIME 
FROM custom.C_MASTER_NOTIFY_TBL 
WHERE PROCESS_NAME IN (v_Process3)
AND TO_CHAR(RCRE_TIME,'DD-MM-YYYY HH24:MI:SS') <= TO_CHAR(TO_DATE('12042016220000','DDMMYYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')
AND CXREF_STATUS IN ('O') AND DEL_FLG='N' AND ONLINE_OR_BATCH='B'
UNION
SELECT PROCESS_NAME,SOL_ID,SCHM_TYPE,FORACID,RCRE_TIME 
FROM custom.C_MASTER_NOTIFY_TBL 
WHERE PROCESS_NAME IN (v_Process4)
AND TO_CHAR(RCRE_TIME,'DD-MM-YYYY HH24:MI:SS') <= TO_CHAR(TO_DATE('12042016220000','DDMMYYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')
AND CXREF_STATUS IN ('O') AND DEL_FLG='N' AND ONLINE_OR_BATCH='B';
PROCEDURE CAcxref_PROC(inp_str IN VARCHAR2, out_retCode OUT NUMBER, out_rec OUT VARCHAR2)
IS
BEGIN
  out_retCode := 0;
  out_rec := '';
  tbaadm.basp0099.formInputArr (inp_str,OutArr);
  V_Process1 := OutArr(0);
  DBMS_OUTPUT.PUT_LINE('Process one:'||V_Process1); 
  V_Process2 := OutArr(1);
  DBMS_OUTPUT.PUT_LINE('Process two:'||V_Process2); 
  V_Process3 := OutArr(2);
  DBMS_OUTPUT.PUT_LINE('Process THREE:'||V_Process3); 
  V_Process4 := OutArr(3);
  DBMS_OUTPUT.PUT_LINE('Process four:'||V_Process4); 
  v_CutOff := OutArr(4);
  DBMS_OUTPUT.PUT_LINE('Cutoff Time:'||v_CutOff); 
    IF NOT GetDetails%ISOPEN then
        OPEN GetDetails(v_Process1,v_Process2,v_Process3,v_Process4,v_CutOff);
        DBMS_OUTPUT.PUT_LINE('CUSRSOR HAS BEEN OPENED');
    END IF;
DBMS_OUTPUT.PUT_LINE('COUNT OF FETCHDATA IS:'||Cacxref_FetchData.COUNT);

IF GetDetails%ISOPEN THEN
--LOOP
  FETCH GetDetails  
  BULK COLLECT 
  INTO Cacxref_FetchData
  LIMIT 10; /* WARNING: NUMBER OF RECORDS FETCHED SHOULD MATCH SIZE OF OUTREC VARIABLE */
  DBMS_OUTPUT.PUT_LINE('INSIDE THE BULK COLLECT LOOP');
                IF (Cacxref_FetchData.COUNT = 0) THEN
                    CLOSE GetDetails;
                    out_retcode := 1;
                    RETURN;
                END IF;
--  EXIT WHEN GetDetails%NOTFOUND;
--END LOOP;

FOR indx in 1 .. Cacxref_FetchData.COUNT
LOOP
    DBMS_OUTPUT.PUT_LINE('INSIDE THE FOR LOOP');
  lv_variable := Cacxref_FetchData(indx).rec_PROCESS_NAME || '|' || Cacxref_FetchData(indx).rec_SOL_ID || '|' || 
  Cacxref_FetchData(indx).rec_SCHM_TYPE || '|' || Cacxref_FetchData(indx).rec_FORACID||'|' ||'|' ||'|' ||'|' ||'|' ||'|' ||'|' ||'|'
  ||'|' ||'|' ||'|' ||'|' ||'|' ||'|' ||'|' ||'|' ||'|' ||'|' ||'|' ||'|' ||'|' ||'|' 
  ||Cacxref_FetchData(indx).rec_RCRE_TIME   ;
  DBMS_OUTPUT.PUT_LINE('lv_cnt is 1 and lv_variable is:'||lv_variable);
  out_rec := out_rec || lv_variable;
                    IF (indx = Cacxref_FetchData.COUNT) THEN
                        out_rec := out_rec || lv_variable;              

                    ELSE

                        out_rec := out_rec || lv_variable ||  CHR (10);             

                    END IF;
END LOOP;
RETURN;
END IF;
END CAcxref_PROC;
END CAcxref;
/
GRANT EXECUTE ON CUSTOM.CAcxref TO TBAGEN; 
GRANT EXECUTE ON CUSTOM.CAcxref TO TBAADM WITH GRANT OPTION;
GRANT EXECUTE ON CUSTOM.CAcxref TO TBAUTIL;

And now the result looks like this after setting out_rec varchar2(4000):

SQL> set serveroutput on
SQL> exec custom.CAcxref.CAcxref_Proc ('ACOPN!ACMOD!TEST!TEST1!12042016220000',:out_retCode,:out_rec)
Process one:ACOPN
Process two:ACMOD
Process THREE:TEST
Process four:TEST1
Cutoff Time:12042016220000
CUSRSOR HAS BEEN OPENED
COUNT OF FETCHDATA IS:0
INSIDE THE BULK COLLECT LOOP
INSIDE THE FOR LOOP
lv_cnt is 1 and lv_variable
is:ACMOD|102|SBA|99101000036||||||||||||||||||||||12-APR-16
INSIDE THE FOR LOOP
lv_cnt is 1 and lv_variable
is:ACOPN|101|SBA|99101000116||||||||||||||||||||||12-APR-16

PL/SQL procedure successfully completed.

SQL> exec custom.CAcxref.CAcxref_Proc ('ACOPN!ACMOD!TEST!TEST1!12042016220000',:out_retCode,:out_rec)
Process one:ACOPN
Process two:ACMOD
Process THREE:TEST
Process four:TEST1
Cutoff Time:12042016220000
COUNT OF FETCHDATA IS:2
INSIDE THE BULK COLLECT LOOP

PL/SQL procedure successfully completed.

SQL> exec custom.CAcxref.CAcxref_Proc ('ACOPN!ACMOD!TEST!TEST1!12042016220000',:out_retCode,:out_rec)
Process one:ACOPN
Process two:ACMOD
Process THREE:TEST
Process four:TEST1
Cutoff Time:12042016220000
CUSRSOR HAS BEEN OPENED
COUNT OF FETCHDATA IS:0
INSIDE THE BULK COLLECT LOOP
INSIDE THE FOR LOOP
lv_cnt is 1 and lv_variable
is:ACMOD|102|SBA|99101000036||||||||||||||||||||||12-APR-16
INSIDE THE FOR LOOP
lv_cnt is 1 and lv_variable
is:ACOPN|101|SBA|99101000116||||||||||||||||||||||12-APR-16

PL/SQL procedure successfully completed.

Perhaps you should read the line which says "ORA-06512: at "CUSTOM.CACXREF", line 84". Best of luck. — Bob Jarvis - Reinstate Monica
@BobJarvis, line 84 is CUSTOM CACXREF PACKAGE BODY 84 " out_rec := lv_variable; " from dba_source. But I am not able to understand how can out_rec be small? — Mistu4u
@WilliamRobertson, :OUT_REC IS VARCHAR2, I ave not given any size limit. — Mistu4u
I didn't even know you could do that. I just tried (SQL*Plus 11.0.2.0 on Windows) and it allows it but the resulting variable only accepts strings up to 3 bytes. Try varchar2(4000). — William Robertson

William Robertson William Robertson · Accepted Answer · 2016-09-24T08:53:21

(The original 'character string buffer too small' error was due to the SQL*Plus bind variable used to capture the output being declared too small - this was cleared up in the comments. The points below relate to the follow-up question about the code giving results on alternate runs.)

I can see a couple of issues with the code (apart from being unformatted which makes it hard to spot errors - I recommend laying out all code neatly and prefixing global variables with g_, procedure parameters with p_, cursor parameters with cp_ etc).

Firstly, cursor getdetails is global (declared at package body level) but it is never closed, so I suppose global array cacxref_fetchdata keeps the first values it gets for the rest of the session. Is that intended?

Global array outarr has me a bit confused as well, because it's not an 'out' parameter but seems to be used within the procedure for parsing inp_str.

There is a global variable lv_cnt (why lv?) which is never used, but you have an output message saying 'lv_cnt is 1'.

v_cutoff is passed as a cursor parameter but not used within the cursor.

Cursor getdetails could be simplified from four unions to:

cursor getdetails
   ( v_process1 varchar2
   , v_process2 varchar2
   , v_process3 varchar2
   , v_process4 varchar2
   )  -- not used: v_cutoff   varchar2 )
is
   select distinct process_name, sol_id, schm_type, foracid, rcre_time
   from   custom.c_master_notify_tbl
   where  process_name in (v_process1, v_process2, v_process3, v_process4)
   and    to_char(rcre_time,'DD-MM-YYYY HH24:MI:SS')
          <= to_char(to_date('12042016220000','DDMMYYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')
   and    cxref_status = 'O'
   and    del_flg = 'N'
   and    online_or_batch = 'B';

The date comparison looks wrong, unless you want 1-DEC-2016 00:00:00 to be 'before' 2-JAN-1997 23:59:59. Assuming rcre_time is a date, I think you need to change it to:

and    rcre_time <= to_date('12-04-2016 22:00:00','DD-MM-YYYY HH24:MI:SS')

Also the cursor parameters have the same names as some global variables, which is confusing, although the compiler will use the parameters (I would prefix them cp_ for clarity).

The bulk fetch loop looks odd - you fetch 10 rows at a time until you hit a %notfound but you don't do anything with the data until after the last fetch.

loop
   fetch getdetails bulk collect into cacxref_fetchdata limit 10;
   /* warning: number of records fetched should match size of out_rec variable */
   dbms_output.put_line('inside the bulk collect loop');
   exit when getdetails%notfound;
end loop;

Then you loop through cacxref_fetchdata but you only use the last one (again using a global variable, which could affect subsequent calls).

It's hard to tell without running it through the debugger with your tables and data, but since global variables retain their value between procedure calls and your cursor is only fetched once, I'm guessing some global's value from one run is affecting the logic for the next. I would tidy the code up, make sure variables are only global if they absolutely have to be, and explicitly reinitialise anything that you don't want to keep from the previous run.

PL/SQL: numeric or value error: character string buffer too small

1 Answers