For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
How can I set Character set in Spool command file. I am creating CSV fiile using spool command.
First of all, ensure that your terminal is set to UTF-8 by locale charmap or echo $LANG Then in SQL*Plus set your NLS_LANG environment variable, via the export command, for example, for UTF-8 Unicode export NLS_LANG=.AL32UTF8 By the way, Oracle 12.2.0.1 and above, SQL*Plus is capable of displaying the result of a query as CSV, which can make formatting easier SET MARK[UP] CSV {ON|OFF} DELIMI[TER] character QUOTE {ON|OFF}
locale charmap
echo $LANG
NLS_LANG
export NLS_LANG=.AL32UTF8
SET MARK[UP] CSV {ON|OFF} DELIMI[TER] character QUOTE {ON|OFF}
Thanks @paulzip .. This will definetly help me. I will check. But what my requirement is I need a script to generate CSV file and I execute that file using crontab daily baisis. So, How I export UTF-8 everytime. Or can i write this in spool file? . Or Can you suggest any better way to perform this task.
You cannot set character set as using an SQL*Plus command. You have to set it in the environment of SQL*Plus in the variable NLS_LANG. You can call SQL*Plus from a shell script and set (and export) NLS_LANG in this shell script before calling SQL*Plus. You can also write your own spooling program in Java+JDBC or in C+OCI and then you can set the character set inside the program.
Thanks, Sergiusz
I just user PowerShell to convert the file into UTF8 encoding, by:
Get-Content $inFileEncodingCp936 | Set-Content $outFileUTF8 -Encoding UTF8