utf8 ,sqlplus, unix, sqlldr and character translation 
Author Message
 utf8 ,sqlplus, unix, sqlldr and character translation

We have an Oracle 8.1.7 DB  (running in Solaris 8) that was altered to
have an NLS_CHARCTERSET of utf8.

A Weblogic application inserts rows to this  DB.

The main two tables were created with mostly CHARACTER datatype
columns instead of VARCHAR2 .

On a nightly basis a sqlplus script runs that takes data fro this
oracle DB to a reporting DB  (also ORace 8.1.7 on a separate solaris
box)where the same tables are defined with VARCHAR2 type to save
space.

Here is my question: If the  client app can accept utf-8 encoded
charaters, will my extract and load scripts that take data from one
unix box and put it on another using sqlplus, ftp and sqlldr still
work  or will I mangle utf8 encoded characters inthe process ?

Seems to me that somewhere along the line, the charaters will be
translated back to ascii unless I can tell sqlplus that spool files
need to be utf8 encoded .

After reading the documentation on NLS etc. , I coulnd't find a good
explanation of this.

Thanks



Fri, 28 Jan 2005 06:05:37 GMT
 utf8 ,sqlplus, unix, sqlldr and character translation

If you set the NLS_LANG properly (specifying UTF8) ... then you should not
have any problem.
So if you specify nls_lang to something like American_America.UTF8 in the
env settings before
calling your sqlplus script, you will have a utf8 spool file.
And if you specify the same for sqlldr (direct=n), you will see the data
loaded correctly.

Thus:

export NLS_LANG=American_America.UTF8

...
ftp ...
...
export NLS_LANG=American_America.UTF8
sqlldr userid/pass control=mycontrol

You should test it first .. of course....

Anurag


Quote:
> We have an Oracle 8.1.7 DB  (running in Solaris 8) that was altered to
> have an NLS_CHARCTERSET of utf8.

> A Weblogic application inserts rows to this  DB.

> The main two tables were created with mostly CHARACTER datatype
> columns instead of VARCHAR2 .

> On a nightly basis a sqlplus script runs that takes data fro this
> oracle DB to a reporting DB  (also ORace 8.1.7 on a separate solaris
> box)where the same tables are defined with VARCHAR2 type to save
> space.

> Here is my question: If the  client app can accept utf-8 encoded
> charaters, will my extract and load scripts that take data from one
> unix box and put it on another using sqlplus, ftp and sqlldr still
> work  or will I mangle utf8 encoded characters inthe process ?

> Seems to me that somewhere along the line, the charaters will be
> translated back to ascii unless I can tell sqlplus that spool files
> need to be utf8 encoded .

> After reading the documentation on NLS etc. , I coulnd't find a good
> explanation of this.

> Thanks



Fri, 28 Jan 2005 07:11:20 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Character set conversion to UTF8 from WE8ISO8859P1 to UTF8

2. Characters beyond ASCII in database ( US7ASCII and UTF8 character set )

3. How to avoid display of password when script executes SQLPLUS / SQLLDR

4. UTF8 characters in an ASCII7 Oracle Instance.

5. Solution: UCS2 / UTF8 / Oracle / Fail to convert between UTF8 and UCS2

6. database character sets utf8

7. character set conversion: AL24UTFFSS to UTF8

8. Export & Import with UTF8 Character Set

9. migrating data from en_us.819 to en_us.utf8, Chinese characters and Unicode PERL modules

10. Urgent Help Needed: How to load french characters to a table using SQLLDR

11. sqlldr question with danish characters

12. Character Translation Problem!!!


 
Powered by phpBB® Forum Software