SQL & PL/SQL

So, I haven't visited this topic in a long time, so I'm trying to refresh my memory on how everything works ...

We set our NLS_DATE_FORMAT at the system level ... what ... 4 years ago to 'MM/DD/RR'. Despite the fact that I set this to something more commonly-used than the default of 'DD-Mon-RR', we've adopted the standard of always using TO_DATE() with an explicit format, just in case it's ever changed.

There were some applications that missed the standard, and now that a driver (for ColdFusion) has been updated, these SQL statements are now failing.

When I started looking into it, I realized that the system-level default of 'MM/DD/RR' should work fine. But, after experimenting in SQL*Plus and TOAD, I am thinking that either:

1) the system-level format is not being used
2) and/or there are login scripts which are setting these to something else

Coincidentally, both SQL*Plus and TOAD return the exact same query results:

SQL> select * 
     from NLS_INSTANCE_PARAMETERS 
     where parameter = 'NLS_DATE_FORMAT';

PARAMETER                      VALUE
------------------------------ -----------------------------
NLS_DATE_FORMAT                MM/DD/RR

SQL> select * 
     from NLS_SESSION_PARAMETERS 
     where parameter = 'NLS_DATE_FORMAT';

PARAMETER                      VALUE
------------------------------ -----------------------------
NLS_DATE_FORMAT                DD-MON-RR

So I looked into the glogin.sql script (which both tools share), and there's nothing mentioned about the NLS_DATE_FORMAT

--
-- Copyright (c) 1988, 2003, Oracle Corporation.  
-- All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to
--   be executed when a user starts SQL*Plus, or 
--   uses the SQL*Plus CONNECT command
--
-- USAGE
--   This script is automatically run
--

-- Used by Trusted Oracle
COLUMN ROWLABEL FORMAT A15

-- Used for the SHOW ERRORS command
COLUMN LINE/COL FORMAT A8
COLUMN ERROR    FORMAT A65  WORD_WRAPPED

-- Used for the SHOW SGA command
COLUMN name_col_plus_show_sga FORMAT a24
COLUMN units_col_plus_show_sga FORMAT a15
-- Defaults for SHOW PARAMETERS
COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME
COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE

-- Defaults for SHOW RECYCLEBIN
COLUMN origname_plus_show_recyc   FORMAT a16 HEADING 'ORIGINAL NAME'
COLUMN objectname_plus_show_recyc FORMAT a30 HEADING 'RECYCLEBIN NAME'
COLUMN objtype_plus_show_recyc    FORMAT a12 HEADING 'OBJECT TYPE'
COLUMN droptime_plus_show_recyc   FORMAT a19 HEADING 'DROP TIME'

-- Defaults for SET AUTOTRACE EXPLAIN report
COLUMN id_plus_exp FORMAT 990 HEADING i
COLUMN parent_id_plus_exp FORMAT 990 HEADING p
COLUMN plan_plus_exp FORMAT a60
COLUMN object_node_plus_exp FORMAT a8
COLUMN other_tag_plus_exp FORMAT a29
COLUMN other_plus_exp FORMAT a44

-- Used to alter the TOAD environment so that users do not have to
-- use the SET DEFINE OFF command prior to compiling code
-- Charles Forbes 10.17.2005
SET scan off

If I expressly go into either tool and execute, the following, setting the format to that already delcared at the system-level:

alter session set nls_date_format = 'MM/DD/RR'

Then these SQL statements start running just fine again.

There's something that I'm missing in my basic understanding of how this works. I assumed that the driver update for ColdFusion perhaps enabled a different "glogin.sql"-type script equivalent for that tool ... until ... I started checking into how the NLS_DATE_FORMAT is supposed to work ... but isn't. Could someone help me clarify where the hole is in my understanding?

Thanks,
Chuck