Odd one that came in recently

From: "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
To: "ORACLE-L (oracle-l@xxxxxxxxxxxxx)" <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 15 Nov 2011 20:26:39 +0000

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


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

Odd one that came in recently

Other related posts: