5

I am getting an error in an Oracle 11g stored procedure. The error is...

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

It is happening at line 31, the line that contains out_cnt_tot := 0; I'm really not sure why there is anything wrong with that line. Another programmer created this procedure and I'm really not familiar with SQL procedures. Can anyone help me figure this out?

create or replace 
PROCEDURE                  "FIP_BANKREC_PREP" 
                   (
    in_file_date in varchar2,
    in_bank_code in varchar2,
    out_cnt_apx_miss_no out integer,
    out_cnt_prx_miss_no out integer,
    out_cnt_apx_no_mtch out integer,
    out_cnt_prx_no_mtch out integer,
    out_cnt_ap_dup out integer,
    out_cnt_pr_dup out integer,
    out_cnt_bad out integer,
    out_cnt_ap_load out integer,
    out_cnt_pr_load out integer,
    out_cnt_ap_not_load out integer,
    out_cnt_pr_not_load out integer,
    out_cnt_tot out integer,
    out_message out varchar2
    ) as

file_date date;
ap_acct_no varchar2(16);
pr_acct_no varchar2(16);

-- ------------------------------------------------------
--  begin logic
-- ------------------------------------------------------
begin

  file_date := to_date(in_file_date,'yyyymmdd');
  out_cnt_tot := 0;   --- THE ERROR IS ON THIS LINE ---
  out_message := 'Test Message';

  select brec_acct_code into ap_acct_no 
    from MSSU.zwkfi_bankrec_accts
    where brec_acct_bank = in_bank_code
      and brec_acct_type = 'AP';

  select brec_acct_code into pr_acct_no 
    from MSSU.zwkfi_bankrec_accts
    where brec_acct_bank = in_bank_code
      and brec_acct_type = 'PR';      

// The rest of the procedure...
Improve this question
5
  • How do you know it's line 31? Are you sure? Did you try to look at the file with hex editor? Maybe you can spot some UTF-8 character which is invisible in your editor but causes problems. Commented Jul 11, 2013 at 16:55
  • 2
    It looks rather more likely to be the line after that; line numbers in these messages are sometimes not quite what you'd expect. Which means it's probably the size of the variable you're passing in to the procedure as out_message that is too small, rather than an error in the procedure itself. Can you show how you're calling this, and how variables are declared for that call? Commented Jul 11, 2013 at 17:06
  • The only thing passed in to out_message is that "Test Message" literal. We are going to be adding messages shortly, but hadn't gotten around to it yet so we just put some placeholder text in there. Is it possible that the string "Test Message" is too short for that variable? I've never heard of that happening before. Commented Jul 11, 2013 at 17:49
  • You're setting out_message to 'Test Message'. That's fine. But the size of the out_message variable (the 'string buffer' in the error message) is determined by whatever calls this procedure. If it's defined there as, say, varchar2(10) then the assignment will fail, as the value is 12 characters. That's why I was asking about the caller. Commented Jul 11, 2013 at 17:58
  • That makes sense. Some C# code in an ASP.NET application is calling this procedure. The out_str_message OracleParameter object does not have a size declared in my code. It is just this: OracleParameter prm15 = new OracleParameter("out_str_message", OracleDbType.Varchar2); prm15.Direction = ParameterDirection.Output; cmdLoad.Parameters.Add(prm15); Commented Jul 11, 2013 at 18:05

1 Answer 1

Reset to default
11

Simple demo of the scenario mentioned in comments:

create or replace procedure p42(out_message out varchar2) as
begin
  out_message := 'Test message';
end p42;
/

If I call that with a variable that is declared big enough, it's fine. I have a 12-char variable, so assigning a 12-char value is not a problem:

declare
  msg varchar2(12);
begin
  p42(msg);
end;
/

anonymous block completed

But if I make a mistake and make the caller's variable too small I get the error you're seeing:

declare
  msg varchar2(10);
begin
  p42(msg);
end;
/

Error report:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "STACKOVERFLOW.P42", line 3
ORA-06512: at line 4
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    
*Action:

The error stack shows both the line in the procedure that errored (line 3), and the line in the caller that triggered it (line 4). Depending on where you're calling it you might not have the whole stack, of course.

You mentioned that there would be various error messagesin the future. You need to make sure that anything that ever calls this defines the variables to be big enough to cope with any of your messages. If they were stored in a table you could semi-automate that, otherwise it'll be a manual code review check.

OK, saw your c# comment after posting this. It looks like you're calling this constructor; that doesn't say what default size it gets, but not unreasonable to think it might be 1. So you need to call this constructor instead to specify the size explicitly:

OracleParameter(String, OracleType, Int32)
Initializes a new instance of the OracleParameter class that uses the parameter name, data type, and length.

... something like:

OracleParameter prm15 = new OracleParameter("out_str_message",
    OracleDbType.Varchar2, 80);

Unless there's a way to reset the size after creation, which I can't see. (Not something I've ever used!).

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

It looks like adding a length of 12 to my OracleParameter() constructor fixes this issue. Thanks a million for your help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.