Skip to Main Content

Oracle Database Discussions

Announcement

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!

NLS_DATE_FORMAT strange issue

learner1Aug 25 2015 — edited Aug 26 2015

Hi,

DB host : OEL 5.4 32 bit

Oracle db : 11.2.0.3

I have set the NLS_DATE_FORMAT variable, but not sure why it doesn't show sysdate in that format.

I feel i might have hit some sort of bug, any idea.

[oracle@ocmprimary ~]$ echo $NLS_DATE_FORMAT

MM/DD/YYYY HH24:MI:SS

[oracle@ocmprimary ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 25 20:01:34 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select sysdate from dual;

SYSDATE

---------

25-AUG-15

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL>

Here's the SET output

[oracle@ocmprimary ~]$ set

AGENT=/u01/app/oracle/agent11g/bin

BASH=/bin/bash

BASH_ARGC=()

BASH_ARGV=()

BASH_LINENO=()

BASH_SOURCE=()

BASH_VERSINFO=([0]="3" [1]="2" [2]="25" [3]="1" [4]="release" [5]="i686-redhat-linux-gnu")

BASH_VERSION='3.2.25(1)-release'

COLORS=/etc/DIR_COLORS.xterm

COLORTERM=gnome-terminal

COLUMNS=237

DBUS_SESSION_BUS_ADDRESS=unix:abstract=/tmp/dbus-ciVMoHgzIZ,guid=ffa47a8716d695c36a827a0055dd0cb7

DESKTOP_SESSION=default

DESKTOP_STARTUP_ID=

DIAG=/u01/app/oracle/diag/rdbms/

DIRSTACK=()

DISPLAY=:0.0

EUID=500

GDMSESSION=default

GDM_XSERVER_LOCATION=local

GNOME_DESKTOP_SESSION_ID=Default

GNOME_KEYRING_SOCKET=/tmp/keyring-Mz2Xnd/socket

GROUPS=()

GTK_RC_FILES=/etc/gtk/gtkrc:/home/oracle/.gtkrc-1.2-gnome2

G_BROKEN_FILENAMES=1

HISTFILE=/home/oracle/.bash_history

HISTFILESIZE=1000

HISTSIZE=1000

HOME=/home/oracle

HOSTNAME=ocmprimary

HOSTTYPE=i686

IFS=$' \t\n'

INPUTRC=/etc/inputrc

LANG=en_US.UTF-8

LESSOPEN='|/usr/bin/lesspipe.sh %s'

LINES=50

LOGNAME=oracle

LS_COLORS='no=00:fi=00:di=00;34;41:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:'

MACHTYPE=i686-redhat-linux-gnu

MAIL=/var/spool/mail/oracle

MAILCHECK=60

NLS_DATE_FORMAT='MM/DD/YYYY HH24:MI:SS'

OPTERR=1

OPTIND=1

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

ORACLE_SID=primdb

OSTYPE=linux-gnu

PATH=/usr/kerberos/bin:/usr/local/bin:/usr/bin:/bin:/usr/X11R6/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0/dbhome_1/bin

PIPESTATUS=([0]="127")

PPID=4457

PROMPT_COMMAND='echo -ne "\033]0;${USER}@${HOSTNAME%%.*}:${PWD/#$HOME/~}"; echo -ne "\007"'

PS1='[\u@\h \W]\$ '

PS2='> '

PS4='+ '

PWD=/home/oracle

SESSION_MANAGER=local/ocmprimary:/tmp/.ICE-unix/4172

SHELL=/bin/bash

SHELLOPTS=braceexpand:emacs:hashall:histexpand:history:interactive-comments:monitor

SHLVL=2

SSH_AGENT_PID=4246

SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass

SSH_AUTH_SOCK=/tmp/ssh-gFzAVx4172/agent.4172

TERM=xterm

TNS_ADMIN=/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/

UID=500

USER=oracle

USERNAME=oracle

WINDOWID=30427226

XAUTHORITY=/tmp/.gdmTH6U3X

XMODIFIERS=@im=none

_=SET

consoletype=pty

tmpid=500

[oracle@ocmprimary ~]$

What am i missing here ??? Sometime issues which are so basic like this can throw you off :-(

Irrespective of what i set in unix, i always get sysdate as DD-MON-YYYY

-Learner

This post has been answered by caadecarvalho on Aug 26 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 23 2015
Added on Aug 25 2015
13 comments
2,389 views