CODEWITHSUNDEEP
sqloracle-databaseplsqlplsqldeveloperclob
Mayank Aggarwal
Mayank Aggarwal

Reputation: 35

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

Upvotes: 2

Views: 1926

Answers (2)

William Robertson
William Robertson

Reputation: 15991

By the way, the code can be simplified to:

declare
    type         clob_array is table of clob;
    index_array  clob_array := clob_array();
begin
    for r in (
        select replace(dbms_metadata.get_ddl('INDEX', index_name), '"C",') as index_ddl
        from   user_indexes
        where  table_name = 'A'
    )
    loop
        index_array.extend;
        index_array(index_array.count) := r.index_ddl;
        dbms_output.put_line(substr(index_array(index_array.count), 1, 32767));
    end loop;
end;

I used substr() to limit the value passed to dbms_output.put_line to its documented limit. You could probably work around it by splitting the text into smaller chunks, and maybe finding the position of the last blank space before position 32767 in order to avoid splitting a word.

Here's what I came up with:

declare
    type         clob_array is table of clob;
    index_array  clob_array := clob_array();

    procedure put_line
        ( p_text clob )
    is
        max_len constant simple_integer := 32767;
        line varchar2(max_len);
        remainder clob := p_text;
    begin
        while dbms_lob.getlength(remainder) > max_len loop
            line := dbms_lob.substr(remainder,max_len);
            line := substr(line, 1, instr(line, ' ', -1));
            remainder := substr(remainder, length(line) +1);
            dbms_output.put_line(line);
        end loop;
        
        if length(trim(remainder)) > 0 then
            dbms_output.put_line(remainder);
        end if;
    end put_line;
begin
     for r in (
        select replace(dbms_metadata.get_ddl('INDEX', index_name), '"C",') as index_ddl
        from   user_indexes
        where  table_name = 'A'
    )
    loop
        index_array.extend;
        index_array(index_array.count) := r.index_ddl;
        put_line(index_array(index_array.count));
    end loop;
end;

Upvotes: 0

psaraj12
psaraj12

Reputation: 5072

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

Upvotes: 1

Related Questions