Forum OpenACS Q&A: NLS_DATE_FORMAT problem with remote Oracle

Collapse
1: NLS_DATE_FORMAT problem with remote Oracle
Posted by Jamie Ross on 02/25/04 11:15 PM
Hi all,

I am (finally) doing a 4.6 system at work where I am running on a solaris box with Oracle on a remote NT box.  I managed to get all the openacs.tcl parameters set and I can talk to Oracle but the environment variable NLS_DATE_FORMAT doesn't seem to have any effect, which I assume is because the database is on a differnt machine.  My dba doesnt want to chaneg the init file as it will change all instances.  Does anyone have any suggestions on how I can change it from my side..am I just missing something obvious?

thanks
Jamie

Collapse
2: Re: NLS_DATE_FORMAT problem with remote Oracle (response to 1)
Posted by Don Baccus on 02/26/04 01:27 AM
Huh?  The init file controls a single database, so if he's willing to do the work he can create you your own little database with nls date format set correctly and it won't affect any of the others ...

The obvious thing I'm missing, though, is why you're not running OpenACS with Postgres :)

Collapse
3: Re: NLS_DATE_FORMAT problem with remote Oracle (response to 1)
Posted by Don Baccus on 02/26/04 01:28 AM
I think that's right, anyway (I've never tried running with multiple init files ...)
Collapse
4: Re: NLS_DATE_FORMAT problem with remote Oracle (response to 1)
Posted by Barry Books on 02/26/04 02:57 AM
I always have trouble mixing Unix and NT. This seems to fix the problem 
create or replace trigger data_logon_trigger
after logon
ON DATABASE
begin
    execute immediate
        'alter session set nls_date_format = ''YYYY-MM-DD'' ';
end;
/
show error
Collapse
5: Re: NLS_DATE_FORMAT problem with remote Oracle (response to 1)
Posted by Don Baccus on 02/26/04 03:17 AM
Oracle supports AFTER LOGON triggers, eh?  Now, Oracle-hater that I am, I still gotta admit that's pretty cool ...
Collapse
6: Re: NLS_DATE_FORMAT problem with remote Oracle (response to 1)
Posted by Barry Books on 02/26/04 12:41 PM
It's even smart enough to let you login as sys if you screw up the trigger.
Collapse
7: Re: NLS_DATE_FORMAT problem with remote Oracle (response to 2)
Posted by Jamie Ross on 02/26/04 06:25 PM
If I had my druthers , I WOULD be running PostgreSQL.. but this being a big company project, they want to interface with Oracle..  on the bright side, they didnt make me use IIS :)
Collapse
8: Re: NLS_DATE_FORMAT problem with remote Oracle (response to 1)
Posted by Michael Hinds on 02/26/04 06:35 PM
You should be able to set NLS_DATE_FORMAT on the Solaris box, either in the environment of nsadmin or in the script that starts nsd. E.g. just did this from one of our Linux boxes to a Win2000 database: 
[oracle@qfs18 oracle]$ sqlplus crga/crga@oemrep

SQL*Plus: Release 8.1.7.0.0 - Production on Thu Feb 26 17:18:09 2004

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> select sysdate from dual;

SYSDATE
----------
2004-02-26

SQL> quit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
[oracle@qfs18 oracle]$ export NLS_DATE_FORMAT="DD-MM-YYYY"
[oracle@qfs18 oracle]$ sqlplus crga/crga@oemrep

SQL*Plus: Release 8.1.7.0.0 - Production on Thu Feb 26 17:18:27 2004

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> select sysdate from dual;

SYSDATE
----------
26-02-2004

SQL>