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 th

oracle-database
Edit
Report