How to fix Character string buffer too small error in OUT variable in Stored Procedure

Question

I`m trying to use OUT variable to send a SQL result but I received "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" error, I try to change o_sql from varchar2 to nvarchar2 but still have a problem and also seems cannot define it as varchar2(10000) on in/out section. in the first part, I received time from i_In and o_sql + o_file for the caller of the procedure, also I remove the exception part bcz don't have any problem on it.

CREATE OR REPLACE PROCEDURE P_C_CCT_QADATA(i_In      in  varchar2,
    o_sql     out nvarchar2,  --in here i try to change it to varchar2(1000) 
    --but not working and 
    o_File    out varchar2
    )
AS
v_date        DATE;
v_ERRCODE     T_C_CCT_RESULTMSG.code%TYPE;
v_ERRMSG      T_C_CCT_RESULTMSG.MESSAGE%TYPE;
v_month       varchar(2);

Begin

v_date := TO_DATE(i_In,'YYYYMMDDHH24MISS');
select to_char(sysdate, 'MM') into v_month from dual;


o_sql  := 'select t.SERIALNO,
t.CONTACTID,
t.CONTACTCHANNELID,
t.CONTACTCHANNELNAME,
t.CONTACTMODEID,
t.CONTACTMODENAME,
t.SUBCCNO,
t.VDNID,
t.HOSTEDCCID,
t.CALLID,
t.CALLTYPE,
t.LANGUAGETYPEID,
t.LANGUAGETYPENAME,
t.CALLSKILLID,
t.CALLSKILLDESC,
t.CALLERNO,
t.CALLEDNO,
t.SUBSNUMBER,
t.ORGCALLERNO,
t.ORGCALLEDNO,
t.MEDIATYPEID,
t.MEDIATYPENAME,
t.CALLSTARTTIME,
t.CALLDURATION,
t.STAFFID,
t.CUSTCITYID,
t.SERVICECITYID,
t.STAFFCITYID,
t.SUBSCITYID,
t.SUBSNAME,
t.SUBSLEVELID,
t.SUBSLEVELNAME,
t.SUBSBRANDID,
t.SUBSBRANDNAME,
t.SUBSPHONE1,
t.SUBSPHONE2,
t.SUBSFAXNO,
t.SUBSEMAIL,
t.SUBSADDRESS,
t.CUSTID,
t.CUSTNAME,
t.CUSTLEVELID,
t.CUSTLEVELNAME,
t.CUSTBRANDID,
t.CUSTBRANDNAME,
t.CUSTPHONE1,
t.CUSTPHONE2,
t.CUSTFAXNO,
t.CUSTEMAIL,
t.CUSTADDRESS,
t.LINKMODE,
t.LINKMAN,
t.LINKINFO,
t.LINKADDRESS,
t.REMARK,
t.CONTACTSTARTTIME,
t.CONTACTDURATION,
t.PLAYRECORDFLAG,
t.QCFLAG,
t.EVTERID,
t.HAVESERVICEFLAG,
t.INTERCEPTFLAG,
t.STAFFHANGUP,
t.SURVEYTYPEID,
t.USERSATISFY,
t.SATISFYFILEPATH,
t.LISTENFLAG,
t.INNERHELPFLAG,
t.PICKUPSTAFFID,
t.MAINCONTACTFLAG,
t.CALLTRACK,
t.DIGITCODE,
t.EXPFLAG,
t.HASRECORDFILE,
t.ISPROCESSED,
t.TENANTID,
b.FILENAME
from T_CCT_CONTACTDETAIL t, trecordinfo'||v_month||'@icddb b
where  b.CALLID = t.CALLID
and t.CALLSTARTTIME > (sysdate- 8/24)
and t.CALLSTARTTIME <= (sysdate- 2/24))';
o_file := 'CONTACTDETAIL' || TO_CHAR(v_date, 'YYYYMMDDHH');

END P_C_CCT_QADATA;

Answer 1

--in here i try to change it to varchar2(1000)
--but not working

PL/SQL parameters don't take length restrictions. So varchar2(1000) won't compile as a parameter definition.

The problem lies in the size of the variable you assign to o_sql when you call this procedure. That needs to be big enough to hold the string. Your string is about 1330 characters long which means varchar2(1000) is way too short for your needs.

Try something like:

declare
    l_sql nvarchar2(1600); -- big enough to hold teh output value!
    l_file varchar2(32);
begin
    P_C_CCT_QADATA(i_In   => 'whatever',
                   o_sql  => l_sql, 
                   o_File => l_file);
end;
/

Answer 2

The problem is not in your procedure, but it whatever calls your procedure. Go to wherever your procedure is being called from, find the variable which is passed to your procedure as the output parameter, and make that variable larger. Defining it as VARCHAR2(32767), the maximum possible in PL/SQL, seems reasonable.

Answer 3

i have compiled your code into my schema and i'm able to execute it, try something like:

 declare

p_sql varchar2(5000);

p_File varchar2(5000);

 begin

    P_C_CCT_QADATA(12412342134,p_sql,p_File);

    dbms_output.put_line(p_sql);

   end;