CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Syan
Syan

Reputation: 137

ORA-06502: I take CLOB datatype variable still getting this error

Error report - ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 19 06502. 00000 - "PL/SQL: numeric or value error%s"

IF I COMMENT LINE NUMBER 23 & 24 I'LL GET OUTPUT WHILE I UN-COMMENT I'M GETTING ABOVE ERROR WHILE I TAKE CLOB DATATYPE

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

Upvotes: 1

Views: 3887

Answers (1)

alexgibbs
alexgibbs

Reputation: 2480

Though you are dealing with CLOB datatype, the concatenations are producing literals (max length 32,767 bytes), and PL/SQL won't allow overshooting this max during literal concatenation.

If TESTLEI has enough rows, the length of V_MESSAGEATT in one of the concatenations goes over this limit, and you are given the ORA-06502. You can see this in action, if you add an exception handler like the below at the end of your block:

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

When you run the above, you should get a message like the following, with a number just over 32767:

Failed to build the attachment:32804

To allow the full attachment to build, you can take measures to avoid literal concatenation. Here's an example that uses CONCAT instead. (But note! you also can't DBMS_OUTPUT a huge CLOB either, so this example catches that exception too and lets you know.

First make test data:

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;

Now we have 999 rows.

Now modify the block to only use || concatenation on small strings. use CONCAT instead on big strings:

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

You can't print the CLOB as-is; if you really want to print it, you'd need to break it up. But if you are really planning to do something like send to another function, you should be good to go.

When you run the above, you should get something like:

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

Upvotes: 4

Related Questions