SQL & PL/SQL

Hi all,

I came across a very strange problem. One of our applications is running on both oracle 9i and oracle 10g. there is a packages, the developer put something like:

select to_char(sysdate,'D') into l_week_day from dual;

then rest of the code doing the calculation on l_week_day, ie l_week_day + 1. but for a given date, this number returns varies value. I guess there might be some different NLS setting against these two db

However how can I prove this, I issued the following statement on both database and surprisingly get the exactly the same output.

select * from v$nls_parameters t

1 NLS_LANGUAGE ENGLISH
2 NLS_TERRITORY UNITED KINGDOM
3 NLS_CURRENCY £
4 NLS_ISO_CURRENCY UNITED KINGDOM
5 NLS_NUMERIC_CHARACTERS .,
6 NLS_CALENDAR GREGORIAN
7 NLS_DATE_FORMAT DD-MON-RR
8 NLS_DATE_LANGUAGE ENGLISH
9 NLS_CHARACTERSET UTF8
10 NLS_SORT BINARY
11 NLS_TIME_FORMAT HH24.MI.SSXFF
12 NLS_TIMESTAMP_FORMAT DD-MON-RR HH24.MI.SSXFF
13 NLS_TIME_TZ_FORMAT HH24.MI.SSXFF TZR
14 NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH24.MI.SSXFF TZR
15 NLS_DUAL_CURRENCY €
16 NLS_NCHAR_CHARACTERSET UTF8
17 NLS_COMP BINARY
18 NLS_LENGTH_SEMANTICS CHAR
19 NLS_NCHAR_CONV_EXCP FALSE

Thanks very much for your help.

Pete

Edited by: lcpx on Dec 9, 2009 5:50 AM