Unable to get large data i.e. XML from oracle table using Procedure

Question

I am tring to create update statement for a column which datatype is CLOB. For this i am fetching XML from table and writing it in oracle console for later use. For some of the data it is working fine. For some i am getting error as

Exception1 : CREATE_UPDATE_XML_QUERY('600264','700009');

--ORA-06502: PL/SQL: numeric or value error

I have changed datatype of V_XML,V_BLOCK nothing has worked

PROCEDURE CREATE_UPDATE_XML_QUERY
(
 MY_ID          NUMBER,
 MY_ID2       NUMBER
) AS
V_SCREEN_VERSION  NUMBER;
V_XML_ID          NUMBER;
V_CNT NUMBER;
V_XML CLOB);
V_BLOCK CLOB;
BEGIN
      SELECT XML,XMLID INTO V_XML,V_XML_ID 
      FROM XML_TABLE WHERE ENC_ID = MY_ID AND SCREEN_ID = MY_ID2 ; ----getting excption

      V_BLOCK := 
      '
      SET SERVEROUTPUT ON;
      DECLARE 
      V_XML CLOB ;

      BEGIN 

      ';

      V_BLOCK := V_BLOCK||'V_XML := '''||V_XML||''';';

      V_BLOCK := V_BLOCK||'

      UPDATE XML_TABLE SET XML = '||'V_XML'||'
      WHERE ENC_ID = '||MY_ID||' AND ENC_TYPE = ''P'' AND SCREEN_ID = '||MY_ID2||' AND XMLID = '||V_XML_ID||';
      --DBMS_OUTPUT.PUT_LINE(''V_XML =>''||V_XML);
      DBMS_OUTPUT.PUT_LINE(''ROWCOUNT =>''||SQL%ROWCOUNT);

      END;
      /';
      DBMS_OUTPUT.PUT_LINE('--Printing Annomous Block the XML :->>');


      DBMS_OUTPUT.PUT_LINE(V_BLOCK);
  EXCEPTION 
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Exception1 : UPDATE_SCREEN_MASTER_XML('''||MY_ID||''','''||MY_ID2||''','''||V_XML_ID||'''); --'||SQLERRM);--'||SQLERRM);

END CREATE_UPDATE_XML_QUERY;

How can i avoid error. Is it because my XML is too big.

Answer 1

Where you get the error depends on the length of your table's XML CLOB value.

If the XML is more than 32k then you'll see the error at line 27 in your code, from trying to concatenate a varchar2 string a number (as @kfinity demonstrated) onto a CLOB; behaviour that isn't explained in the documentation, but is presumably something to do with implicit conversion since just explicitly converting the numbers with to_char(MY_ID) (or to_clob(MY_ID)).

If the XML is less than, but close to, 32k then you'll just scrape past that, but the V_BLOCK CLOB still ends up as greater than 32k, then will still error at line 39 as dbms_output can't handle that.

You can avoid the first problem by using to_char() around the numeric variables, or by using dbms_lob.append instead of concatenation:

...
      V_BLOCK := 
      '
      SET SERVEROUTPUT ON;
      DECLARE 
      V_XML CLOB ;

      BEGIN 

      ';

      dbms_lob.append(V_BLOCK, 'V_XML := '''||V_XML||''';');

      dbms_lob.append(V_BLOCK, '

      UPDATE XML_TABLE SET XML = '||'V_XML'||'
      WHERE ENC_ID = '||MY_ID||' AND ENC_TYPE = ''P'' AND SCREEN_ID = '||MY_ID2||' AND XMLID = '||V_XML_ID||';
      --DBMS_OUTPUT.PUT_LINE(''V_XML =>''||V_XML);
      DBMS_OUTPUT.PUT_LINE(''ROWCOUNT =>''||SQL%ROWCOUNT);

      END;
      /');
...

And you can avoid the second problem, as long as your XML value contains line breaks, by splitting the CLOB into lines, as shown here, but with a slight modification to handle empty lines; with additional variables declared as:

V_BUFFER VARCHAR2(32767);
V_AMOUNT PLS_INTEGER;
V_POS PLS_INTEGER := 1;

then instead of:

  DBMS_OUTPUT.PUT_LINE(V_BLOCK);

you can do:

  WHILE V_POS < length(V_BLOCK) LOOP
    -- read to next newline if there is one, rest of CLOB if not
    IF dbms_lob.instr(V_BLOCK, chr(10), V_POS) > 0 THEN
      V_AMOUNT := dbms_lob.instr(V_BLOCK, chr(10), V_POS) - V_POS;
      IF V_AMOUNT = 0 THEN
        V_BUFFER := null; -- first character is a new line (i.e. a blank line)
      ELSE 
        dbms_lob.read(V_BLOCK, V_AMOUNT, V_POS, V_BUFFER);
      END IF;
      V_POS := V_POS + V_AMOUNT + 1; -- skip newline character
    ELSE
      V_AMOUNT := 32767;
      dbms_lob.read(V_BLOCK, V_AMOUNT, V_POS, V_BUFFER);
      V_POS := V_POS + V_AMOUNT;
    END IF;

    DBMS_OUTPUT.PUT_LINE(V_BUFFER);
  END LOOP;

db<>fiddle

---

@VinayakDwivedi edited to add a function to use instead:

PROCEDURE print_clob_to_output (p_clob IN CLOB)
IS
    v_offset       NUMBER := 1;
    v_chunk_size   NUMBER := 10000;
BEGIN
    LOOP
        EXIT WHEN v_offset > DBMS_LOB.getlength (p_clob);
        DBMS_OUTPUT.put_line (
            DBMS_LOB.SUBSTR (p_clob, v_chunk_size, v_offset));
        v_offset := v_offset + v_chunk_size;
    END LOOP;
END print_clob_to_output;

... but this will introduce extra line breaks every 10000 characters.

---

However, it's worth noting that the PL/SQL block you are generating within that ends up with a line like:

V_XML := '<original xml from table>';

and if that generated code is run it will also error if the original XML is more that 32k. Really that generated code also needs to be broken up to reconstruct your CLOB in chunks - i.e. a loop that takes 32k at a time and concatenates/appends those chunks to reconstitute the full value. It also has whitespace at the start of each line so the DECLARE etc. and more importantly the final / are not at the start of their respective lines, which will also cause problems when trying to run it as-is.

Answer 2

Well, I've come up with a test case to reproduce this (Oracle 12.2.0.1), and you're right, the problem isn't the DBMS_OUTPUT line.

declare
  v_clob clob;
  xmlid number;
begin
  -- initialize clob and make clob a string of length 32768
  dbms_lob.createtemporary(v_clob, true);
  for i in 1..32768 loop
    v_clob := v_clob || 'x';
  end loop;
  dbms_output.put_line(length(v_clob));
  -- testing:
  v_clob := v_clob || 'x'; -- appending a varchar2 works fine
  v_clob := v_clob || xmlid; -- appending a number gives ORA-06502
  v_clob := v_clob || 'x' || xmlid; -- appending a string+number still gives ORA-06502
  v_clob := v_clob || to_clob(xmlid); -- works fine
  dbms_lob.append(v_clob, 'x' || xmlid); -- also works fine
  dbms_output.put_line(length(v_clob));
  dbms_output.put_line(substr(v_clob,1,32767));
end;
/

The problem seems to be that when you concatenate strings with pipes, Oracle can append 2 clobs together if one of them is over 32k, and it can implicitly convert a varchar2 to a clob and append them. But if you try to append a number to a clob over 32k, it fails. It understands how to append varchar2 and number, and clob and clob, and clob and varchar2. But it can't seem to automatically figure out how to do number -> varchar2 -> clob. You can fix it by wrapping the string in to_clob(), avoiding the issue with Oracle's implicit conversion.

Answer 3

Check out: https://www.techonthenet.com/oracle/errors/ora06502.php This suggests that there may be 3 possible causes to this error

1. Number to big - I doubt if this is your issue, as the max numbers are pretty huge
2. Conversion error - you are trying to convert a non number to a number
3. Assigning NULL to a NOT NULL constrained variable - self explantory

without knowing more of the context it is not really possible to determine which of these is your issue.

Hope this helps!