My oracle 11g database has utf8 character encoding. And data like ä ö ü etc. Oracle is not on the same host as my php application. The database host can be a Windows 7 machine (in Windows-1252) and a Linux machine (in UTF-8). And I need to export a table into a cvs file to the host.
The client machines can equally be Windows or Linux. The client machines do not have oracle installed. They use php/pdo to connect to the database machines.
What is the best way to export the data from a table from the database to a client host file preserving the correctness of the characters and how can I do this generically, so that the host's locale/character set is respected as best possible?
What I have up to now, to convert and to get the cvs format: using sqlplus:
select convert( COLUMNNAME1, "WE8ISO8859P1")||','||convert( COLUMNNAME2, "WE8ISO8859P1")|| ... from TABLE;
which is fine for a iso-8859 system.
But this obviously will break, if I use this code on the utf8 system. And I need to map the client hosts character set to the oracle database convert parameter.
I do not have the freedom to change the locales of the systems.
Or is all this solved if/when NLS_LANG is set? How can I let the database on the remote machine know that exported data, e.g. using spool, needs to be converted into the client's machine characterset?
