Handle a very large string in pl/sql script

I am trying to run below code which reads the index definition for table A so that it can be created again after I delete/create that in this script. This script runs fine when the returned value(ddl) is small but in other environments where the value is large with 140K characters in one row this script fails with below mentioned error. Please note that I cannot use spool in this case due to some restrictions. Could someone help on how to resolve this issue or suggest some another approach?

Thanks in advance.

“An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).”

SET SERVEROUTPUT ON;
DECLARE 
my_cursor SYS_REFCURSOR;
TYPE clob_array IS VARRAY(15) OF CLOB;
index_array clob_array := clob_array();
v_clob CLOB;
--index_array SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
BEGIN
OPEN my_cursor FOR 'select replace(dbms_metadata.get_ddl (''INDEX'', index_name), ''"C",'', '''')
              from user_indexes
             where table_name = ''A''';

LOOP FETCH my_cursor INTO v_clob;
EXIT WHEN my_cursor%NOTFOUND;
index_array.extend;
index_array(index_array.count) := v_clob;
dbms_output.put_line(index_array(index_array.count));
END LOOP;
CLOSE my_cursor;
END;
/

Answer

I simulated this issue you are getting this error because of the dbms_output.put_line which displays the output.Try switching to UTL_FILE at the server side OR Try for any alternatives

Advertisement

Answer