ORA-06502:我接受CLOB datatype变量仍然得到此错误

Question

SET SERVEROUTPUT ON;

declare
    v_messageatt      CLOB;
    attachment_text   CLOB;
    CURSOR main_cursor IS
        SELECT custid, custname, email
      from testlei; 

    main_cursor_var   main_cursor%rowtype;
BEGIN
    OPEN main_cursor;
    LOOP
        FETCH main_cursor INTO main_cursor_var;
        EXIT WHEN
            ( main_cursor%notfound );
        IF main_cursor%rowcount = 1 THEN
            v_messageatt := v_messageatt || '<tr><th>user ID</th><th>USERNAME</th><th>EMAILID</th></tr>';
        END IF;
        v_messageatt := v_messageatt || '<tr>';
        v_messageatt := v_messageatt||  '<td>'||  main_cursor%rowcount||  '</td>';
        v_messageatt        := v_messageatt || '<td>'||main_cursor_var.custid||'</td>';
        v_messageatt        := v_messageatt || '<td>'||main_cursor_var.custname||'</td>';
        v_messageatt        := v_messageatt || '<td>'||main_cursor_var.email||'</td>';                   
        v_messageatt := v_messageatt || '</tr>';
        attachment_text := v_messageatt;
    END LOOP;

    CLOSE main_cursor;
    dbms_output.put_line(attachment_text);
END;
/

Answer 1

尽管您正在处理CLOBdatatype，但连接会产生literals（最大长度32,767字节），并且pl/sql在literal连接期间不允许超调这个最大长度。

如果testlei有足够的行，那么其中一个连接中v_messageatt的长度超过了这个限制，就会给出ORA-06502。如果在块的末尾添加如下所示的异常处理程序，您可以看到这一点：

declare
    v_messageatt      CLOB;
    attachment_text   CLOB;
...
... rest of block here ...
...

  EXCEPTION WHEN OTHERS THEN
  DECLARE
    V_ATTACH_ERROR_CHAR_COUNT NUMBER;
  BEGIN
    V_ATTACH_ERROR_CHAR_COUNT := DBMS_LOB.getlength(V_MESSAGEATT);
    DBMS_OUTPUT.PUT_LINE('Failed to build the attachment:' || V_ATTACH_ERROR_CHAR_COUNT);
  END;

END;
/

当您运行上面的命令时，您应该会得到如下所示的消息，其中的数字正好位于32767:

Failed to build the attachment:32804

要允许构建完整附件，可以采取措施避免文本串联。下面的示例使用concat。（但是请注意！您也不能dbms_output一个巨大的clob，所以这个示例也捕获了这个异常并让您知道。

首先制作测试数据：

CREATE TABLE TESTLEI (
  CUSTID   NUMBER,
  CUSTNAME VARCHAR2(128),
  EMAIL    VARCHAR2(128)
);

INSERT INTO TESTLEI SELECT
                      LEVEL,
                      'Person' || LEVEL,
                      'email' || LEVEL || '@email.abc'
                    FROM DUAL
                    CONNECT BY LEVEL < 1000;

现在修改该块，使其只对小字符串使用级联。对大字符串使用concat:

DECLARE
  V_MESSAGEATT    CLOB;
  ATTACHMENT_TEXT CLOB;
  V_CHAR_COUNT    NUMBER;

  CURSOR MAIN_CURSOR IS
    SELECT
      CUSTID,
      CUSTNAME,
      EMAIL
    FROM TESTLEI;

  MAIN_CURSOR_VAR MAIN_CURSOR%ROWTYPE;
BEGIN
  OPEN MAIN_CURSOR;
  LOOP
    FETCH MAIN_CURSOR INTO MAIN_CURSOR_VAR;
    EXIT WHEN
      (MAIN_CURSOR%NOTFOUND);
    IF MAIN_CURSOR%ROWCOUNT = 1
    THEN
      V_MESSAGEATT := V_MESSAGEATT || '<tr><th>user ID</th><th>USERNAME</th><th>EMAILID</th></tr>';
    END IF;
    V_MESSAGEATT := CONCAT(V_MESSAGEATT, '<tr>');
    V_MESSAGEATT := CONCAT(V_MESSAGEATT, '<td>' || MAIN_CURSOR%ROWCOUNT || '</td>');
    V_MESSAGEATT := CONCAT(V_MESSAGEATT, '<td>' || MAIN_CURSOR_VAR.CUSTID || '</td>');
    V_MESSAGEATT := CONCAT(V_MESSAGEATT, '<td>' || MAIN_CURSOR_VAR.CUSTNAME || '</td>');
    V_MESSAGEATT := CONCAT(V_MESSAGEATT, '<td>' || MAIN_CURSOR_VAR.EMAIL || '</td>');
    V_MESSAGEATT := CONCAT(V_MESSAGEATT, '</tr>');
    ATTACHMENT_TEXT := V_MESSAGEATT;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Finished building attachment!!');

  CLOSE MAIN_CURSOR;

  DECLARE
    V_PRINT_ERROR_CHAR_COUNT NUMBER;
  BEGIN
    --The clob is too big to print.  This will fail.
    DBMS_OUTPUT.put_line(ATTACHMENT_TEXT);
    EXCEPTION WHEN OTHERS THEN
    V_PRINT_ERROR_CHAR_COUNT := DBMS_LOB.getlength(V_MESSAGEATT);
    DBMS_OUTPUT.PUT_LINE('The attachment was too long to print in DBMS_OUTPUT.  Length:' || V_PRINT_ERROR_CHAR_COUNT);
  END;

  EXCEPTION WHEN OTHERS THEN
  DECLARE
    V_ATTACH_ERROR_CHAR_COUNT NUMBER;
  BEGIN
    V_ATTACH_ERROR_CHAR_COUNT := DBMS_LOB.getlength(V_MESSAGEATT);
    DBMS_OUTPUT.PUT_LINE('Failed to build the attachment:' || V_ATTACH_ERROR_CHAR_COUNT);
  END;

END;
/

不能按原样打印clob；如果你真的想打印它，你需要把它拆开。但是如果你真的打算做一些事情，比如发送到另一个函数，你应该很好地去做。

当您运行上面的内容时，您应该得到如下内容：

Finished building attachment!!
The attachment was too long to print in DBMS_OUTPUT.  Length:77548
PL/SQL procedure successfully completed.