Oracle 超过varchar2限制时Clob数据类型错误

PL/SQL程序中的Clob数据类型在超过varchar2限制并给出 “错误: ORA-06502: PL/SQL: 数值或值错误” 时会出现异常，为什么Clob数据类型表现得像varchar2数据类型。我认为clob可以容纳4 GB的数据。请帮我找出根本原因。

DECLARE

  t_Sql_Stmt           CLOB DEFAULT Empty_Clob();
  t_No_Of_Days_Pending NUMBER := 0;
  Ip_Period_Name       VARCHAR2(10) := 'SEP-18';

BEGIN

  FOR i IN (SELECT *
              FROM (SELECT Gjb.Default_Period_Name Period_Name,
                           'Test' Ledger_Name,
                           Gjb.Name Batch_Name,
                           'Required' Batch_Approval_Status,
                           Gjb.Created_By Batch_User_Name,
                           Gjb.Created_By Batch_User_Id,
                           Gjb.Created_By Created_By,
                           Gjb.Status Batch_Status,
                           'test@test.com' Email_Address,
                           Gjb.Je_Batch_Id Batch_Id,
                           Nvl(Round((SYSDATE - Gjb.Creation_Date), 2) * 24,
                               0) Hrs_Outstanding,
                           Gjb.Approval_Status_Code,
                           Decode(Gjb.Status, 'U', 'Unposted', 'Posted') Batch_Status_Desc,
                           NULL Approver_Email_Id,
                           'Not Submitted' Approver,
                           To_Char(Gjb.Creation_Date,
                                   'MM/DD/YYYY HH:MI:SS AM') || ' Phx Time' Creation_Date,
                           NULL Submission_Date,
                           NULL Sub_Hrs_Outstanding
                      FROM Gl_Je_Batches Gjb
                     WHERE Gjb.Default_Period_Name = Ip_Period_Name
                       AND Gjb.Approval_Status_Code = 'R'
                       AND Gjb.Status = 'U'
                       AND (SYSDATE - Gjb.Creation_Date) >
                           t_No_Of_Days_Pending)
             WHERE Rownum <= 130) LOOP
  
    BEGIN
    
      t_Sql_Stmt := t_Sql_Stmt || ' select ' || '''' || i
                   .Period_Name || '''' || ' ||' || ' ''~'' ' || '|| ' || '''' ||
                    i.Ledger_Name || '''' || ' ||' || ' ''~'' ' || '|| ' || '''' || i
                   .Batch_Name || '''' || ' ||' || ' ''~'' ' || '|| ' || '''' || i
                   .Batch_Approval_Status || '''' || ' ||' || ' ''~'' ' ||
                    '|| ' || '''' || i
                   .Batch_User_Name || '''' || ' ||' || ' ''~'' ' || '|| ' || '''' ||
                    i.Batch_User_Id || '''' || ' ||' || ' ''~'' ' || '|| ' || '''' || i
                   .Created_By || '''' || ' ||' || ' ''~'' ' || '|| ' || '''' ||
                    i.Approver || '''' || ' ||' || ' ''~'' ' || '|| ' || '''' || i
                   .Hrs_Outstanding || '''' || ' from dual ';
    
      Dbms_Output.Put_Line('No Problem Batch name' || i.Batch_Name);
      Dbms_Output.Put_Line('Length' || Length(t_Sql_Stmt));
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
        --dbms_output.put_line('In Exception');
        Dbms_Output.Put_Line('Problem with Batch_name: ' || i.Batch_Name);
        Dbms_Output.Put_Line('Error: ' || SQLERRM);
        Dbms_Output.Put_Line('Length' || Length(t_Sql_Stmt));
    END;
  END LOOP;

END;

set serverout on
declare
  c clob;
begin
  c := 'some chars';

  for i in 1 .. 24 loop
    c := c || c;
    dbms_output.put_line('C is now '||length(c));
  end loop;
end;
/

下面是您提供的代码的输出

C现在是20
C现在是40
C现在80了
C现在是160
C现在是320
C现在是640
C现在是1280
C现在是2560
C现在是5120
C现在是10240
C现在是20480
C现在是40960
C现在是81920
C现在是163840
C现在是327680
C现在是655360
C现在是1310720
C现在是2621440
C现在是5242880
C现在是10485760
C现在是20971520
C现在是41943040
C现在是83886080
C现在是167772160

您的数据类型转换有点松散。

如果我们要全面处理varchar2，我们可以将其映射到clob，例如

SQL> DECLARE
  2    t_Sql_Stmt           CLOB DEFAULT Empty_Clob();
  3  BEGIN
  4   FOR i IN (SELECT *
  5             from dba_objects
  6             where rownum < 200000 )
  7   LOOP
  8        t_Sql_Stmt := t_Sql_Stmt ||
  9        i.owner ||
 10        i.object_name ;
 11    END LOOP;
 12    Dbms_Output.Put_Line('Length ' || Length(t_Sql_Stmt));
 13
 14  END;
 15  /
Length 2945946

PL/SQL procedure successfully completed.

但是，如果您在混合中加入一些non-varchar2，那么我们将尝试将整个表达式转换为varchar2，以使其正常工作... 然后根据varchar2进入长度限制，例如

SQL> DECLARE
  2    t_Sql_Stmt           CLOB DEFAULT Empty_Clob();
  3  BEGIN
  4   FOR i IN (SELECT *
  5             from dba_objects
  6             where rownum < 200000 )
  7   LOOP
  8        t_Sql_Stmt := t_Sql_Stmt ||
  9        i.owner ||
 10        i.object_name || i.object_id;   --- object_id is numeric
 11    END LOOP;
 12    Dbms_Output.Put_Line('Length' || Length(t_Sql_Stmt));
 13
 14  END;
 15  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 8
ORA-06512: at line 8

所以只要确保你对你的数据类型是明确的，例如

SQL> DECLARE
  2    t_Sql_Stmt           CLOB DEFAULT Empty_Clob();
  3  BEGIN
  4   FOR i IN (SELECT *
  5             from dba_objects
  6             where rownum < 200000 )
  7   LOOP
  8        t_Sql_Stmt := t_Sql_Stmt ||
  9        i.owner ||
 10        i.object_name || cast(i.object_id as varchar2);
 11    END LOOP;
 12    Dbms_Output.Put_Line('Length ' || Length(t_Sql_Stmt));
 13
 14  END;
 15  /
Length 3333231

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    t_Sql_Stmt           CLOB DEFAULT Empty_Clob();
  3  BEGIN
  4   FOR i IN (SELECT *
  5             from dba_objects
  6             where rownum < 200000 )
  7   LOOP
  8        t_Sql_Stmt := t_Sql_Stmt ||
  9        i.owner ||
 10        i.object_name || to_char(i.object_id);
 11    END LOOP;
 12    Dbms_Output.Put_Line('Length ' || Length(t_Sql_Stmt));
 13
 14  END;
 15  /
Length 3333231

PL/SQL procedure successfully completed.