Defining a Character Set for a column For oracle database tables

Question

I am running following query in SQL*Plus

CREATE TABLE  tbl_audit_trail (
  id NUMBER(11) NOT NULL,
  old_value varchar2(255) NOT NULL,
  new_value varchar2(255) NOT NULL,
  action varchar2(20) CHARACTER SET latin1 NOT NULL,
  model varchar2(255) CHARACTER SET latin1 NOT NULL,
  field varchar2(64) CHARACTER SET latin1 NOT NULL,
  stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  user_id NUMBER(11) NOT NULL,
  model_id varchar2(65) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (id),  
  KEY idx_action (action)
);

I am getting following error:

action varchar2(20) CHARACTER SET latin1 NOT NULL,
                      *
ERROR at line 5:
ORA-00907: missing right parenthesis

Can you suggest what am I missing?

Hegarty

@Andriod_crazy:This error is new to me ,but what will be my first approach is to find the characterset SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET' and then check whether this is supporting latin or not – Agonized

@GauravSoni, the error won't be new :-). It's just a standard syntax error. – Uropygium

@Ben:Yes your right ,but this is not specific error from which we can find the root cause . error is new to me means i have never seen error because of character set :) Even i don't know character sets can't be defined at column level thanks for the answer +1 for that – Agonized

Could you maybe explain what is lacking from my current answer @AloneInTheDark? – Uropygium

Did you check my bounty reason? "This question has not received enough attention." What i want is alternative comments about this situation. – Counterbalance

I'm sorry, but it's still not completely clear to me. Do you mean you'd like a way of defining a column level character set in Oracle? – Uropygium

I think you have 2 options here. varchar2, which is in the databases base charset, or nvarchar2, which is in the national charset. To my knowledge, you can't define a different fields charset directly like this. – Mapping

Maybe not at the column level but, table level. – Counterbalance

Answer 1

The simple answer is that, unlike MySQL, character sets can't be defined at column (or table) level. Latin1 is not a valid Oracle character set either.

Character sets are consistent across the database and will have been specified when you created the database. You can find your character by querying NLS_DATABASE_PARAMETERS,

select value
  from nls_database_parameters
 where parameter = 'NLS_CHARACTERSET'

The full list of possible character sets is available for 11g r2 and for 9i or you can query V$NLS_VALID_VALUES.

It is possible to use the ALTER SESSION statement to set the NLS_LANGUAGE or the NLS_TERRITORY, but unfortunately you can't do this for the character set. I believe this is because altering the language changes how Oracle would display the stored data whereas changing the character set would change how Oracle stores the data.

When displaying the data, you can of course specify the required character set in whichever client you're using.

Character set migration is not a trivial task and should not be done lightly.

On a slight side note why are you trying to use Latin 1? It would be more normal to set up a new database in something like UTF-8 (otherwise known as AL32UTF8 - don't use UTF8) or UTF-16 so that you can store multi-byte data effectively. Even if you don't need it now it's wise to attempt - no guarantees in life - to future proof your database with no need to migrate in the future.

If you're looking to specify differing character sets for different columns in a database then the better option would be to determine if this requirement is really necessary and to try to remove it. If it is definitely necessary¹ then your best bet might be to use a character set that is a superset of all potential character sets. Then, have some sort of check constraint that limits the column to specific hex values. I would not recommend doing this at all, the potential for mistakes to creep in is massive and it's extremely complex. Furthermore, different character sets render different hex values differently. This, in turn, means that you need to enforce that a column is rendered in a specific character, which is impossible as it falls outside the scope of the database.

_{1. I'd be interested to know the situation}

Uropygium

CREATE TABLE utf_test ( coll VARCHAR(50) NOT NULL CHARACTER SET utf8 ); throw the same error ORA-00907: missing right parenthesis, what should I do sir? – Caphaitien

Please don't use a NCHAR unless you're extremely certain what you're doing @masT. Use a NVARCHAR2... – Uropygium

Answer 2

According to provided DDL statement it's some need to use 2 character sets. The implementation of this functionality in Oracle is different from MySQL and done with n* data types like nvarchar2, nchar... Latin1 is similar to some Western European character set that might be default. So you able to define for example "Latin1" (WE**) and some Unicode (UTF8..).

The NVARCHAR2 datatype was introduced by Oracle for databases that want to use Unicode for some columns while keeping another character set for the rest of the database (which uses VARCHAR2). The NVARCHAR2 is a Unicode-only datatype. The reason you want to use NVARCHAR2 might be that your DB uses a non-Unicode character and you still want to be able to store Unicode data for some columns. Columns in your example would be able to store the same data, however the byte storage will be different.

Ewen

Good point - it is possible to have 2 character sets; it's worth noting that the character set of a NVARCHAR2 column is either UTF-16 or Oracle's old subset of UTF-8 (confusingly called UTF8). It will also cause some problems with the standard functions. – Uropygium