Search code examples
oracleplsqloracle11gplsqldeveloperdbms-output

dbms_output.put_line in PLSQL Developer fails with ORA-06502 for strings longer than 32512 chars


This script fails in PLSQL Developer version 14.0.0.1961 (error ORA-06502: PL/SQL: numeric or value error: character string buffer too small):

declare
  v varchar2(32767) := rpad('x',32513,'x');
begin
  dbms_output.enable(null);
  dbms_output.put_line(v);
end;
/
  • If 32513 is replaced by higher value, it fails too.
  • If 32513 is replaced by lower value, it works (i.e. prints text without failure).
  • In SQL*Plus and Toad it works for up to 32767 (this is expected).
  • In Intellij Idea it works for up to 32766, for 32767 it does not raise exception though prints nothing.

How can I explain such behaviour? The 32512 seems to be interesting constant (I found it in some APEX question, question about blobs and is also mentioned in JDBC tutorial but I miss any connection to described problem.)


Solution

  • Looks like a PL/SQL Developer bug.

    In 2013, they said they'll fix it:

    it's a bit of a low-level issue, so his may need to wait until version 11.0.

    Which version do you use?