1

I've code to create tables that doesn't specify length semantics:

CREATE TABLE CHAR_TEST (
    CHAR_TEST_ID NUMBER(*,0) NOT NULL,
    FOO VARCHAR2(6) NOT NULL,
    BAR VARCHAR2(6) NOT NULL,

    CONSTRAINT CHAR_TEST_PK PRIMARY KEY (CHAR_TEST_ID) ENABLE
);

Actual code is fairly large and contains hundreds of objects. I have two versions of the code that correspond to different source control revisions separated by a couple of years.

I've created 2 users/schemas on the same server with identical settings. I then run each revision in each respective schema. To my surprise, the latest revision gets an unexpected CHAR semantic:

Name         Null     Type        
------------ -------- ----------- 
CHAR_TEST_ID NOT NULL NUMBER(38)  
FOO          NOT NULL VARCHAR2(6) 
BAR          NOT NULL VARCHAR2(6) 
Name         Null     Type        
------------ -------- ---------------- 
CHAR_TEST_ID NOT NULL NUMBER(38)  
FOO          NOT NULL VARCHAR2(6 CHAR) 
BAR          NOT NULL VARCHAR2(6 CHAR) 

I can't figure out where those CHAR come from!

  • I can't reproduce it if I run CREATE TABLE code isolately
  • I've been unable to find ALTER TABLE CHAR_TEST statements in my code base that change anything but keys or indexes
  • SELECT value FROM V$PARAMETER WHERE NAME LIKE '%nls_length_semantics%'; prints BYTE in both schemas

Could you please suggest where to look further?

2
  • 1
    I always use select value from v$nls_parameters where parameter = 'NLS_LENGTH_SEMANTICS' - does that show a different value than the one stored in v$parameter? – a_horse_with_no_name Nov 18 '15 at 9:49
  • @a_horse_with_no_name It's BYTE anyway. But I think I'm just a plain idiot... Please let me check something and I'll report back. – Álvaro González Nov 18 '15 at 9:57
1

I forgot the most obvious thing: session parameters can be changed!

I had this on the latest revision:

ALTER SESSION SET NLS_LENGTH_SEMANTICS = 'CHAR';

... but not the older one. I was running the scripts in a different session thus I couldn't spot the value change.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.