Skip navigation

If you need a specific date format in the Oracle session of a program that you are not running interactively (and therefore, you cannot type “ALTER SESSION SET NLS_DATE_FORMAT = "DD-MM hh24:mi" ;”), you can define it in the shell that executes your program.

Here is a simple demonstration with SQL+.

Let’s create a tdform.ksh shell script that will run SQL+ 4 times, with NLS_DATE_FORMAT defined in 4 different ways.

 

#!/bin/ksh

sqlplus -s / as sysdba  << EOF

set pagesize 0 echo off lin 300

pro nothing defined

select sysdate from dual ;

exit;

EOF

 

sqlplus -s / as sysdba  << EOF

set pagesize 0 echo off lin 300

alter session set nls_date_format = "DD-MM-RR HH24:MI:SS" ;

pro NLS_DATE_FORMAT forced with an ALTER SESSION

select sysdate from dual ;

exit;

EOF

 

export NLS_DATE_FORMAT="YY-MM-DD hh24:mi"

sqlplus -s / as sysdba  << EOF

set pagesize 0 echo off lin 300

pro NDF exported in SHELL as YY-MM-DD hh24:mi

select sysdate from dual ;

exit;

EOF

 

export NLS_DATE_FORMAT="DD-MM-RR hh24:mi:ss"

sqlplus -s / as sysdba  << EOF

set pagesize 0 echo off lin 300

pro NDF exported in SHELL French format with seconds

select sysdate from dual ;

exit;

EOF

exit

 

Then let’s run that shell script and see if the format of “sysdate” varies each time.

 

oracle@mylinuxserver:/home/oracle $ ./tdform.ksh

nothing defined

29-JAN-18

 

Session altered.

 

NLS_DATE_FORMAT forced with an ALTER SESSION

29-01-18 17:02:04

 

NDF exported in SHELL as YY-MM-DD hh24:mi

18-01-29 17:02

 

NDF exported in SHELL French format with seconds

29-01-18 17:02:04

 

As demonstrated here, SQL+ inherits the NLS_DATE_FORMAT defined in the shell that executes it.  This is especially useful with other programs that do not allow you to run “ALTER SESSION SET NLS_DATE_FORMAT”, such as impdp, RMAN….