DMBS_OUTPUT Can’t Count – Or When is 32767 not 32767 ?

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 9.0.4.0.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).

About these ads

One thought on “DMBS_OUTPUT Can’t Count – Or When is 32767 not 32767 ?

  1. Interesting. I wonder whether the problem is in the PUT_LINE, or the ‘under-the-covers’ GET_LINES that SQL*Plus does at the completion of the query to get the stored results for display.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s