Oracle Database Discussions

I am currently trying to run a UTF8 enabled application on a Oracle 11.2.0.1.0 database, and have been having trouble storing some characters into the DB. I have been advised by the application support that the product can definitely do store them, and that I must set the NLS_LENGTH_SEMANTICS parameter to CHAR using the following statement;

ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR SCOPE=BOTH;

However after logging into the target database as the "systems" user and running this statement, which runs successfully, querying the "v$nls_parameters" still shows that it is set to "BYTE". I have tried then creating a new database and setting this value to CHAR in the advanced settings, but still to no avail.

However after googling this and reading several articles I have began to notice some other strange behaviour. The first thing i noticed was the "SPFILE{dbname}.ORA" file contains the right value i.e;

*.nls_length_semantics='CHAR'

The second thing that I noticed was that running the "v$nls_parameters" query in SQL Developer produced a different result to the one produced by SQL Plus (See below) even though exactly the same user in the same database is being used. In SQL developer the following query "select * from v$nls_parameters where Parameter = 'NLS_LENGTH_SEMANTICS';" produces;

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LENGTH_SEMANTICS BYTE

1 rows selected

Where as in SQLPLUS this query produces;

SQL> select * from v$nls_parameters where Parameter = 'NLS_LENGTH_SEMANTICS';

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_LENGTH_SEMANTICS
CHAR

Can anyone provide any insight as to what could be the problem with my oracle database setup, as the application developers are adamant that UTF8 chars are supported and the only difference they can see between my setup (which doesnt work) and theirs (which does work) is the value of this parameter. Details of my system are;

OS: Windows 7 x64
SQL Developer: 2.1.0.63
DATABASE: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production