PL/SQL - prevent ORA-06502

Question

I frequently get this error (it is so annoying!):

Error report: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 305 06502. 00000 - "PL/SQL: numeric or value error%s"

Example stored procedure is looping through a cursor (that has approx 10k rows), doing some logic and then using dbms_output.put_line to print each record to the 'Script Output' tab in Oracle SQL Developer.

Is there a way to flush out the the buffer or prevent this error coming up (and aborting the rest of the proc)?

If that is possible I could use the Mod function to clear the buffer every 10 or so rows processed.

Answer 1

The default buffer size of DBMS_OUTPUT is 20000 chars. You can up this with:

DBMS_OUTPUT.ENABLE(1000000);

There's no concept of a "flush" as there is nothing to flush to until the stored procedure returns. The way it actually works is that the lines go into a buffer in the DB, then that buffer is explicitly fetched (with DBMS_OUTPUT.GET_LINES) by the client (e.g. SQL*Plus) when the SP completes. It is quite unlike printf() that you might be used to in C.

Answer 2

I don't believe this error has anything to do with the DBMS_OUTPUT buffer size as the other answers imply. If you overflow that buffer, the error you get should be "ORA-20000: ORU-10027: buffer overflow, limit of ##### bytes".

This error usually means that a PL/SQL string variable is too small to hold some value that is assigned to it. This could be internal to DBMS_OUTPUT, if you are passing a value that is larger than it can handle. But your stack trace does now show DBMS_OUTPUT in the stack, so I think that is not the case. The error appears to be occurring on line 305 of your procedure. What does it do? If it's an assignment, then the variable begin assigned probably needs to be declared with a bigger size.

Answer 3

To output large amounts of stuff, it's better to use UTL_FILE instead of DBMS_OUTPUT and check the file. Or use a temporary table and do the insert in an autonomous procedure, so you can see the result even if the main procedure has to do a rollback for whatever reason. In both cases, you have the additional advantage of seeing a part of the result while your procedure is still running; that's something DBMS_OUTPUT is not able to provide.

Answer 4

Receive the value as CLOB not varchar