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;

BEGIN
  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);
  DBMS_SQL.CLOSE_CURSOR(c);

END;
/

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.

Env:

                Oracle 10.2.0.1.0 windows client
                Oracle 11.1.0.7.0 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

--
http://www.freelists.org/webpage/oracle-l

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