The answer is – when it’s 255 or, to put it another way, when 9 = 10.
I’d better explain that a bit before you begin to wonder if I really should learn to use a calculator ( or even take my shoes and socks off to count past 10).
For many years, DBMS_OUTPUT.PUT_LINE had a line-length limit of 255 characters.
By the time the Oracle 10g R2 databases came along, this had been beefed up to 32767. It can therefore be puzzling, when you still get a pesky
ORU-10028 line length overflow for a line that isn’t anywhere near this limit.
Was it something you said, has Oracle just forgotten how to count, or does DBMS_OUTPUT just hate you ?
There are times when I have considered all of these possibilities when dealing with recalcitrant databases, but on this occasion the answer is a bit less dramatic : Oracle seem to have trouble telling the difference between 9 and 10.
This manifests itself when you’re using an early version of Oracle’s Internet Developer Suite 10g.
This is mainly Forms and Reports. Oracle when through one of their periodic “lining up” of versions when 10g database came out. However, it seems like the Application Server guys didn’t get the memo because the original 10g versions of both the Oracle 10g Application Server and the Internet Developer Suite are actually 220.127.116.11.1.
This becomes relevant when you consider that IDS includes an Oracle Client, complete with SQL*Plus.
It becomes even more relevant when you consider that DBMS_OUTPUT, unlike most Oracle supplied PL/SQL packages, is sensitive to the client on which it is running.
So, although you may be running against the latest and greatest Oracle release, the line length limit for DBMS_OUTPUT.PUT_LINE is dictated by the Oracle Client your connecting on.
How can you check you’re on the right client ? Well, when you log in via SQL*Plus, you should get something like :
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 24 12:07:06 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production
Remember, it’s the SQL*Plus version you’re interested in because that’s the version of the Oracle client.
If the version number is 9.4.x then you may have some problems.
The best way to be certain, as always, is to suck it and see :
set serveroutput on size 1000000 DECLARE l_output VARCHAR2(32767) := NULL; BEGIN FOR i IN 1..32767 LOOP l_output := l_output||'a'; END LOOP; DBMS_OUTPUT.PUT_LINE(l_output); END; /
If this runs without an error, you should be fine. If not, then it’s probably time to take your socks off ( or find a later version of the Oracle Client).