Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table xml_table (enc_id,screen_id,xmlid,xml) as
select 1, 2, 3,
  to_clob(dbms_random.string('p', 4000)) || chr(10) || to_clob(dbms_random.string('p', 4000))
  || chr(10) || to_clob(dbms_random.string('p', 4000)) || chr(10) || to_clob(dbms_random.string('p', 4000))
  || chr(10) || to_clob(dbms_random.string('p', 4000)) || chr(10) || to_clob(dbms_random.string('p', 4000))
  || chr(10) || to_clob(dbms_random.string('p', 4000)) || chr(10) || to_clob(dbms_random.string('p', 4000))
  || chr(10) || to_clob(dbms_random.string('p', 666))
from dual
union all
select 2, 3, 4,
  to_clob(dbms_random.string('p', 4000)) || chr(10) || to_clob(dbms_random.string('p', 4000))
  || chr(10) || to_clob(dbms_random.string('p', 4000)) || chr(10) || to_clob(dbms_random.string('p', 4000))
  || chr(10) || to_clob(dbms_random.string('p', 4000)) || chr(10) || to_clob(dbms_random.string('p', 4000))
  || chr(10) || to_clob(dbms_random.string('p', 4000)) || chr(10) || to_clob(dbms_random.string('p', 4000))
  || chr(10) || to_clob(dbms_random.string('p', 424))
from dual;
2 rows affected

create or replace 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;
/
-- querying XML value which is longer than 32k
-- errors on line 27
BEGIN
  CREATE_UPDATE_XML_QUERY(1,2);
END;
/
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "FIDDLE_QTXVUTEJFIBEUZYLFCUZ.CREATE_UPDATE_XML_QUERY", line 27
ORA-06512: at line 4

-- querying XML value which is shorter than 32k, but totals more than 32k in v_block
-- errors on line 39
BEGIN
  CREATE_UPDATE_XML_QUERY(2,3);
END;
/


dbms_output:
--Printing Annomous Block the XML :->>

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "FIDDLE_QTXVUTEJFIBEUZYLFCUZ.CREATE_UPDATE_XML_QUERY", line 39
ORA-06512: at line 4

create or replace 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;
V_BUFFER VARCHAR2(32767);
V_AMOUNT PLS_INTEGER;
V_POS PLS_INTEGER := 1;
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 

      ';

      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;
      /');
      DBMS_OUTPUT.PUT_LINE('--Printing Annomous Block the XML :->>');

      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;

--  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;
/
-- querying XML value which is longer than 32k
-- no errors
BEGIN
  dbms_output.enable(null); -- limited to 20000 bytes by default
  CREATE_UPDATE_XML_QUERY(1,2);
END;
/
1 rows affected

-- querying XML value which is shorter than 32k, but totals more than 32k in v_block
-- no errors
BEGIN
  dbms_output.enable(null); -- limited to 20000 bytes by default
  CREATE_UPDATE_XML_QUERY(2,3);
END;
/
1 rows affected