超过4000个字符的Substr将获得ORA-06502:PL/SQL:数值或值错误

Question

Create or replace function GET_AER_TEXT5(M_AER_ID IN NUMBER,
  F_ID IN VARCHAR2,PREF_LANGUAGE IN VARCHAR2)
IS
  AERTEXT VARCHAR2(32000);
  LANG_PARAM VARCHAR2(2000);
  AER_CLOB CLOB;
BEGIN
  FOR c1 IN (
    select TEXT from AER_TEXT
    where FIELD_ID=F_ID and AER_ID=M_AER_ID and LANGUAGE IN(PREF_LANGUAGE)
  )
  LOOP
    IF c1.text IS NOT NULL THEN
      AER_CLOB:=AER_CLOB || c1.text;
    END IF;
  END LOOP;
  AERTEXT:=substr(AER_CLOB,1,4000);
  RETURN(AERTEXT);
END;

select AER_ID,GET_AER_TEXT5(AER_ID,at,field_id,'001')
from AER a,AER_TEXT AT
where AT.field_ID=12345 and a.aer_id=at.aer_id;

你能告诉我如何解决这个问题吗？

Answer 1

在Oracle12c之前，Oracle只允许在SQL上下文中的varchar2值中包含4000字节。在PL/SQL中允许使用32K，因此，即使substrng获得前4001个字符，您的函数也是可以的；但前提是从PL/SQL调用它。当您尝试从SQL调用它时：

select AER_ID,GET_AER_TEXT5(AER_ID,at,field_id,'001') ...

...您试图为return语句中的隐式SQL列分配一个4001字符的值，这导致了您所看到的错误。

您可以将SAP调用更改为使用PL/SQL上下文和绑定变量来获取返回值，但仍将限制在32K；或者更改函数以将值保持为CLOB，这使得函数变得毫无意义，因为您只能从表中获取值。我不熟悉SAP，所以我不太确定您最终会如何编写这两种方法。