Edit Question - KnowledgeHub

Title Body

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

Tags (comma-separated) Cancel