Odd one that came in recently

From: Goulet, Richard <Richard.Goulet_at_parexel.com>
Date: Tue, 15 Nov 2011 20:26:39 +0000
Message-ID: <7642C3D639A75E4284658CAA38BF60A4080991_at_US-BOS-VEX001.eu.pxl.int>

I've been asked a very odd question about "empty strings" in Oracle. The individual is running the following PL/SQL anonymous block: DECLARE
  c           NUMBER;
  col_cnt     INTEGER;
  rec_tab     DBMS_SQL.DESC_TAB;

  DBMS_OUTPUT.PUT_LINE('Testing the datatype of an empty string:');   c := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(c, 'SELECT '''' as empty_string FROM dual', DBMS_SQL.NATIVE);
  DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
  DBMS_OUTPUT.PUT_LINE('max length = ' || rec_tab(1).col_max_len);


In all but one database, that belongs to a client, the result in 0 as follows:

Testing the datatype of an empty string: max length = 0

PL/SQL procedure successfully completed.

But on the clients system it returns:

Testing the datatype of an empty string: max length = 32

PL/SQL procedure successfully completed.

Anyone ever see this kind of behavior? It violates just about all of the documentation out there.


                Oracle windows client
                Oracle on Linux with TDE.

And yes I have run it against a system that we have TDE on as well. Can't figure this one out. Richard Goulet
Senior Oracle DBA/Na Team Leader

Received on Tue Nov 15 2011 - 14:26:39 CST

Original text of this message