chr(163) for £ pound character

Use chr(163) for the ‘£’ character, especially in stored SQL such as create procedure scripts, where you can’t be sure what client character settings might be used to re-create the procedure. SELECT m.description, chr(163)||TO_CHAR(m.weekly_rate, ‘999.99’) amount from my_table m; This plsql gives the full list of ASCII codes: set serveroutput on size 10240 declare i […]

Read More chr(163) for £ pound character
June 8, 2010

Oracle Application Server sets NLS_LANG by default

If you don’t specify NLS_LANG in your shell when starting OAS, OAS goes and sets it for you. Fix is to specify NLS_LANG in any OAS startup scripts you use, or edit these files: $ grep -i NLS_LANG $ORACLE_HOME/Apache/Apache/bin/apachectl NLS_LANG=${NLS_LANG=”ENGLISH_UNITED KINGDOM.WE8ISO8859P1″}; export NLS_LANG $ grep -i NLS_LANG $ORACLE_HOME/opmn/bin/opmnctl NLS_LANG=${NLS_LANG=”ENGLISH_UNITED KINGDOM.WE8ISO8859P1″}; export NLS_LANG $ Reference 10gR2 […]

Read More Oracle Application Server sets NLS_LANG by default
March 27, 2009

Logon Trigger to Capture Session NLS_Territory

You can see your own sessions nls settings select * from nls_session_parameters ; But for other users’ sessions, that information is stored in their own UGA, not accessible outside their session. So if you need to know what their nls settings are, a logon trigger is needed to record that. Like this: conn / as […]

Read More Logon Trigger to Capture Session NLS_Territory
March 27, 2009

Howto remove carriage return line feed from SQL Server for displaying in Excel

Use this SQL to remove carriage return line feed from SQL server for displaying in excel: SELECT REPLACE ( column_name , CHAR(13) + CHAR(10) , ‘, ‘ ) FROM table_name GO Based on posts by Aaron Bertrand and David Seruyang. Update 2015: Carriage return (chr13) + line feed (chr10) is the default line terminator for […]

Read More Howto remove carriage return line feed from SQL Server for displaying in Excel
March 13, 2009

8 Comments