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
BEGIN
DBMS_OUTPUT.PUT_LINE('Testing the datatype of an empty string:'); c := DBMS_SQL.OPEN_CURSOR;
END;
/
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-lReceived on Tue Nov 15 2011 - 14:26:39 CST