Oracle存储过程中的字符串缓冲区太小错误 [英] Character string buffer too small error in Oracle Stored Procedure

问题描述

我在 Oracle 11g 存储过程中遇到错误.错误是...

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

ORA-06502:PL/SQL:数值或值错误:字符串缓冲区太小

它发生在第 31 行，该行包含 out_cnt_tot := 0; 我真的不知道为什么那行有什么问题.另一个程序员创建了这个过程，我真的不熟悉 SQL 过程.谁能帮我解决这个问题?

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...

推荐答案

评论中提到的场景的简单演示:

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;
/

如果我用一个声明足够大的变量来调用它，那就没问题了.我有一个 12 字符的变量，所以分配一个 12 字符的值不是问题:

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:

错误堆栈显示过程中出错的行(第 3 行)和触发它的调用者中的行(第 4 行).当然，根据您调用它的位置，您可能没有整个堆栈.

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.

好的，在发布这个之后看到你的 c# 评论.看起来你正在调用这个构造函数；这并没有说明它得到的默认大小是多少，但认为它可能是 1 并不是没有道理的.所以你需要调用 此构造函数 改为显式指定大小:

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)
初始化使用参数名称的 OracleParameter 类的新实例，数据类型和长度.

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

...类似:

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!).

这篇关于Oracle存储过程中的字符串缓冲区太小错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！