Im facing below exception in Oracle DB.
java.lang.RuntimeException: java.lang.RuntimeException: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [call SRS_ADMIN.SLI_UTIL.copyData(?,?)]; nested exception is java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
I want command to know the size of DBMS_OUTPUT.
The ORA-06502 error you're getting is nothing to do with dbms_output. You get this when you have a varchar2 variable and you try to put a value that is too long into it.
For example, putting an 11-character value into a 10-character variable:
declare
var varchar2(10);
begin
var := 'abcdefghikl';
end;
/
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
You need to get the full error stack; calling the procedure with the same value from SQL*Plus or SQL Developer would give you more information, and pinpoint the line in the procedure where the error occurs.
If you were putting too much into the dbms_output buffer, you'd get a different error:
set serveroutput on size 2000
begin
for i in 1..40 loop
dbms_output.put('12345678901234567890123456789012345678901234567890');
end loop;
dbms_output.put_line('X');
end;
/
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
As far as I'm aware there is no way to see the current buffer size; the dbms_output documentation doesn't show anything, and if it's a package variable then you won't be able to see it without a getter function.
If you're getting the exception from this:
inout_error_msg := v_msg || '; errorcode--> ' || v_err;
... then the variable the caller is passing to the procedure is too small:
declare
msg varchar2(10);
begin
copyData(msg);
end;
/
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Increasing the size of the passed variable fixes this:
declare
msg varchar2(200);
begin
copyData(msg);
end;
/
anonymous block completed
Obviously you'll have to decide how big to make the variable; it needs to be big enough for any possible response. SQLERRM can be up to 512 characters, so you need to allow for that, plus the error code, plus your fixed text, so... erm... 536, at least? To allow for future changes you should probably make it larger so you don't have to come back and modify the callers.
