CODEWITHSUNDEEP
sqloracle-databaserdbms
Mike
Mike

Reputation: 2559

Increase buffer size in JBSQL

I have some Oracle PL?SQL code that a user is trying to run using the JBSQL tool

There are a few dbms_output lines - when the user runs the code they get an error

 ORA-06502: PL/SQL: numeric or value error: character string buffer too small

How do I increase the buffer size?

I've tried 

SET SERVEROUTPUT ON SIZE 1000000;

but get "unknown option size". Is there a "default" command regardless of the tool to set the buffer size? Or can I add anything to my PL/SQL?

(Eventually I will write the lines to a table/text file but need to get this procedure up and running to show it is worthwhile - everything works absolutely fine - just the bit that notifies the user at the end!!!)

Any help gratefully received

Thanks

Mike

Upvotes: 0

Views: 1077

Answers (3)

Jorgen Bosman
Jorgen Bosman

Reputation: 11

What version of JBSql are you using? Normally, since version 1.1.0.2 the buffer size has been increased from 255 to 32767 characters. If it still fails with the latest version (1.3.0.0), then please provide a testcase to http://duofoto.be/mantis

Upvotes: 1

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

SET SERVEROUTPUT is a SQL*Plus command, it is not a SQL or PL/SQL command. It will only work in SQL*Plus or SQL*Plus-like tools (Toad, SQL developer...).

You can use DBMS_OUTPUT.enable(buffer_size) to increase the default output buffer size. Note however that your error could come from somewhere else: ORA-06502 is a generic error that can be raise by any assignment where the variable is too small.

Upvotes: 2

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

SET SERVEROUTPUT ON SIZE n; is an SQL*Plus command, but many editors allow to use it. Anyways, can you try this instead:

BEGIN
  DBMS_OUTPUT.ENABLE(BUFFER_SIZE => 1000000);
END;

Upvotes: 1

Related Questions