what does affect Oracle's determination of string expressions datatype?

Question

I'm observing a difference in how Oracle determines a datatype of string expressions on one particular Oracle instance. The most striking example is the datatype of an empty string: on all instances but one the datatype is char(0); on that exceptional one it's char(32).

The following script illustrates this: it parses a simple statement SELECT '' FROM dual and describes the column.

===
SET SERVEROUTPUT ON

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;
/
===

It returns 32 on this specific instance and 0 on all the others. The following is the result of querying nls_database_parameters on that particular instance:

PARAMETER       VALUE
--------------- ---------------
NLS_LANGUAGE    AMERICAN
NLS_TERRITORY   AMERICA
NLS_CURRENCY    $
NLS_ISO_CURRENCY    AMERICA
NLS_NUMERIC_CHARACTERS  .,
NLS_CHARACTERSET    WE8MSWIN1252
NLS_CALENDAR    GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE   AMERICAN
NLS_SORT    BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT  HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY   $
NLS_COMP    BINARY
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET  AL16UTF16
NLS_RDBMS_VERSION   11.1.0.7.0
NLS_CSMIG_SCHEMA_VERSION    5

It does not differ from all the other instances. The only thing that is different is the installed TDE option. Unfortunately I don't have an instance with this option installed at hand and cannot test it there...

My question is if anyone knows of the factors which may influence the way Oracle determines the datatype of string expressions. The case with empty strings is not the only one, but it's the most critical as we have a legacy application that issues lots of SELECTs with empty strings without casting them to a specific datatype and that difference in Oracle's behaviour stops the application from working. Any input would be appreciated!

Konstantin

Answer 1

Very curious, I can't replicate this on 9.2.0.1.0, 10.2.0.1.0, 11.1.0.6.0 or 11.2.0.1.0 and they all have near identical parameters (UK not US).

The only possibility that I can think of is that someone at some point has logged onto sys and played about with ( altered ) dual. Obviously I've obscured the server name and pw...

 [oracle@server ~]$ sqlplus -S "sys/pw as sysdba"

Session altered.

desc dual
 Name              Null?    Type
 ----------------- -------- --------------------------------------------
 DUMMY                      VARCHAR2(1)

set echo on
select dbms_lob.substr(dbms_metadata.get_ddl('TABLE','DUAL')) from dual;

DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('TABLE','DUAL'))
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."DUAL"
   (    "DUMMY" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"

Your output should look like the above. If it's something different then that would explain the strangeness.