ORA-06502: PL/SQL: numeric or value error: host bind array too small

I was running anon PLSQL block on SQL prompt, and it was printing a large varchar2(32767) variable. And then I got this error. The data length in the varchar2 variable was just 1200, and it was throwing this error.

Turned out the issue is the with the DBMS_OUTPUT.PUT_LINE, it wont accept more than 200 chars to print. So if you have a larger data to be printed using DBMS_OUTPUT.PUT_LINE, then break it down and print 200 chars at a time.

You can use following outputter to break down big variables.

PROCEDURE print_big_text(bigTextInput VARCHAR2)
  IS
    ln_maxChar NUMBER := 200;
    ln_schar   NUMBER;
    bigText    VARCHAR2(32767) := bigTextInput;
  BEGIN
    LOOP
      EXIT WHEN bigText IS NULL;
      ln_schar := INSTR(bigText, ' ', ln_maxChar);
      -- Find first space
      IF ln_schar = 0 THEN
        ln_schar := ln_maxChar + 1;
      END IF;
      -- If no space, just print first 200 
      dbms_output.put_line( SUBSTR(bigText, 1, ln_schar - 1) );
      -- Move text by ln_schar ahead
      bigText := SUBSTR(bigText, ln_schar);
    END LOOP;
  END print_big_text;