I need some simple advice, a good chance for some quick points for you.
I have never built a database to understand any other character set other than American English. I now have to build a database that will be used for Spanish characters- keyboards, etc. But I will be using English for the 11g software install. I only wish to be able to show Spanish characters in the data for customers names.
I will be creating the database with scripts I have made to make the standard template for database files, control files, etc.
Then I will be importing from a dump I have done that was made with American English character sets.
System is 11g (126.96.36.199.0) on Linux Enterprise Server 5.8.
I was thinking to use the AL32UTF8 character set, but I am unsure where to use it.
My original test did not show Spanish characters for customers names like the 'tilda' or 'sueano' (pardon my spelling). But in this case I did not make the exeception for Spanish, I only used the standard American English build (no changes in the init.ora file or initial database build script).
How can I adjust my parameter file for the initial creation of the database template to be able to understand the Spanish character set and still be able to import my dump file without error.
EXAMPLE of a build script:
CREATE DATABASE mynewdb
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/mynewdb/redo01.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/oradata/mynewdb/redo02.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/oradata/mynewdb/redo03.log') SIZE 100M
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
If I replace NATIONAL CHARACTER SET AL16UTF16 to AL32UTF8 will it work to show Spanish characters?
Sorry for the long winded question, any advice will be great.
the national charsets is for column types like nvarchar not for normal varchar data types. So if your dump file contains such column types you will also need to set it. The charset is for the normal column types like varchar. The use of unicode is best pratice if you use multiel language, but keep in mind that multibyte charset can be a problem during the import because varchar2(10) means 10byte and not 10 chars, so errors like identifier to long can occur during import.
You can create the database.
Check this documentation:
You can use a charset like WE8MSWIN1252 which covers spanish also (as far i know) and is a superset to us7ascii
Million Thanks Peter for the advice, I have never done this before and confused on the documentation- this is new to me.
You are correct in that I am only using VARCHAR2 types, no NCHAR types.
The database does not have any data in it yet, only a template.
So I would change the CHARACTER SET section of my build script to WE8MSWIN1252 and it should work, seems too easy thankfully.
I was wondering if WE8ISO8859P1 would work?
The WE8ISO8859P1 Charset is a very Old Charset. I did not like it to use (but some of my customers use it). We in Europe have Euro € Sign which is not supported by this charset. So try to avoid it to use.
Yes it should work. If you look at [url http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch2charset.htm#NLSPG165]Table 2-3 lSO 8859 Character Sets it specifies Western European supports spainish characters.