I’m working on an Oracle 11g database (Oracle Database 11.2.0.4.0) with linux sqlplus and I need to export query results to a file that can be opened in Excel. The data includes German umlauts (Ä, Ö, Ü), and I’m having trouble getting them to display correctly. I’ve tried both HTML (XLS-like) and CSV approaches.

What I’ve Tried:

1.) Using HTML Markup (result.xls):
I ran something like:

sql
Copy code
SET MARKUP HTML ON SPOOL ON
SPOOL result.xls
SELECT * FROM my_table;
SPOOL OFF

This produces an HTML file, which I can open in Excel. However, umlauts don’t display correctly.

2.) I tried setting NLS_LANG before running sqlplus:

export NLS_LANG=GERMAN_GERMANY.AL32UTF8

This changed the language of the SQL*Plus prompts but did not fix the umlauts in the output file.

3.) I also attempted using:

SET MARKUP HTML ON SPOOL ON ENCODING "UTF8"

inside my sql File but got an SP2-0158: unknown SET option “ENCODING” error, likely because I’m on Oracle 11g and that feature is not supported in this version of SQL*Plus.

4.) I tried NLS_LANG=GERMAN_GERMANY.WE8MSWIN1252 to match a code page that includes umlauts, but Excel still didn’t display the characters correctly.

What I Want to Achieve:

I want a straightforward method to generate a file from SQLPlus on Oracle 11g that I can just double-click in Excel and have all umlauts (Ä, Ö, Ü) display correctly without manual import steps. Is there a recommended approach for older SQLPlus versions to ensure umlauts are displayed correctly in Excel by default?

Thank you in advance!