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.
